通过 VBA 自动更新 Power Query 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36902975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Auto-updating Power Query Connection via VBA
提问by Salim
I have a Power Query set in myexcel.xlsx. I set its connections's properties as thisand this.
我在 myexcel.xlsx 中设置了 Power Query。我将其连接的属性设置为 this和this。
I wrote a VBA code like the following
我写了一个像下面这样的VBA代码
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?
当我手动打开 myexcel.xslx 时，Power Query 连接会更新。但是通过 VBA 代码却没有。我应该补充一点，我使用老式 Excel Connection 对此进行了测试，并且通过 VBA 代码可以正常工作。但问题在于 Power Query 连接。有什么想法吗？
回答by James Heffer
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:
这实际上相当容易，如果您检查现有连接，您可以看到电源查询连接名称的开头方式，它们都以“Query -”开头，然后名称...我的项目，我写了这个有效的代码：
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...
这只是意味着如果连接的名称，从左侧开始并向右侧移动的前 8 个字符（前 8 个字符）等于字符串“查询 - ”，那么...
- 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 - ")...
- 如果您知道查询的名称，请将 8 调整为一个数字，该数字将指示您的查询名称中的字符数，然后使语句等于您的查询连接名称，而不是所有电源查询连接的开头（ “询问 - ”）...
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.
如果您有大量电源查询，我建议不要一次更新所有电源查询。您的计算机可能会崩溃，并且您的 excel 可能没有自动保存。
Happy coding :)
快乐编码:)
回答by Hila DG
Since you're using Power Query, which is different to Power Pivot, you have two options:
由于您使用的是不同于 Power Pivot 的 Power Query，您有两个选择：
- Automatic Update the data source when the file is open - (http://www.excel2013.info/power-query/automatic-update/)
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
- 文件打开时自动更新数据源 - ( http://www.excel2013.info/power-query/automatic-update/)
编写一个 VBA 脚本来更新它
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/
回答by Nick Van Maele
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:
如果通过循环刷新所有连接，则无法控制发生这种情况的顺序。如果您需要控制序列，或者如果您只需要刷新几个 Power Queries，这也是一个选项：
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.
第一个函数刷新一个 Power Query。括号中的函数参数是在 Excel 的“查询和连接”窗格中可见的查询名称。请注意这是如何通过添加“查询 - ”作为前缀来转换为连接名称的。
The second function then uses the first function to call specific Power Queries in a specific order, giving you full control.
然后第二个函数使用第一个函数以特定顺序调用特定的 Power Queries，让您完全控制。
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
回答by Viper
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 :)
当您打开文件时，宏将自动运行，并且数据将被保存，最后一个文件也将保存为 TXT 格式:)
