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=C:\Users\dkusleika\Dropbox\Excel\TheDataBook.xlsx;"
sSQL = "SELECT Name, Number FROM TheData WHERE Number >=2 ORDER BY Name DESC"
Set sh = ThisWorkbook.Worksheets.Add
Set oQt = sh.QueryTables.Add(sConn, sh.Range("A1"), sSQL)
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
.CommandText = Array("SELECT TheData.Name, TheData.Number FROM ‘Z:TheDataBook’.xls.TheData TheData WHERE (TheData.Number >=2) ORDER BY TheData.Name DESC;")
Update: Here’s what the macro recorder returns in 2010
.CommandText = Array( _
"SELECT TheData.Name, TheData.Number" & Chr(13) & "" & Chr(10) & "FROM `C:\Users\dkusleika\Dropbox\Excel\TheDataBook.xlsx`.TheData TheData" & Chr(13) & "" & Chr(10) & "WHERE (TheData.Number>=2)" & Chr(13) & "" & Chr(10) & "ORDER BY TheData.Name" _
Yuck. Here’s the results from the above code.