Retrieving the SQL query of the external data range

[Pavils Jurjans]

Hello,

I have this Excel file with a spreadsheet with external data range. It was
given to me to work though the associated SQL query remotely. Unfortunately,
when I click "Edit Query", I get message that ODBC data source could not be
found. Currently I have no copy of the data, but I'd be willing to study
(and perhaps update) the SQL query.

Is there any way to retrieve the SQL query that is saved in my Excel file,
via other means, for example, using VBA?

Thanks,

Pavils

 

[Guest]

Yes, the QueryTable.CommandText property should contain the SQL query used;
and the other properties like the Connection property might help you find out
what the data source is (should be) and why it isn't connecting - perhaps
either the database was moved, or there is a new ODBC driver.

To get to the Querytable to see these things:
Dim MyQueryTable as QueryTable
Set MyQueryTable = Sheets("SheetName").Querytables("QueryTableName")

To find the querytable's name, right click on the query results range and
look at the properties.

 

[Guest]

Give this a shot to get the information you want. Not Tried...

'/=================================================/
Sub Query_Info()
Dim iRow As Long
Dim qryTable As QueryTable
Dim wks As Worksheet

On Error Resume Next

Worksheets.Add.Move _
After:=Worksheets(Worksheets.Count)
Range("A1").Select

ActiveWorkbook.ActiveSheet.Name = "Query Info"

ActiveWorkbook.ActiveSheet.Range("A1").Value = _
"Query Name"
ActiveWorkbook.ActiveSheet.Range("B1").Value = _
"Connection"
ActiveWorkbook.ActiveSheet.Range("C1").Value = _
"SQL"

For Each wks In Worksheets
For Each qryTable In wks.QueryTables
iRow = iRow + 1
ActiveCell.Offset(iRow, 0).Value = _
qryTable.Name
ActiveCell.Offset(iRow, 1).Value = _
qryTable.Connection
ActiveCell.Offset(iRow, 2).Value = _
qryTable.Sql
Next qryTable
Next wks


End Sub
'/=================================================/

HTH,
Gary Brown