[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

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").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("NonExHrs").Select
    Range("A1").Select
    Sheets("DailyFTE").Select

Any ideas why it will not work when the workbook opens?

thanks

Glen
0
GPSPOW
Asked:
GPSPOW
  • 2
1 Solution
 
Ejgil HedegaardCommented:
Try this
Sheets("DailyStats").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

You don't need to select the sheet to update the query.

You could also set the query to update on workbook open.
0
 
GPSPOWAuthor Commented:
I figured it out.  The macro calls in the auto exec macro were pointing to a closed workbook and creating the error.  I edited the Workbook-Open macro and now it works.

Thanks
0
 
GPSPOWAuthor Commented:
Coding error on my part.  Figured it out myself.

Glen
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now