GPSPOW
asked on
Query update not working in Workbook_Open() macro
I have a vba script that refreshes an Excel workbook tab with an external connection to a SQL database. The vba is part of a VBA script that calls separate scripts in an order. When I run the master VBA script manually, it completes without a problem.
However, when I copy the same VBA script to a private sub Workbook_Open() it gets a run time error 1004: Select Method of Worksheet Class Failed at the point of the query refresh.
Here is the vba script that throws the error:
' Update Meditech Data
'
Sheets("DailyStats").Selec t
Selection.ListObject.Query Table.Refr esh BackgroundQuery:=False
Sheets("NonExHrs").Select
Range("A1").Select
Sheets("DailyFTE").Select
Any ideas why it will not work when the workbook opens?
thanks
Glen
However, when I copy the same VBA script to a private sub Workbook_Open() it gets a run time error 1004: Select Method of Worksheet Class Failed at the point of the query refresh.
Here is the vba script that throws the error:
' Update Meditech Data
'
Sheets("DailyStats").Selec
Selection.ListObject.Query
Sheets("NonExHrs").Select
Range("A1").Select
Sheets("DailyFTE").Select
Any ideas why it will not work when the workbook opens?
thanks
Glen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Coding error on my part. Figured it out myself.
Glen
Glen
Sheets("DailyStats").ListO
You don't need to select the sheet to update the query.
You could also set the query to update on workbook open.