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

Question

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

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

宏记录器似乎不允许我记录步骤。网上关于这样做的信息并不多。

尝试一些更简单的代码:

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

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

Answer 1

VBA 绝对适合自动化 PowerQuery，并且对于重复性工作特别高效。诀窍是首先在 PowerQuery 中创建所需的查询，然后使用高级编辑器捕获 M。将其复制并存储在工作簿的单元格中或单独的文本文件中。

method吉尔·拉维夫 (Gil Raviv) 对此进行了详细描述。为了方便起见，我将 M 存储在文本文件而不是工作簿中，并加载它:

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

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

这是一些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"

一旦加载到 VBA(从工作簿或文本文件)中，就可以在 VBA 中编辑 M，例如通过替换占位符单词，或者在必要时使用 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 & """")
        

下一步是加载查询。我使用 technique described by 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 

这是 LoadToDataModel 函数:

Option Explicit
Function LoadToDataModel(w As Workbook, query As WorkbookQuery, error As Integer) As Boolean
On Error GoTo Load_Error
    ' This code loads the query to the Data Model
    w.Connections.Add2 "Query - " & query.Name, _
        "Connection to the '" & query.Name & "' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
        , """" & query.Name & """", 6, True, False

    LoadToDataModel = True
    
Load_Exit:
    Exit Function
    
Load_Error:
    LoadToDataModel = False
    error = Err.Number
    Resume Load_Exit
End Function

以及 LoadToWorksheetOnly 的函数(感谢 Wayback Machine):

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 
     
    With currentSheet.ListObjects.Add(SourceType:=0, Source:= _ 
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _ 
        , Destination:=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 

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