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?

Tamworth

I wouldn't suggest using the RefreshAll method, AT ALL, EVER! Simply because, in my workbook, I'll have over 300 queries, if we accidentally hit refresh all, the computer will crash. You can refresh a query by it's name, or if the query starts with certain characters. If you have your queries organised like me and use a naming convention, you can refresh queries "grp1_Qry_1", "grp1_Qry_2" and so on, you can - for instance: refresh all quereis that start with "grp1_". Much better than refreshing all queries. Little tip * power queries connection names start with "Query - "... See my answer :) – Winchell

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 :)

Winchell

Answer 2

If you refresh all connections via a loop, you cannot control the order in which this happens. If you need control of the sequence, or if you need to refresh just a couple of Power Queries, this is also an option:

The first function refreshes one single Power Query. The argument of the function in parentheses is the name of the query as visible on the "Queries and connections" pane in Excel. Note how this is translated into the connection name by adding "Query - " as prefix.

The second function then uses the first function to call specific Power Queries in a specific order, giving you full control.

Public Sub RefreshSpecificPowerQuery(pqName As String)

Dim con As WorkbookConnection
Dim conName As String

conName = "Query - " & pqName

With ActiveWorkbook.Connections(conName).OLEDBConnection
    .BackgroundQuery = False    'or TRUE, as the case requires
    .Refresh
End With

End Sub

Public Sub RefreshListOfPowerQueries()

Call RefreshSpecificPowerQuery("pqMyFirstPowerQueryName")
Call RefreshSpecificPowerQuery("pqMySecondPowerQueryName")

End Sub

Heartthrob

Answer 3

Since you're using Power Query, which is different to Power Pivot, you have two options:

1. Automatic Update the data source when the file is open - (http://www.excel2013.info/power-query/automatic-update/)

2. Write a VBA script for updating it

   For Each cn In ThisWorkbook.Connections If cn = "Power Query – Employee" Then cn.Refresh Next cn End Sub

copied from here: https://devinknightsql.com/category/power-query/

Necrophobia

I have done both. It does not work. I even wrote the code as this: Dim cn As WorkbookConnection For Each cn In ThisWorkbook.Connections cn.Refresh Next cn – Tamworth

Could you please add a picture of the other tab of the connection? – Necrophobia

@Hila_DG Thanks for answering! I just added – Tamworth

(I don't know, but maybe) try to name the data area in Sheet1 (make it a named table) and then change the connection from SQL to Table? – Necrophobia

Don't think the power query connection names start with that, but rather just "Query - ", maybe that's why it didn't work – Winchell

Edited your answer Hila DG – Winchell

Answer 4

Just in response to James Heffer’s post which worked for me after some tweaking. If you live in non-English speaking country your connection changes name. You can see connection name by adding a Debug.Print command like here

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

For Each con In ActiveWorkbook.Connections
    Debug.Print con
    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

After you run the code, it will show you the localized names. Mine is called “Forespørgsel – LevBonusData” Hope it helps somebody 😊

Weis

Answer 5

You can try this code as well

Sub auto_open()
    ActiveWorkbook.RefreshAll
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    ThisWorkbook.Save
    ChDir "D:\Data"
    ActiveWorkbook.SaveAs Filename:="D:\Data\abc.txt", FileFormat:=xlText, CreateBackup:=False
    Application.Quit
End Sub

When you will open file at that time macro will run automatically and also data will be saved and in last file will be saved as TXT format as well :)

Wiseacre