使用Powerquery复制工作表

Question

我有一个叫做“模板”的工作表，里面有一个PowerQuery "TEMPLATE_Query“，可以工作。Powerquery从网站请求CSV文件。

现在，在循环中使用VBA，我复制了这个工作表的N个副本，生成了名为"Template (X)“的工作表和名为"TEMPLATE_Query (X)”的Powerquerys。在我的X=1..N代码中，我修改了Powerquery公式以请求一个不同的CSV文件。在此之前，一切正常。Powerquerys在所有这些工作表中运行和接收不同的CSV文件。

这里有个问题：

当我在创建副本的循环中使用VBA更改工作表的名称时，Powerquerys随后就会失败。比如“下载失败，仅连接”。因此，很明显，Powerquery不再引用正确的页名了。当我重命名Powerquery并将工作表名称保持不变时，也会发生同样的情况。

我的目标是在我的VBA循环中重命名工作表和Powerquery。

但是，为什么这会中断查询呢？

Answer 1

不久前我也遇到了同样的问题。如果我没有记错，它会中断，因为查询仍然希望访问具有不同名称的内容。我不知道为什么Excel在重命名查询时不更改引用。如果您用右键手动重命名它，它甚至不会自动更改它。如果您查看该查询，右键单击它，然后切换到属性或中间选项卡被调用的内容，您可以在那里看到一些详细信息。

长话短说，我就是这样修好我的：

Sub Create_new_connection()

'On Error Resume Next
'Count the current amount of queries and save that number to refer to it later
QueryCount = ThisWorkbook.Queries.Count

'Copy the template and rename it
ThisWorkbook.Sheets("Template").Copy after:=ThisWorkbook.Sheets("Template")
ThisWorkbook.Sheets(3).Name = "Put a name here"

'Change the name of the query
ThisWorkbook.Queries.Item(QueryCount + 1).Name = "New Query Name"

'Change the names of the new table
ThisWorkbook.Sheets(3).ListObjects(1).Name = "I had a table I wanted to rename"

'Change the formula of the new connection
NewFormula = Replace(ThisWorkbook.Queries.Item(1).Formula, ThisWorkbook.Sheets("Create New List").ListObjects("Template").DataBodyRange(1, 1), ThisWorkbook.Sheets("Create New List").ListObjects("FAUF").DataBodyRange(1, 1))
ThisWorkbook.Queries.Item(QueryCount + 1).Formula = NewFormula

'Connect the new table to the new connection and
ThisWorkbook.Sheets(3).ListObjects(1).QueryTable.WorkbookConnection = "Abfrage - " & ThisWorkbook.Queries.Item(QueryCount + 1).Name
ThisWorkbook.Sheets(3).ListObjects(1).QueryTable.Connection = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & """" & ThisWorkbook.Queries.Item(QueryCount + 1).Name & """" & ";Extended Properties=" & """" & """"
ThisWorkbook.Sheets(3).ListObjects(1).QueryTable.CommandText = "SELECT * FROM [" & ThisWorkbook.Queries.Item(QueryCount + 1).Name & "]"

'Refresh the new connection
ThisWorkbook.Sheets(3).ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

End Sub

第二到最后一串，3修改连接是重要的。这是在一个德语Excel tho，所以您可能需要更改"Abfrage -“位，以适应您的语言。正确地更改WorkbookConnection、Connection和CommantText非常重要。