如何在 VBA 中自动执行电源查询？

How to automate a power query in VBA?

提问by JonnyBoy

I have data in sheet 1. Normally I go to power query and do my transformations, then close, and load to an existing sheet 2.

我在工作表 1 中有数据。通常我会打开电源查询并进行转换，然后关闭并加载到现有工作表 2。

I would like to automate this using VBA, where I can just run my power query automatically and populate the transformation to sheet 2.

我想使用 VBA 自动执行此操作，我可以在其中自动运行电源查询并将转换填充到工作表 2。

Macro recorder doesn't seem to allow me to record the steps. And there isn't much online about doing this.

宏记录器似乎不允许我记录步骤。网上没有太多关于这样做的内容。

Trying some simpler code:

尝试一些更简单的代码：

Sub LoadToWorksheetOnly()

'Sub LoadToWorksheetOnly(query As WorkbookQuery, currentSheet As Worksheet)
    ' The usual VBA code to create ListObject with a Query Table
    ' The interface is not new, but looks how simple is the conneciton string of Power Query:
    ' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name

    query = Sheets("Sheet6").Range("A1").value 'here is where my query from power query is. I put the text from power query avanced editor in another sheet cell.
    currentSheet = ActiveSheet.Name
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
        , Destination:=Sheets("target").Range("$A")).QueryTable
        .CommandType = xlCmdDefault
        .CommandText = Array("SELECT * FROM [" & query.Name & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With

End Sub

Here is my issue when trying to load to new sheet manually.

这是我尝试手动加载到新工作表时的问题。

采纳答案by QHarr

WIP:

在制品：

So how to write this to be sufficient? The bottom line is you should set your query up using the built-in tools, not VBA. You load your data via the appropriate method which can be from file, looping files in a folder, web, database.... the list goes on. You can import from external sources as well as load from internal. Have a look herefor more information on loading from external sources.

那么如何写这个才足够呢？底线是您应该使用内置工具而不是 VBA 来设置查询。您可以通过适当的方法加载数据，这些方法可以来自文件、文件夹、网络、数据库中的循环文件......列表还在继续。您可以从外部源导入，也可以从内部加载。有关从外部源加载的更多信息，请查看此处。

Once you have secured your source and it is loaded you will be presented with the query editor where you can perform your transformation steps.

一旦您保护好源并加载它，您将看到查询编辑器，您可以在其中执行转换步骤。

The point being that as you perform your steps using the UI, M code is written in the background and forms the basis of a re-usable query provided you don't change the source format or location.

关键在于，当您使用 UI 执行您的步骤时，M 代码在后台编写并构成可重用查询的基础，前提是您不更改源格式或位置。

In your case, when you have performed your steps and have a query as you wish you then close and load to sheet2.

在您的情况下，当您执行完步骤并根据需要进行查询后，您将关闭并加载到 sheet2。

At this step, the first time you are setting this up you will select sheet 2 as your close and load destination:

在这一步，第一次设置时，您将选择工作表 2 作为关闭和加载目的地：

NB: When you select existing sheet, ensure Sheet 2 already exists and you can manually edit Sheet2! in front of the suggested range.

注意：当您选择现有工作表时，请确保工作表 2 已经存在，您可以手动编辑工作表 2！在建议范围之前。

---

---

You are experiencing issues because you keep trying to recreate all of this with code.

您遇到问题是因为您一直试图用代码重新创建所有这些。

Don't. Set it up using the UI and load to sheet2. From then on, either open the query editor to edit the steps and/or refresh the query to load the existing sheet2 with new/refreshed data.

别。使用 UI 设置它并加载到 sheet2。从那时起，打开查询编辑器以编辑步骤和/或刷新查询以使用新的/刷新的数据加载现有的 sheet2。

---

---

Some of the available methods for refreshing your query:

刷新查询的一些可用方法：

The query will be refreshed by VBA/Manual refreshes to the sheet it resides in (Sheet2), or to the workbook itself e.g. Sheet2.Calculate, ThisWorkbook.RefreshAll, manually pressing the refresh workbook button in the data tab (these are all overkill really)

该查询将通过VBA被刷新/手动刷新到它驻留在（Sheet 2中），或以工作簿本身的片材例如Sheet2.Calculate，ThisWorkbook.RefreshAll在数据标签手动按压刷新工作簿按钮（这些都是矫枉过正真的）

More targeted methods:

更有针对性的方法：

VBA for the query table in sheet 2:

表 2 中查询表的 VBA：

ThisWorkbook.Worksheets("Sheet2").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False   

Change the above to the appropriate table etc.

将上述更改为适当的表等。

Right clicking in the querytable itself and selecting refresh:

右键单击查询表本身并选择刷新：

Click on the refresh button in the workbook queries window on the right hand side for the query in question (icon with green circling arrows)

单击右侧工作簿查询窗口中有关查询的刷新按钮（带有绿色圆圈箭头的图标）

---

---

The Ken PullsVBA way (minor edit from me)

在肯拉手VBA方式（从我小修改）

Option Explicit
Public Sub UpdatePowerQueries()
    ' Macro to update my Power Query script(s)

    Dim lTest As Long, cn As WorkbookConnection
    On Error Resume Next
    For Each cn In ThisWorkbook.Connections
        lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
        If Err.Number <> 0 Then
            Err.Clear
            Exit For
        End If
        If lTest > 0 Then cn.Refresh
    Next cn
    On Error GoTo 0
End Sub

---

---

There shouldn't be any real need for you to do all of this work via VBA. You may have some tricky data manipulation you feel more comfortable doing with VBA and then having PowerQuery access that processed data as source. You can fire off the whole lot by having a subroutine that calls the processing routine and then uses one of the VBA command methods listed above. There are more methods and I will add them when I have more time.

您不需要通过 VBA 完成所有这些工作。您可能会使用 VBA 进行一些棘手的数据操作，然后让 PowerQuery 访问将处理的数据作为源数据。您可以通过调用处理例程然后使用上面列出的 VBA 命令方法之一的子例程来启动整个批次。还有更多的方法，我会在有更多时间时添加它们。

---

---

Calculations:

计算：

If you have calculations that depend on the PowerQuery output you have 4 obvious immediate options:

如果您的计算依赖于 PowerQuery 输出，您有 4 个明显的即时选项：

1. Add these calculations where possible into PowerQuery. It supports calculated columns, user defined functions and lots more.
2. Add the PowerQuery output to the data model and use the data model to perform calculations including calculated fields. This will give you access to time intelligence functions as well.
3. Use VBA to add the calculations to the appropriate areas in sheet 2 if the range changes on refresh
4. If range doesn't change on refresh simply put your formulas out of the way.

1. 尽可能将这些计算添加到 PowerQuery 中。它支持计算列、用户定义的函数等等。
2. 将 PowerQuery 输出添加到数据模型并使用数据模型执行包括计算字段在内的计算。这也将使您能够访问时间智能功能。
3. 如果刷新时范围发生变化，则使用 VBA 将计算添加到工作表 2 中的相应区域
4. 如果刷新时范围没有改变，只需将您的公式放在一边。

回答by mer_curius

I disagree with the premise of the above answer; VBA is absolutely suitable for automating PowerQuery and is particularly efficient for repetitive work. The trick is to create first the query you need in PowerQuery, then use the Advanced Editor to capture the M. Copy it and store it, either in a cell in the workbook, or in a separate text file.

我不同意上述答案的前提；VBA 绝对适用于自动化 PowerQuery，尤其适用于重复性工作。诀窍是首先在 PowerQuery 中创建您需要的查询，然后使用高级编辑器捕获 M。将其复制并存储在工作簿的单元格中或单独的文本文件中。

The methodis described in detail by Gil Raviv. For convenience, I store my M in text files instead of the workbook and load it with:

该方法由 Gil Raviv 详细描述。为方便起见，我将 M 存储在文本文件而不是工作簿中，并使用以下命令加载它：

Function LoadTextFile(FullFileName As String) As String
  With CreateObject("Scripting.FileSystemObject")
    LoadTextFile = .OpenTextFile(FullFileName, 1).readall
  End With 
End Function

The nice thing about text files is that they are independent of excel and can re-used by many workbooks.

文本文件的好处在于它们独立于 excel 并且可以被许多工作簿重复使用。

Here is some M:

这是一些M：

let
// load the reference file (variables are shown in capitals;  
// variable values are replaced with strings from the excel control workbook)
    Source = Excel.Workbook(File.Contents(PATH_AND_NAME), null, true),
    ImportSheet = Source{[Item=SHEET_NAME,Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(ImportSheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ACCOUNT", type text}})
in
    #"Changed Type"

Once loaded into VBA (from either workbook or text file), the M can be edited within VBA, for example by substituting placeholder words, or by using the M command names to locate and alter lines if necessary, e.g.

一旦加载到 VBA（从工作簿或文本文件），M 可以在 VBA 中进行编辑，例如通过替换占位符词，或在必要时使用 M 命令名称来定位和更改行，例如

    ' create the M script to read the M file that will do the import
        M_Script = LoadTextFile(M_Source)

    ' insert the path
        M_Script = Replace(M_Script, "PATH_AND_NAME", """" & qSource & """") 

    ' insert the worksheet name
        If wksName <> "" Then M_Script = Replace(M_Script, "SHEET_NAME", """" & wksName & """")

The next step is to load the query. I do this using the technique described by Gilas follows:

下一步是加载查询。我使用Gil 描述的技术来做到这一点，如下所示：

Dim qry As WorkbookQuery

If DoesQueryExist(qName) Then 
    ' Deleting the query 
    Set qry = ThisWorkbook.Queries(qName) 
    qry.Delete 
End If 

Set qry = w.queries.Add(qName, M_Script, qSource)

' We check if data should be loaded to Data Model 
shouldLoadToDataModel = ThisWorkbook.Worksheets(1).Cells(13, "D") 

' We check if data should be loaded to worksheet 
shouldLoadToWorksheet = ThisWorkbook.Worksheets(1).Cells(13, "E") 

If shouldLoadToWorksheet Then 
    ' We add a new worksheet with the same name as the Power Query query 
    Set currentSheet = Sheets.Add(After:=ActiveSheet) 
    currentSheet.Name = qName 

    If Not shouldLoadToDataModel Then 
        ' Let's load to worksheet only 
        LoadToWorksheetOnly qry, currentSheet 
    Else 
        ' Let's load to worksheet and Data Model 
        LoadToWorksheetAndModel qry, currentSheet 
    End If 
ElseIf shouldLoadToDataModel Then 
    ' No need to load to worksheet, only Data Model 
    LoadToDataModel qry 
End If 

Gil's code allows for importing data to either the data model or a worksheet. The OP requires the second, and if the method is followed, the transformed data should appear in the worksheet.

Gil 的代码允许将数据导入数据模型或工作表。OP 需要第二个，如果遵循该方法，则转换后的数据应出现在工作表中。

回答by MirelesJ

A little late to this conversation, but I found it rather easy to update a Power Query in VBA by editing the Formula property.

这次谈话有点晚了，但我发现通过编辑公式属性在 VBA 中更新 Power Query 相当容易。

First update the let..informula. Then refresh the connections that use it

首先更新let..in公式。然后刷新使用它的连接

Dim pqFormula as String
pqFormula = "let..in"

Dim pqName as String
pqName = "<Name of the connection>"

' Update the formula for the specific power query.
ThisWorkbook.queries(pqName).Formula = pqFormula

' Refresh the connection
ThisWorkbook.Connections("Query - " & pqName).Refresh

All tables that rest on that connection should immediately update.

位于该连接上的所有表都应立即更新。

Regards,

问候，