Changing Connection via VBA (Loop through queries)

[Shortmeister1]

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:

"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

 

[gazpage]

You probably want “ThisWorkbook.Connections”

 

[Shortmeister1]

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

 

[gazpage]

Thanks for not bothering to check before saying I was wrong. Power Query queries are Connections in VBA.

 

[gazpage]

https://stackoverflow.com/questions/36902975/auto-updating-power-query-connection-via-vba

Here is an example.

 

[Shortmeister1]

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:

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:

    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

 

[gazpage]

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.

 

[Shortmeister1]

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!

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