Query Refresh prompt (2003 / XP)

Topic

New Reply

Hi everybody:

I’m sure there must be an easy way to do this, but I can’t find it in the “help.”

I have inherited an Excel workbook that contains pivot tables and query tables. I want to refresh them via code to get rid of the annoying “Query Refresh” Excel pop-up that appears each time the workbook is opened. So, I wrote a couple of little procedures to enumerate all the querytables and pivottables in the workbook and set their manual update property and backgroundquery property to false. Fine. But I’m still seeing the “Query Refresh” Excel pop-up after running the code, saving the workbook, and reopening it. So, I assume there must be some other queries somewhere in the workbook that are refreshing.

Can someone tell me how to enumerate all the queries in a workbook and set them so they don’t refresh on file open?

Thank you, in advance, for your help.

Reply | Quote

Replies

Sub DisableRefreshAllQueries()
   Dim wks As Worksheet
   Dim qt As QueryTable
   For Each wks In ActiveWorkbook.Worksheets
       For Each qt In wks.QueryTables
           qt.RefreshOnFileOpen = False
       Next qt
   Next wks
End Sub

Reply | Quote

Does this help?

Sub LoopQueries()
Dim wsh As Worksheet
Dim qtb As QueryTable
For Each wsh In Worksheets
For Each qtb In wsh.QueryTables
qtb.BackgroundQuery = False
qtb.RefreshOnFileOpen = False
Next qtb
Next wsh
End Sub

Also see Pieterse’s free QueryManager add-in available from JKP-ADS Download page.

Reply | Quote

Yes, thanks. Is refreshonfileopen the default? It wasn’t set anywhere I could see.

Reply | Quote

According to the VBA help, the default value for RefreshOnFileOpen is False. When I create a database query, the corresponding check box in the query properties is not ticked…

Reply | Quote

Thanks, Hans.

I checked the datarange properties for the datatables I enmerated, and RefreshOnFileOpen was not checked for any of them, so I assume there must have been one or more additional database queries somewhere in the workbook.

Reply | Quote