Changing Connection via VBA (Loop through queries)

martinshort

Board Regular
Joined
Feb 19, 2008
Messages
204
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
  6. 2003 or older
Platform
  1. Windows
Hi

I've never really played around with Power Query before. I've written a large number of queries and someone very helpful has gone and moved the database to a different server.

I would normally think that you would declare an object variable (let's call it qry) and then run a loop something like:

Code:
"For Each qry In Activeworkbook.queries"

Well nothing is ever that simple. It doesn't work and the macro recorder doesn't give anything useful up easily.

So...how do I loop through all the qry objects, identifying each one with the express purpose of changing the server name from one SQL database to a different one.

Thanks
Martin
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi Gazpage

Thanks for responding, but I don't think your answer is correct.

Power Query runs by queries not connections. (The connection is embedded in the query.)

It's actually the queries that I need to loop through not the connections.

Cheers
Martin
 
Upvote 0
Thanks for not bothering to check before saying I was wrong. Power Query queries are Connections in VBA.
 
Upvote 0
Ooo a bit tetchy gazpage. Not sure that polite forums are your forte. Thanks anyway!

I've actually managed to solve it at last and as I stated, it has nothing to do with connections.

First version:

Code:
Sub test()
Dim i As Integer
    For i = 1 To 29
        With ActiveWorkbook.Queries(i)
        Debug.Print i, .Name
        Debug.Print .Formula
        Debug.Print
        End With
    Next i
End Sub

Second version:

Code:
    Sub test1()
        Dim qry As Object
        For Each qry In ActiveWorkbook.Queries
            With qry
                Debug.Print qry.Name
                Debug.Print qry.Formula
                Debug.Print "-----------------------"
                Debug.Print
            End With
        Next qry
    End Sub

This exposes the Power Query properties including the all important query formula where the connection is stored.

Modifying the connection in the formula string was then a piece of cake as it's just a REPLACE function from old to new.

BR
Martin
 
Upvote 0
Well, you’ve got me there. Seems the .Queries collection, properties etc were added in Excel 2016 so I am 3 years (or 1 in the case of my office) out of date.

I apologise for my behaviour.
 
Upvote 0
Thanks gazpage - that's really appreciated.

Yup 2016 and everything changed. I am struggling to get my head around the new Power Query features. It's very powerful, but not the same as the previous methodology. What is interesting is that you were right in that it sets up what could best be described as a dummy connection per query with no useful info contained within...whatever a "mashup" is!

Code:
Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Claim1;Extended Properties=""
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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
Back
Top