如何在VBA中自动执行功率查询? [英] How to automate a power query in VBA?

问题描述

我在工作表1中有数据.通常，我会进行功率查询并进行转换，然后关闭并加载到现有工作表2中.

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.

我想使用VBA将其自动化，在这里我可以自动运行电源查询并将转换填充到工作表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.

宏记录器似乎不允许我记录这些步骤.而且，有关此操作的在线信息并不多.

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

尝试一些简单的代码:

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$1")).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.

推荐答案

在制品:

那么如何写就足够了?最重要的是，您应该使用内置工具(而不是VBA)来设置查询.您可以通过适当的方法加载数据，这些方法可以来自文件，也可以循环访问文件夹，Web，数据库中的文件....您可以从外部来源导入，也可以从内部来源加载.看看此处，以获取有关从外部源进行加载的更多信息.

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 here for more information on loading from external sources.

在您保护好源并将其加载后，将向您显示查询编辑器，您可以在其中执行转换

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.

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

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.

以您为例，当您执行了步骤并根据需要进行查询时，请关闭并加载到sheet2.

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

在此步骤中，首次设置时，您将选择工作表2作为关闭并加载目的地:

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

注意:选择现有工作表时，请确保工作表2已经存在，并且可以手动编辑工作表2！在建议的范围之前.

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

您遇到了问题，因为您一直尝试使用代码来重新创建所有这些内容.

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

不要.使用UI进行设置并将其加载到sheet2.然后，打开查询编辑器以编辑步骤和/或刷新查询以使用新的/刷新的数据加载现有的sheet2.

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.

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

Some of the available methods for refreshing your query:

查询将通过VBA刷新/手动刷新到其所在的工作表(Sheet2)或工作簿本身，例如Sheet2.Calculate，ThisWorkbook.RefreshAll，手动按数据"选项卡中的刷新工作簿"按钮(这些实际上都是多余的)

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)

更多有针对性的方法:

工作表2中的查询表的VBA:

VBA for the query table in sheet 2:

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)

肯拉 VBA方式(由我进行小修改)

The Ken Pulls VBA way (minor edit from me)

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

---

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

---

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.

计算:

如果您的计算依赖于PowerQuery输出，则有4个明显的立即选择:

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

1. 将这些计算尽可能地添加到PowerQuery中.它支持计算列，用户定义的函数以及更多其他功能.
2. 将PowerQuery输出添加到数据模型，并使用该数据模型执行包括已计算字段在内的计算.这也使您可以使用时间智能功能.
3. 如果范围在刷新时更改，请使用VBA将计算结果添加到工作表2中的适当区域
4. 如果刷新后范围没有变化，只需将您的公式排除在外即可.

这篇关于如何在VBA中自动执行功率查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！