Copying Worksheets with Powerquery using VBA - breaks Query

Question

I have a Worksheet called "TEMPLATE" with a PowerQuery "TEMPLATE_Query" in it which works. The Powerquery requests a CSV-file from a website.

Now, using VBA in a loop, I make N copies of this sheet, resulting in worksheets named "Template (X)" and Powerquerys named "TEMPLATE_Query (X)". X=1..N. In my VBA code, I modify the Powerquery Formulas to request a different CSV file. Until here, it works alright. The Powerquerys run and receive different CSV files in all those Worksheets.

Here's the problem:

When I change the name of the Worksheet using VBA during my loop where I create the copies, the Powerquerys fail afterwards. Saying like "Download failed, connection only". So apparently the Powerquery doesn't have a reference to the correct sheetname anymore. The same happens when I rename the Powerquery and leave the Worksheet Name the same.

My goal is, to rename the Worksheet AND the Powerquery both in my VBA loop.

But why does this break the Queries?

Czeskleba · Accepted Answer · 2020-01-25 11:59:19Z

1

I had the same problem a little while ago. If I remember correctly, it breaks because the query still wants to access something with a different name. I don't know why Excel does not change the reference when you rename a query. It does not even automatically changes it if you rename it manually with a right click. If you look at the query, right click it and then switch over to properties or what ever the middle tab is called, there you can see some details.

Long story short, this is how I fixed mine:

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

The second to last bunch, the 3 modifying the connection are the important ones. This is on a German Excel tho, so you may need to change the "Abfrage - " bit to fit your language. It's just important that you correctly change the WorkbookConnection, Connection and CommantText correctly.

answered Jan 25, 2020 at 11:59

Czeskleba

4643 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tim Over a year ago

Wow, so nice to contribute that as a first entry. Thanks very much. Ill try it later and let you know!

tim Over a year ago

One more question. I checked the output of the 3 properties ThisWorkbook.Sheets(3).ListObjects(1).QueryTable.X and I saw that the Query name is referenced there. So yes, changing this would make sense. But I'm still wondering where exactly in your code you change some property to refer to the new Worksheet name because as we concluded - renaming the sheet also breaks the Query.

Czeskleba Over a year ago

Uhm, If I remember correctly, renaming a sheet did not break my quiery. I was using 365 tho, so maybe it's a version thing? I just tried in Excel 2019 and renaming a sheet without renaming the query works just fine in the 2019 version as well.

Collectives™ on Stack Overflow

Copying Worksheets with Powerquery using VBA - breaks Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related