Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Refresh prompt (2003 / XP)

    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.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Refresh prompt (2003 / XP)

    <pre>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

    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Refresh prompt (2003 / XP)

    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.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Refresh prompt (2003 / XP)

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Refresh prompt (2003 / XP)

    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...
    Attached Images Attached Images

  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Refresh prompt (2003 / XP)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •