Auto-updating Power Query Connection via VBA

Question

I have a Power Query set in myexcel.xlsx. I set its connections's properties as this and this.

I wrote a VBA code like the following

Sub UpdateData()
    Dim filename As String
    Dim wbResults As Workbook
   filename = "C:\myexcel.xlsx"
   Set wbResults = Workbooks.Open(filename)

   ActiveWorkbook.RefreshAll
   wbResults.Close savechanges:=True

End Sub

When I open the myexcel.xslx manually, the Power Query connection updates. But through VBA code it doesn't. I should add I tested this with an old fashioned Excel Connection andit works fine through VBA code. But the problem is with Power Query connections. Any thoughts?

Answer 1

It is actually rather easy, if you check out your existing connections, you can see how the power query connection name starts, they're all the same in the sense that they start with "Query - " and then the name... In my project, I've written this code which works:

Sub RefreshQuery()
Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
    If Left(con.name, 8) = "Query - " Then
    Cname = con.name
        With ActiveWorkbook.Connections(Cname).OLEDBConnection
            .BackgroundQuery = False  'or true, up to you
            .Refresh
        End With
    End If
Next
End Sub

This will refresh all your power queries, but in the code you can see it says:

If Left(con.name, 8) = "Query - " Then

This just means if the name of the connection, the first EIGHT characters starting from the LEFT and moving towards the RIGHT (the first 8 characters) equals the string "Query - " then...

• and if you know the name of your query, adjust the 8 to a number that will indicate the amount of characters in your query name, and then make the statement equal to your query connection name, instead of the start of all power query connections ("Query - ")...

I'd advise NEVER updating all power queries at once IF you have a large amount of them. Your computer will probably crash, and your excel may not have auto saved.

Happy coding :)