有没有办法从 Power Query M 调用 Excel VBA 函数?

huangapple go评论95阅读模式
英文:

Is there a way to call Excel VBA function from Power Query M?

问题

有没有一种方法可以从Power Query M中调用Excel VBA函数以获取值以供进一步处理?

通过M函数可以获取单元格的值,但我还想从文本框中获取值。
在VBA中,我可以获取文本,但我找不到是否可以从M查询中调用Excel VBA函数。

更详细地说:
实际上,我想获取JSON以在Power Query中解析。如果我选择“从JSON文件获取数据”,它可以完美运行。但它使电子表格依赖于文件的位置,如果我将其发送给其他用户,用户应该将JSON文件放在与我的计算机上相同的位置,这会带来一些复杂性。
我尝试将JSON文件复制到单个Excel单元格中。它可以工作,但似乎有文件大小的限制,我的文件比Excel允许的单元格大。
另一个解决方案是将JSON文本放入文本框中。我可以使用VBA从文本框中提取内容,但无法将其传递给Power Query。
有什么想法吗?

英文:

Is there a way to call Excel VBA function from Power Query M to obtain value for further processing?

It is possible to get cell value via M function, but I also want to get value from Text Box.
In VBA I can get that text, but I could not find if I can call that Excel VBA function from M query.

In more details:
I actually want to get JSON to parse with Power Query. It works perfectly if I go with "Get Data from File JSON". But it makes the spreadsheet dependent on the location of the file, if I send it to other user, the user should place JSON file into the same location as on my computer which present some complications.
I tried to copy that JSON file into single Excel cell. It works but it looks like there is a limitation for the file size, my files are bigger that Excel allows in the cell.
Other solution would be to place that JSON text into Text Box. I can extract the context from Text Box with VBA but I cannot get it into Power Query.
Any ideas?

答案1

得分: 3

在Power Query中,您可以让某人将位置放入一个命名范围,然后通过以下方式从命名范围(单元格)获取文件路径:

let NameValue = Excel.CurrentWorkbook(){[Name="rangenamehere"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(NameValue), null, true),

或者,如果您想要使用VBA来进行文件提示,可以让VBA调用Power Query,而不是反过来:

  1. 创建一个命名范围,比如aaa。

  2. 使用VBA通过文件提示来填充它:

Sub prompt()
    Dim FName As Variant
    FName = Application.GetSaveAsFilename("", "Data file (*.xl*),*.xl*", 1)
    If FName = False Then
        MsgBox "False"
        Exit Sub
    End If
    Range("aaa").Value = FName
End Sub
  1. 在Power Query中引用您设置的命名范围:
let NameValue = Excel.CurrentWorkbook(){[Name="aaa"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(NameValue), null, true),
  1. 在VBA的最后添加以下代码来刷新所有查询或特定查询:

刷新所有查询:

ActiveWorkbook.RefreshAll

或刷新特定查询:

ActiveWorkbook.Queries("QueryNameHere").Refresh
英文:

In powerquery you can have someone put a location in a named range, then grab the filepath from the named range (cell) by using

let NameValue= Excel.CurrentWorkbook(){[Name="rangenamehere"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(NameValue), null, true),

Or if you wanted the VBA route for file prompt, have the VBA call the PowerQuery, not the other way around

1 Create a range name, here aaa

2 Use VBA to populate it using a file prompt

Sub prompt()
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "Data file (*.xl*),*.xl*", 1)
If FName = False Then
MsgBox "False"
Exit Sub
End If
Range("aaa").Value = FName
End Sub

3 Refer to the named range in powerquery you set up

let NameValue= Excel.CurrentWorkbook(){[Name="aaa"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(NameValue), null, true),

4 Tack on code at end of VBA to refresh all queries or specific query

ActiveWorkbook.RefreshAll

or

ActiveWorkbook.Queries("QueryNameHere").Refresh

huangapple
  • 本文由 发表于 2023年7月31日 23:54:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76805251.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定