I have a spreadsheet that has over 30 ODBC queries to a MS Access database. Via VBA I programmed a command button to do a simple refresh all:
ActiveWorkbook.RefreshAll
This takes about 30 minutes to complete...it seems to take a long time to Connect to the datasource (based upon the status bar message).
If I change the code to have 30 entries of the code below it takes less than 1 minute:
Worksheets("Sheetx").Activate
'A1 is the location of the query table
Worksheets("Sheetx").Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
The workaround is worth it from a performance perspective but NOT from a maintenance perspective. Any advice on how to make the ActiveWorkbook.RefreshAll run in a reasonable amount of time.
Bookmarks