Create a QueryTable from an Excel Source in VBA
Elton is trying to create a QueryTable based on a named range in a closed Excel file. When the code tries to refresh the QueryTable object, he gets an SQL Syntax Error message. Although he's sure his SQL syntax is correct, it's almost certainly not. The syntax you get from the macro recorder works, but it's easy to get a stray quote or something in there when you start messing around with it.
I always rewrite my SQL statements from scratch. The macro recorder and MSQuery make sure the SQL statement is rock-solid by prefixing each field even if there's only one table and by including the path to the table, which already exists in the Connection string.
In this example, I have a workbook name TheDataBook.xls and a named range of TheData.
This code creates a new sheet and adds the QueryTable.
Dim sConn As String
Dim sSQL As String
Dim oQt As QueryTable
Dim sh As Worksheet
sConn = "ODBC;DSN=Excel Files;DBQ=Z:\TheDataBook.xls;"
sConn = sConn & "DefaultDir=Z:;DriverId=1046;"
sConn = sConn & "MaxBufferSize=2048;PageTimeout=5;"
sSQL = "SELECT Name, Number FROM TheDataBook.xls.TheData WHERE Number>=2 ORDER BY Name DESC"
Set sh = ThisWorkbook.Worksheets.Add
Set oQt = sh.QueryTables.Add(sConn, sh.Range("A1"), sSQL)
oQt.Refresh
End Sub
Notice the nice clean SQL statement. Due to persistant errors I can't show you what the macro recorder would have returned, but it would have looked something like
Yuck. Here's the results from the above code.
Hans Schraven:
Wouldn't it be nice to clean up the connectionstring as well ?
ActiveWorkbook.Sheets.Add
With ActiveSheet.QueryTables.Add("ODBC;DSN=Excel-bestanden;DBQ=E:\TheDatabook.xls;", Range("H1"))
.CommandText = "SELECT name, number FROM E:\TheDatabook.TheData TheData WHERE number = 1"
.Refresh False
End With
End Sub
Hans Schraven:
Some more reducement
ActiveWorkbook.Sheets.Add
With ActiveSheet.QueryTables.Add("ODBC;DSN=Excel-bestanden;DBQ=E:\TheDatabook.xls",[H1])
.CommandText = "SELECT name, number FROM TheData WHERE number =1"
.Refresh False
End With
End Sub
Rob van Gelder:
Something very interesting that I didn't notice before.
You can create a querytable in the same workbook, and it the data refreshed without first having to save it.
Triggering the refresh is a minor issue
25 June 2008, 3:44 amLists that grow based on selectable criteria (parameter query) is a very useful feature.
Jan:
I have had some really bad type issues with this particularly where there is no data in the first 8 or even 16 rows of a column. The Excel / Jet engine type casting gets it wholly wrong and screws up all sorts of queries. Rule of thumb be careful and don't use this on any data that might have gaps in it.
7 July 2008, 2:18 pmNo amount of tweaking was able to deal with this problem even trying casting of data using multipliers unions with hard coded numbers etc. All failed and sometimes I would even get a whole column from something that was not in the select statement. This was not down to some bad SQL this was a purely random impact based on large datasets.