VBA Code to Refresh Power Query

Question

Hello,&nbsp;I am importing an excel file's power query output into MS Access.&nbsp;I am wondering if I can add code to the below to refresh the Power Query automatically by VBA prior to importing into Access?&nbsp; I sometimes forget to refresh the Power Query.&nbsp;&nbsp;For example, I have the following code on a button's click event:&nbsp;&nbsp;Private Sub cmdImportPC_Click()&nbsp; &nbsp; &nbsp;DoCmd.RunMacro "mcrImportPC" ' this imports the data from excel power query.&nbsp; Refresh PQ first!&nbsp;End Sub&nbsp;the file location is here (if needed):C:\Users\Me\Box\Financing\ProjectCostsPQ_FE.xlsx&nbsp;thank you

jennratten · Accepted Answer

You can create a macro to refresh Power Query and just run it before importing to Access.&nbsp; Here is some sample code to refresh queries in Excel:
Sub Refresh_All_Data_Connections()

'-------------------------------------------------------------------------------------
'Purpose:   Refresh all data connections in the active workbook
'           after the sub below turns off all background refreshing
'
'Source:    https://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba
'
'Editor:    Jenn Ratten
'
'Revisions
'01/30/19   Enhanced the speed
'-------------------------------------------------------------------------------------
    
    For Each objConnection In ActiveWorkbook.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

End Sub

Sub ChangeConnectionRefreshModeAndRefreshAll()

'-------------------------------------------------------------------------------------
' Purpose:   Turn off background refreshing for all workbook connections.
'            This prevents hangs and crashes
'
' Source:    https://www.myonlinetraininghub.com/excel-forum/vba-macros/pause-macro-until-power-queries-finished-refreshing
'
' Editor:    Jenn Ratten
'
' Revisions
' 01/30/19   Enhanced the speed
'-------------------------------------------------------------------------------------
    
    Dim Connection As WorkbookConnection
    Dim bugfix As Integer
    
        For bugfix = 1 To 2

On Error Resume Next
            
            For Each Connection In ActiveWorkbook.Connections
            
                With Connection
                
                    If (.Type = xlConnectionTypeODBC) Then
                        .ODBCConnection.BackgroundQuery = False
                    
                    Else
                    
                        If (.Type = xlConnectionTypeOLEDB) Then
                            .OLEDBConnection.BackgroundQuery = False
                    
                        End If
                    
                    End If
                
                End With
            
            Connection.Refresh
            
            Next Connection
            
        Next bugfix

End Sub
&nbsp;
&nbsp;

Forum Discussion

VBA Code to Refresh Power Query

2 Replies

Related content

Excel Power Query Refresh

Refresh Direct Query not refreshing

Power Bi refresh

Power Query dates

Data transform using power query