如何参数化 ActiveWorkbook.Queries.Add

Question

请帮助解决我在 VBA 中遇到的以下问题

ActiveWorkbook.Queries.Add ' returns error when replacing URL with a variable having the same URL

具有固定 URL 的实际工作代码：

ActiveWorkbook.Queries.Add Name:="projects", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Source = Json.Document(Web.Contents(""https://.............."", [Headers=[Authorization=""Bearer ########-####-####-...........""]]))," & Chr(13) & "" & Chr(10) & "    #""Converted to Table"" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"" = Table.ExpandRecordColumn(#""Converted to Table"", ""Colu" & _
"mn1"", {""id"", ""name""}, {""Column1.id"", ""Column1.name""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"""

Dim pURL As String
pURL = "https://........................"
ActiveWorkbook.Queries.Add Name:="projects", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Source = Json.Document(Web.Contents(“”” & pURL& “””), [Headers=[Authorization=""Bearer ########-####-####-...........""]]))," & Chr(13) & "" & Chr(10) & "    #""Converted to Table"" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"" = Table.ExpandRecordColumn(#""Converted to Table"", ""Colu" & _
"mn1"", {""id"", ""name""}, {""Column1.id"", ""Column1.name""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"""

我想用参数替换 VBA 中的源和标题字段，但:(不起作用，有人可以对上述查询进行吗？

谢谢

Answer 1

问题不在于网址，问题在于名称没有改变。 试试这个：

ActiveWorkbook.Queries.Add Name:="projects2", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Source = Json.Document(Web.Contents(“”” & pURL& “””), [Headers=[Authorization=""Bearer ########-####-####-...........""]]))," & Chr(13) & "" & Chr(10) & "    #""Converted to Table"" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"" = Table.ExpandRecordColumn(#""Converted to Table"", ""Colu" & _
"mn1"", {""id"", ""name""}, {""Column1.id"", ""Column1.name""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"""

实际上，您可以添加一个连接(您可以在数据>连接下查看(，但每个连接都需要一个唯一的名称，您不允许添加具有现有名称的连接。另外，在不再需要连接后，请注意删除连接，否则您的工作表将变得非常慢，具体取决于连接量。