Find locations where connections are used Excel VBA

Question

I have a raft of Excel 2013 workbooks that I have to refine, each with multiple sheets and multiple data connections and I am looking for a quick way to list:

• connection name
• connection string
• location(s) where connections are used (sheet name or range would be useful)

I can see all this information in the connections dialogs but am having trouble tracking them down programmatically. I want to do this one file at a time so am not worried about running code across all the files, just something I can drop in a module as I start work on the file concerned. So far I have found this on this site:

Dim conn As WorkbookConnection
For Each conn In ActiveWorkbook.Connections
Debug.Print conn.Name
Next conn

but I can't find the location information to go alongside this. Any pointers would be very gratefully received.

Cheers

Kyle

Answer 1

Expected output :

(Connection's Name): Sheet'sName|1st Cell of Range
Connection's ODBC Command Text
(Connection's Name): Sheet'sName|1st Cell of 1st Range // Sheet'sName|1st Cell of 2nd Range

Here you go :

Private Sub List_Connections_Ranges()

Dim wC As WorkbookConnection
Dim rG As Range
Dim TpStr As String

For Each wC In ActiveWorkbook.Connections
    TpStr = "(" & wC.Name & ") found on : "
    For Each rG In wC.Ranges
        TpStr = TpStr & rG.Parent.Name & "|" & rG.Cells(1, 1).Address(0, 0) & " // "
    Next rG
    Debug.Print Left(TpStr, Len(TpStr) - 4)
    Debug.Print wC.ODBCConnection.CommandText
Next wC

End Sub