VBA Code to Refresh Power Query

[Centaur]

Hello,

I am importing an excel file's power query output into MS Access. 

I am wondering if I can add code to the below to refresh the Power Query automatically by VBA prior to importing into Access?  I sometimes forget to refresh the Power Query. 

For example, I have the following code on a button's click event: 

Private Sub cmdImportPC_Click()
     DoCmd.RunMacro "mcrImportPC" ' this imports the data from excel power query.  Refresh PQ first! 

End Sub

the file location is here (if needed):

C:\Users\Me\Box\Financing\ProjectCostsPQ_FE.xlsx

thank you

[jennratten · Accepted solution]

You can create a macro to refresh Power Query and just run it before importing to Access.  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

[Centaur]

Ahh.   Nice.  thank you for the tip!  Much appreciated.