如何使用 VBA 编辑电源查询的来源？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40119185/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to edit the source of a power query using VBA?
提问by BH57
I am trying to edit the source of one of my queries using VBA. This is what I have so far:
我正在尝试使用 VBA 编辑我的一个查询的来源。这是我到目前为止：
Dim mFormula As String
mFormula = _
"let Source = Excel.Workbook(File.Contents(wbname), null, true) in Source"
query1 = ActiveWorkbook.Queries.Add("LATEST", mFormula)
I set wbnamepreviously in my code. "LATEST" is already added, instead of delete it and read it, I would just like to change the source. Is this possible?
我wbname之前在我的代码中设置过。“最新”已添加，与其删除并阅读它，我只想更改来源。这可能吗？
回答by Alejandro Lopez-Lago - MSFT
You can use ActiveWorkbook.Queries.Itemto get the query you want and use the Formulaproperty to update the query's formula, like so:
您可以使用ActiveWorkbook.Queries.Item来获取所需的查询并使用该Formula属性来更新查询的公式，如下所示：
ActiveWorkbook.Queries.Item("LATEST").Formula = "let MyNewFormula = 1 + 1 in Source"
ActiveWorkbook.Queries.Item("LATEST").Formula = "let MyNewFormula = 1 + 1 in Source"
Note: this only works on Excel 2016 or later.
注意：这仅适用于 Excel 2016 或更高版本。
回答by Eugene
I beleive you'd better avoid such methods as they can cause compatibility problems as well as some other.
我相信您最好避免使用此类方法，因为它们会导致兼容性问题以及其他一些问题。
If you learn M, you probably won't need to edit code with VBA.
如果您学习 M，您可能不需要使用 VBA 编辑代码。
回答by Dean
A bit late to the party, but additionally, for anyone who views this moving forward one can use:
聚会有点晚了，但另外，对于任何看到这一进展的人，都可以使用：
Thisworkbook.Queries("LATEST").Formula = mFormula
Thisworkbook.Queries("LATEST").Formula = mFormula
Note, ThisWorkbookis preferable to ActiveWorkbook.
请注意，ThisWorkbook优于ActiveWorkbook.
