如何使用VBA编辑Power Query的源代码？

Question

我正在尝试使用VBA编辑我的其中一个查询的源代码。以下是目前的代码：

 Dim mFormula As String

 mFormula = _

 "let Source = Excel.Workbook(File.Contents(wbname), null, true) in Source"

 query1 = ActiveWorkbook.Queries.Add("LATEST", mFormula)

我之前在代码中设置了wbname。现在已经加入了“LATEST”，而不是删除它并重新读取，我只想改变源。这是否可能？

- BH57

Answer 1

您可以使用ActiveWorkbook.Queries.Item来获取所需查询，并使用Formula属性更新查询的公式，如下所示：

ActiveWorkbook.Queries.Item("LATEST").Formula = "let MyNewFormula = 1 + 1 in Source"

注意：此方法仅适用于Excel 2016或更高版本。

- Alejandro Lopez-Lago - MSFT

Answer 2

有点晚了，但是对于任何未来查看此内容的人，可以使用以下代码： Thisworkbook.Queries("LATEST").Formula = mFormula 请注意，ThisWorkbook比ActiveWorkbook更可取。

- Dean

---

当您使用此功能时，是否有仅更改公式的源部分的方法，还是必须指定整个PowerQuery公式？ - newuser2967

Answer 3

我认为你最好避免使用这些方法，因为它们可能会导致兼容性问题以及其他一些问题。

如果你学习M语言，你可能就不需要用VBA编辑代码了。

- Eugene

Answer 4

不建议使用Thisworkbook.Queries(index).Formula = "newFormula"来更改查询，但我没有发现其他更新连接的方法。

使用此方法的缺点是您无法更改连接的任何特定属性（源等），但您可以使用Split、Replace、Instr、Mid等函数来获得所需内容。

另一个缺点是新的“.formula”经常会出现语法错误。因此，您需要一个简单的错误处理程序。

- dasg7

Answer 5

感谢这篇文章。 一种操作和替换现有公式的方法如下：

Sub EditAllWorkbookFormuals()
For Each q In ThisWorkbook.Queries
 q.Formula = NewQuery(q.Formula)
Next
End Sub
Function NewQuery(MyQuery As String) As String
' Do you manipulation here of your MyQuery string
MsgBox MyQuery
' Use Select case, If, Inst, replace, and so on.
' .....
' Parse the new string back to the NewQuery
NewQuery = MyQuery
End Function

- Svein Arne Hylland

---

您的回答不够清晰。请 [编辑] 添加更多细节，以帮助其他人理解它如何回答提出的问题。您可以在帮助中心找到有关编写良好答案的更多信息。 - Community

Answer 6

这对我有用：

ActiveWorkbook.Queries.Add Name:="Qname", Formula:= _
"let" & "    Source = Pdf.Tables(File.Contents(" & """" & FullPathN & """" & "), [Implementation=""1.3""])," & _
"    Page1 = Source{[Id=""Page001""]}[Data]," & _
"    #""Promoted Headers"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & _
"    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Column" & "1"", type text}})" & _
"in" & "    #""Changed Type"""

FullPathN 是完整路径名，字符串；Qname 是查询名称，字符串

我不知道这在 MacOS excel 上是否可行。有人可以测试一下吗？我的任务是针对 MacOS 的。

- ozirisra