1

Not such much a question as a summing up and request for views, experiences and opinion of others.

I, or, more accurately, my colleagues, were having a problem reliably and without drama (that's to say calling me) refreshing the data of a workbook which has 30 or so queries all connected to Excel workbooks located in various sharepoint libraries in the organisation I work for. Although some general tidying up was done I think the main problem was that too many queries seemed to be trying to refresh at once. So I looked online for a VBA solution. I found this (sorry can't remember where, it could have been here, though the same code is about elsewhere).

    Sub DisableBackgroundRefresh_Orig()  
     
    ' This is the original version as I got it from a website somewhere - 
    ' stackoverflow, Mr Excel, I can't remember            
    ' This appeared to work, but, removing the "on Error Resume Next" showed that 
    '    it doesn't and in fact it runs into a "catastrophic failure" no less,    
    ' It wasn't that bad - I lived to fight another day and so will you            
    ' This macro disables background refresh of each query in turn and runs it, 
    ' Down side is that it refreshes all queries/connections even those marked as 
    ' "Do Not Refresh of Refresh All".
    
    ' It could be extended so that it first changes all the Backgroundquery 
    ' parameters to false and then runs a refresh all which should observe the 
    ' "Include in refresh_all setting and then resets all backgroundquery values 
    'to true - I've done that in the 2nd query code below and it seemed to work 
    'and it was simpler than I thought it would be
            
    MsgBox "Before closing all connections will be refreshed - it will take 50 seconds or so" & vbCrLf & "click ok when the connections have refreshed and then click save"
        
    
 On Error Resume Next
           
    
        For Each objConnection In ThisWorkbook.Connections
    
            'Get current background-refresh value
            bBackground = objConnection.OLEDBConnection.BackgroundQuery
        
            'Temporarily disable background-refresh
            objConnection.OLEDBConnection.BackgroundQuery = False
        
            'Refresh this connection
            objConnection.Refresh
         
            'Set background-refresh value back to original value
            objConnection.OLEDBConnection.BackgroundQuery = bBackground
    
        Next
             
        MsgBox "Finished refreshing all data connections"
    
    End Sub

Before I realised that this didn't work at all I thought I had improved it to take account of the "Refresh on Refresh All" property of each query. I amended it to this, at first I thought it worked.

    Sub DisableBackgroundRefresh_Refresh_V1()
         
        
 ' This appeared to work, but, removing the "on Error Resume Next" showed 
 ' that it doesn't and in fact it too runs into a "catastrophic failure" no less,
     
 ' this macro disables background refresh for all connections and then refreshes 
 ' all. I think this makes the connections/queries run in sequence rather than 
 ' parallel, which
        
 ' is possibly slower but more reliable with fewer errors. Unlike the first 
 ' version DisableBackgroundRefresh_Orig() I think this respects the "Do Not  
 ' Refresh of Refresh All" setting of each query it then resets all 
 ' backgroundquery values to true
              
  MsgBox "This will refresh all connection which are set in their properties to 
  ""Refresh on Refresh All"", that includes all the most important queries. It 
  will take a few minutes"
                       
            On Error Resume Next
        
            For Each objConnection In ThisWorkbook.Connections
        
                'Get current background-refresh value
                bBackground = objConnection.OLEDBConnection.BackgroundQuery
          
        
                'Temporarily disable background-refresh    
                objConnection.OLEDBConnection.BackgroundQuery = False
        
          Next
        
            ActiveWorkbook.RefreshAll
        
        
       '        'Refresh this connection        
        '        objConnection.Refresh
        
            For Each objConnection In ThisWorkbook.Connections
        
              'Set background-refresh value back to original value        
               objConnection.OLEDBConnection.BackgroundQuery = bBackground
        
            Next
        
        MsgBox "Almost finished refreshing - after you've clicked ok here look at 
the left of the status bar at the bottom of the Excel window to check when it 
has completely stopped refreshing"
        
        End Sub

