Auto-updating Power Query Connection via VBA

问题:

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?

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

回答2:

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/

回答3:

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

回答4:

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