作者:最棒的寒冬腊月_531 | 来源:互联网 | 2022-12-10 13:32
我在表1中有数据.通常我会进行电源查询并进行转换,然后关闭,并加载到现有工作表2.
我想使用VBA自动执行此操作,我可以自动运行电源查询并将转换填充到第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
尝试手动加载到新工作表时,这是我的问题.
1> QHarr..:
WIP:
那么如何写这个就足够了?最重要的是,您应该使用内置工具设置您的查询.不是VBA.您可以通过适当的方法加载数据,该方法可以来自文件,循环文件夹,Web,数据库....列表继续.您可以从外部源导入以及从内部加载.有关从外部源加载的更多信息,请查看此处.
一旦您保护了源并加载了源,您将看到查询编辑器,您可以在其中执行转换步骤.
关键是当您使用UI执行步骤时,M代码将在后台编写,并形成可重复使用查询的基础,前提是您不要更改源格式或位置.
在您的情况下,当您执行了步骤并根据需要进行查询时,请关闭并加载到sheet2.
在此步骤中,第一次进行此设置时,您将选择工作表2作为关闭和加载目标:
注意:当您选择现有工作表时,请确保工作表2已存在,您可以手动编辑Sheet2!在建议的范围之前.
您遇到了问题,因为您一直在尝试使用代码重新创建所有这些问题.别.使用UI进行设置并加载到sheet2.从那时起,打开查询编辑器以编辑步骤和/或刷新查询以使用新/刷新的数据加载现有的sheet2.
一些用于刷新查询的可用方法:
该查询将通过VBA /手动刷新刷新以它驻留在(Sheet 2中),或以工作簿本身的片材例如Sheet2.Calculate
,ThisWorkbook.RefreshAll
在数据标签手动按压刷新工作簿按钮(这些都是矫枉过正真的)
有针对性的方法:
表2中查询表的VBA:
ThisWorkbook.Worksheets("Sheet2").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
将上述内容更改为相应的表格等.
右键单击查询表本身并选择刷新:
单击右侧工作簿查询窗口中的刷新按钮以查询相关查询(带有绿色圆圈箭头的图标)
在肯拉手 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
您不应该真正需要通过VBA完成所有这些工作.您可能有一些棘手的数据操作,您觉得使用VBA更舒服,然后使用powerquery访问处理数据作为源.你可以通过调用处理例程然后使用上面列出的vba命令方法之一来触发整个批次.还有更多方法,我会在有更多时间时添加它们.
计算:
如果您的计算依赖于powerquery输出,那么您有4个明显的即时选项:
尽可能将这些计算添加到powerquery中.它支持计算列,用户定义函数等等.
将powerquery输出添加到数据模型,并使用数据模型执行包括计算字段的计算.这将使您可以访问时间智能功能.
如果范围在刷新时更改,则使用VBA将计算添加到工作表2中的相应区域
如果范围在刷新时没有改变,只需将您的公式放在一边.
2> mer_curius..:
我不同意以上回答的前提;VBA绝对适合于PowerQuery的自动化,并且对于重复工作特别有效。诀窍是先在PowerQuery中创建所需的查询,然后使用“高级编辑器”捕获M。将其复制并存储在工作簿的单元格中或单独的文本文件中。
该方法由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:
Function LoadTextFile(FullFileName As String) As String
With CreateObject("Scripting.FileSystemObject")
LoadTextFile = .OpenTextFile(FullFileName, 1).readall
End With
End Function