In the end this worked for me.

    Sub DisableBackgroundRefresh_Refresh_All_V2()
    
 ' This works for me. Key changes were ThisWorkbook changed to 
 ' ActiveWorkbook, no more catastophic failure and looped through    
 ' the for loop properly as it should. Second change was to the part setting 
 ' OLEDBConnection.BackgroundQuery to active again    
 ' the code I got from - the web somewhere said "= bBackground" which did 
 ' nothing, changed that to "True" and it all seems to work as it should.    
 ' The "onerror resume next" was put back as it was otherwise getting stuck on 
 ' some queries which had their "enable background refresh" property greyed out 
 ' (I think because they referenced other queries rather than established a 
 ' direct connection of their own). It seems ok now quite short too, 
 ' apart from all my comments. I like short.
   
 ' This macro disables background refresh for all connections and then refreshes 
 ' all. I think this makes the connections/queries run in sequence rather than 
 'parallel, it is possibly slower but more reliable with fewer errors. Unlike the 
 ' first version above I think this respects the "Do Not Refresh of Refresh All" 
 ' setting of each query it then resets all backgroundquery values to true
    
   MsgBox "This will refresh all connection which are set in their properties to 
 ""Refresh on Refresh All"". That includes all the most important queries. It 
   will take a 2-3 minutes"
    
     On Error Resume Next
    
     For Each objConnection In ActiveWorkbook.Connections
    
     'Get current background-refresh value
     bBackground = objConnection.OLEDBConnection.BackgroundQuery
       
    'Temporarily disable background-refresh 
     objConnection.OLEDBConnection.BackgroundQuery = False
    
     Next
    
     ActiveWorkbook.RefreshAll
    
    
     For Each objConnection In ActiveWorkbook.Connections
    
     'Set background-refresh value back to original value
     'objConnection.OLEDBConnection.BackgroundQuery = bBackground
    
      objConnection.OLEDBConnection.BackgroundQuery = True
    
      Next
  
 
    MsgBox "Almost finished refreshing. After clicking ok here look at the bottom 
    left of" & vbCrLf & "the Excel window, the status bar, to check it has 
    completely stopped refreshing." & vbCrLf & "If there's nothing there, it 
    has."
    
    
    End Sub

So, that's it. Except that there is a less sophisticated and much simpler way to do this which will work most of the time. The downside, if it is a downside, is that your queries will need to be set manually and permanently to "Refresh Background Query = False". This code is very short, and, arguably, if you have already set the background refresh to false in every query is almost pointless as just clicking "Refresh All" in the menu will do the same, except for the message box. Here it is,

    Public Sub Refresh_All()
    
    ActiveWorkbook.RefreshAll
    
    DoEvents
    
    MsgBox "Refresh Complete"
    
    ' this works ok too if, manually, you uncheck enable background refresh on all queries before using it
    
    End Sub
androo235
  • 11
  • 4

1 Answers1

0

Here is what I use - it has been cobbled together over the years as Excel's queries have evolved from simple query objects to listobjects, and retains elements to handle both. It cycles through all worksheets and on each it updates all legacy queries and then all list object tables that are type 'xlSrcQuery'. For each it overrides the query definition to disable background refresh. Once all worksheets have been cycled through it goes back to do the same for pivot tables. A recent addition was to include updates to the Status Bar to reflect which sheet and query/table is being updated, since some of my files have quite a few sheets and objects to be updated and I thought it would be informative to be able to see the progress.

The code:

    Sub macUpdateAllQueryAndPivot()

    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim lo As ListObject
    Dim pt As PivotTable

    On Error Resume Next

    Application.ScreenUpdating = False

    'Update all query tables on the Sheet

        'Old Style QueryTables

        For Each ws In ActiveWorkbook.Worksheets

            For Each qt In ws.QueryTables
                Application.StatusBar = "Updating Sheet:  " & _
                ws.Name & "   --   Query&:  " & qt.Name
                qt.Refresh BackgroundQuery:=False
            Next qt
            
        'New Style ListObject Query Tables

        For Each lo In ws.ListObjects
            If lo.SourceType = xlSrcQuery Then
                Application.StatusBar = "Updating Sheet:  " & _
                ws.Name & "   --   Data Table:  " & lo.Name
                lo.QueryTable.Refresh BackgroundQuery:=False
                End If
            Next lo
    
        Next ws
    
    'Update all pivot tables on each Sheet

        For Each ws In ActiveWorkbook.Worksheets

            For Each pt In ws.PivotTables
                Application.StatusBar = "Updating Sheet:  " & _
                ws.Name & "   --   Pivot Table:  " & pt.Name
                pt.RefreshTable
                Next pt

            Next ws

    Application.StatusBar = False
    Application.CommandBars("PivotTable").Visible = False

    Application.ScreenUpdating = True

    Application.StatusBar = False

    Beep

    End Sub
David
  • 1