getting Connection information using VBA

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,042
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
Code:
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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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
 
Upvote 0
That's a web connection, but there is no corresponding WEBConnection object sadly. I'd suspect you need to loop through querytables.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,215,560
Messages
6,125,527
Members
449,236
Latest member
Afua

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