通过VBA自动更新电源查询连接 [英] Auto-updating Power Query Connection via VBA

问题描述

我在myexcel.xlsx中设置了超级查询.我将其连接的属性设置为 此和

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

我写了如下的VBA代码

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

当我手动打开myexcel.xslx时，Power Query连接会更新.但是通过VBA代码却没有.我应该补充一点，我使用老式的Excel Connection对此进行了测试，并且可以通过VBA代码正常工作.但是问题出在Power Query连接上.有什么想法吗?

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?

推荐答案

实际上很简单，如果您检查现有连接，则可以看到电源查询连接名称的开始方式，它们在感觉它们以"Query-"开头，然后是名称...在我的项目中，我编写了如下代码:

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

这仅表示如果连接名称，则从LEFT开始并向RIGHT的前8个字符等于字符串"Query-"，然后...

• ，如果您知道查询的名称，则将8调整为一个数字，该数字将指示查询名称中的字符数，然后使该语句等于您的查询连接名称，而不是使用所有首字母开头查询连接(查询-")...

如果您有大量查询，建议不要立即更新所有电源查询.您的计算机可能会崩溃，并且您的Excel可能没有自动保存.

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.

快乐的编码:)

这篇关于通过VBA自动更新电源查询连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！