VBA to change source of multiple power query connections

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
289
Office Version
  1. 365
  2. 2010
Hello I've a got sheet with 10 queries that load to Tables

I want to make a copy of the sheet with tables populated using the same query but with a different data source, but preserving the table formats, etc.

So, I copy the sheet, which automatically creates new queries, like:

Query 1 Name Group 1 (2)
Query 2 Name Group 1 (2)
Query 3 Name Group 1 (2)
...
Query 10 Name Group 1 (2)

Using a manual process, I then open the the query editor, click the source step at the top and change the source line for each query from something like
Power Query:
= #"Data source for Group 1"
to
Power Query:
= #"Another data from another connection"

Then I close the editor and click "Keep", and then once the refreshes have completed copied table on the new sheet now has the correct data in it. It all works, but to do the whole sheet takes more than 40 minutes, by the time the queries have loaded into the editor and, saved and then refreshed and the data loaded to the tables.

It is possible to use VBA instead to change the source for a list of specific connections?

If so, it would be handy also to:
- first generate a list of connection names, so that these can be used to specify the correct connections to update - OR if it's easier just constrain the source changes to connections on the active sheet?
- rename the updated copied queries/connections,

e.g.
Query 1 Name Group 2
Query 2 Name Group 2
Query 3 Name Group 2
etc.

instead of the automatically created
Query 1 Name Group 1 (2)
Query 2 Name Group 1 (2)
Query 3 Name Group 1 (2)
etc.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top