Hello ! I'm working on a personnal VBA code, and when I use this : The result on my Excel isn't the same as my code on VBA when I debug. What is surprising is the fact that on an other Excel, with the same VBA code, but just other SharePoint Db connection, there is no problem. The result that I need is the result from the VBA test. Thanks a lot for your help ! Jb

VBA Update different of UpdateAll from Excel

Sub RefreshUsingCommandbarbutton() Dim oCMB As CommandBar 'Add temporary commandbar Set oCMB = Application.CommandBars.Add("MyBar", msoBarPopup, False, True) 'Add refreshall button and click it With oCMB.Controls.Add(ID:=1952) .Execute End With 'Remove temporary commandbar Application.CommandBars("MyBar").Delete Set oCMB = Nothing End Sub

JKPieterse

Silver Contributor

Feb 20, 2018

Refresh All is often problematic, especially if you have queries that return their results to tables and then have pivot tables which use those tables. The best way to do this is to use code which does all refreshes in the correct order:

Option Explicit

Sub MyRefreshAll()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Dim oQt As QueryTable
    Dim oPc As PivotCache
    Dim oPQ As WorkbookQuery
    'First do all querytable son all worksheets
    For Each oSh In Worksheets
        For Each oQt In oSh.QueryTables
            oQt.Refresh False
        Next
        For Each oLo In oSh.ListObjects
            If Not oLo.QueryTable Is Nothing Then
                oLo.QueryTable.Refresh False
            End If
        Next
    Next
    'Then do all PowerQuery queries
    UpdatePowerQueriesOnly
    'finally, update pivots
    For Each oPc In ActiveWorkbook.PivotCaches
        oPc.Refresh
    Next
End Sub

Public Sub UpdatePowerQueriesOnly()
    Dim lTest As Long, cn As WorkbookConnection
    On Error Resume Next
    For Each cn In ThisWorkbook.Connections
        lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1")
        If Err.Number <> 0 Then
            Err.Clear
            Exit For
        End If
        If lTest > 0 Then cn.Refresh
    Next cn
End Sub

The code in UpdatePowerQueriesOnly courtesy:

https://www.excelguru.ca/blog/2015/08/19/update-refresh-power-queries-with-vba/

Jb Jeannot
Copper Contributor
Feb 21, 2018
Hello Jan,

First, thanks for your answer.

I tried it, but the actualization isn't corresponding to my expectations.

When I debug line to line, my columns are updated normaly, and for the same code (exactly the same code on my cell), I got what I want. When I don't debug, and I refresh with "Refresh all", my datas are wrong. Is that possible to overload this button by VBA code ? What is the name of the associated function ?

Thank you !
- JKPieterse
  Silver Contributor
  Feb 22, 2018
  What about this method then:
  
  Sub RefreshUsingCommandbarbutton() Dim oCMB As CommandBar 'Add temporary commandbar Set oCMB = Application.CommandBars.Add("MyBar", msoBarPopup, False, True) 'Add refreshall button and click it With oCMB.Controls.Add(ID:=1952) .Execute End With 'Remove temporary commandbar Application.CommandBars("MyBar").Delete Set oCMB = Nothing End Sub

Forum Discussion

VBA Update different of UpdateAll from Excel

Share

Resources