Upon further thought, this might be of more use to you, gives more info:

Sub ReadConnections()
Dim anySheet As Worksheet
Dim qTable As QueryTable

For Each anySheet In ThisWorkbook.Worksheets
If anySheet.QueryTables.Count 0 Then
For Each qTable In anySheet.QueryTables
With qTable
Debug.Print .Name
Debug.Print .Connection
Debug.Print .CommandText
Debug.Print ' separating line
End With
Next
End If
Next
End Sub


"MChrist" wrote:

I have a number of Excel files that have data connections to various sources,
and I want to be able to read the data connection strings and command text
(SQL) using VBA.

In Access this would be easy to do, creating a database object, and looking
at the QueryDefs. Is there something similar in Excel?

If someone could point me to an on-line reference or provide a code like the
one below, that would be great.

Thank you.

Mark

Private Sub Test()

Dim strMsg As String

Dim db As DAO.Database
Dim qds As DAO.QueryDefs
Dim qd As DAO.QueryDef

Set db = currentdb()
Set qds = db.QueryDefs

For Each qd In qds

Debug.Print qd.Connect & vbTab & qd.Sql

Next

End Sub