View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
AB[_2_] AB[_2_] is offline
external usenet poster
 
Posts: 236
Default Accessing the Data Connection Strings and Command Text (SQL) inVB

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