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

In excel vba you can code pretty much the same way as in access vba.
If you want to have intellisense and use early bounding - add
refference to dao library (in vbe Tools>Refferences and then check the
most recent DAO lib taht you have or whichever want to use) and off
you go.

google for:
set DAO.Database object from excel
and will get loads of feeds.

On Apr 30, 2:45*pm, MChrist <MChr...@discussions.microsoft.com> 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

See if this helps you out any

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
MsgBox qTable.Connection
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
>
>

Perhaps JLatham understood your post correctly (as oppose to me).
I thought that you wanted to read sql strings in Access from Excel.
If you want to read SQL strings and Connections in excel sheets (i.e.,
querytables on excel tabs) then JLatham code is the way to go.
As per his code:
qTable.Connection - will give you the connection string
qTable.Commandtext - will give you the SQL string.

On Apr 30, 3:16*pm, JLatham <JLat...@discussions.microsoft.com> wrote:
> See if this helps you out any
>
> 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
> * * * * MsgBox qTable.Connection
> * * * 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- Hide quoted text -
>
> - Show quoted text -

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
>
>

And maybe you got it right! Until we hear back from the OP, probably won't
know. Often 2 or 3 people read the same request and each has a slightly
different interpretation of the needs/desires.
In either case, hopefully he gets the help he's looking for.

"AB" wrote:

> Perhaps JLatham understood your post correctly (as oppose to me).
> I thought that you wanted to read sql strings in Access from Excel.
> If you want to read SQL strings and Connections in excel sheets (i.e.,
> querytables on excel tabs) then JLatham code is the way to go.
> As per his code:
> qTable.Connection - will give you the connection string
> qTable.Commandtext - will give you the SQL string.
>
> On Apr 30, 3:16 pm, JLatham <JLat...@discussions.microsoft.com> wrote:
> > See if this helps you out any
> >
> > 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
> > MsgBox qTable.Connection
> > 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- Hide quoted text -
> >
> > - Show quoted text -
>
> .
>