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

score 7 · Accepted Answer · answered Feb 08 '17 at 13:04

7

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

answered Feb 08 '17 at 13:04

R3uK

14,417
7
43
77

Thanks R3uK , that's exactly what I needed. I couldn't see how to use the range object. – Kyle Gorf Feb 08 '17 at 13:11
@KyleGorf : you are welcome! Just to see how this works, place a breakpoint in the loop, and right click on `wC`, and **add a spy**! Now you can browse it to see how the data inside is structured! ;) – R3uK Feb 08 '17 at 13:14

Vityata · Answer 2 · 2021-10-26T06:58:12.247

The code looks like ok to me. Can you try it like this to see whether you have connections at all:

Public Sub TestMePlease()

    Dim conn As WorkbookConnection

    If ActiveWorkbook.Connections.Count > 0 Then
        For Each conn In ActiveWorkbook.Connections
            Debug.Print conn.Name
        Next conn
    Else
        Debug.Print "No connection found"
    End If

End Sub

Edit: More information about connection is in the library. To see it, press ctrl + space like this:

Concerning the sheet name, it is here:

    Debug.Print conn.Ranges.Item(1).Parent.Name

Concerning the connection string, Microsoft would not give you a possibility to access such information from VBA. If it is available it should be a giant security problem.

I think that you missed the point here : *location(s) where connections are used (sheet name or range would be useful)* The idea is to list the places in the workbook in which the connections are used — R3uK, Feb 08 '17 at 12:48

Find locations where connections are used Excel VBA

2 Answers2

Linked