getting Connection information using VBA

[baitmaster]

I'm analysing a bulk set of files and want to log details of each one, including any Query and Connection information. I'm struggling to extract the Connection String

Within my code loops I have the following so far

Debug.Print "_______________________" & vbLf & "Queries and Connections:"

For Each q In wb.Queries
    Debug.Print q.Name, q.Formula
Next q


For Each conn In wb.Connections
    Debug.Print conn.Name, conn.Description
Next conn


If wb.Queries.Count + wb.Connections.Count = 0 Then Debug.Print "none"

I'm getting what I want for the Queries, but can't seem to extract the Connection data. I can see 14 connections in my test file, each has a name but no description. From Excel, [Data > Connection Properties > Definition > Connection String] shows some text, which is what I want to extract. In my first example this begins "SAGE_DR: <(1,1)Nominal Activity-SageDataset#SageApp.... I also note that this box is greyed out and and locked, I can't access it. My Locals window (in break mode) shows most properties as (Application-defined... error)

Is there another property that I can use to get this text?

 

[RoryA]

You need to check the Type of the connection. If it's xlConnectionTypeODBC or xlConnectionTypeOLEDB, then you need to access the ODBCConnection or OLEDBConnection properties to get to the interesting information, like CommandText or Connection.

 

[baitmaster]

Thanks Rory. Both of those properties were showing application error. The type for every connection is returning "5" but I'm not sure what this value is

 

[RoryA]

That's a web connection, but there is no corresponding WEBConnection object sadly. I'd suspect you need to loop through querytables.

 

[baitmaster]

Thanks, that may be enough info. I suspect any connections using this approach are obsolete in our system, which is the answer I'm looking to uncover