Prev: what besides displayalerts statement
Next: refresh linked query
From: JLatham on 30 Apr 2010 17:14
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...(a)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 -
>
> .
>
 | 
Pages: 1
Prev: what besides displayalerts statement
Next: refresh linked query