Create a QueryTable from an Excel Source in VBA

Posted on June 24, 2008February 25, 2015 by Dick Kusleika

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.

Sub MakeExcelQT()
   
    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)
   
    oQt.Refresh
   
End Sub

Sub MakeExcelQT()

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)

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

.CommandText = Array("SELECT TheData.Name, TheData.Number FROM ‘Z:TheDataBook’.xls.TheData TheData WHERE (TheData.Number >=2) ORDER BY TheData.Name DESC;")

1	.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" _
)

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

13 thoughts on “Create a QueryTable from an Excel Source in VBA”

Hans Schraven says:

June 25, 2008 at 2:46 am

Wouldn’t it be nice to clean up the connectionstring as well ?

Sub idee()
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 says:

June 25, 2008 at 3:14 am

Some more reducement

Sub idee()
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 says:

June 25, 2008 at 3:44 am

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
Lists that grow based on selectable criteria (parameter query) is a very useful feature.
Jan says:

July 7, 2008 at 2:18 pm

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.
No 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.
Dale says:

December 4, 2010 at 6:21 am

It’s a little complicated, so I’ll give the readers digest version.
I’m building a spreadsheet to compare current and prior year and graph it by week, month and quarter using index and match. I can insert saved queries at different locations in target, but I have to edit 26 queries (13 weeks each for prior and current year) for each branch in the region and it’s quite time consuming. Is there a way to run code in vba to execute all these at once, then I can just use replace to change branch numbers and run in a new spread sheet for each location?
Here is a sample query for week 1 in 2010, the week numbers are all that change (Wk 1 to Wk 2 and Week1 to Week2, etc) for each query.

XLODBC

1

DSN=Excel Files;DBQ=R:2010P1Wk 1Supplemental2066_Week1ToDateProductionSales.xls;DefaultDir=R:2010P1Wk 1Supplemental;DriverId=790;MaxBufferSize=2048;PageTimeout=5;

SELECT ‘Sheet1$’.’Daily Production/Sales by Category and PricePoint Unified’, ‘Sheet1$’.F2, ‘Sheet1$’.F9 FROM ‘R:2010P1Wk 1Supplemental2066_Week1ToDateProductionSales’.’Sheet1$’ ‘Sheet1$’ WHERE (‘Sheet1$’.F2 Is Null) OR (‘Sheet1$’.F2 Like ‘Total’) OR (‘Sheet1$’.F2 Like ‘Branch Total’)

Daily Production/Sales by Category and PricePoint Unified F2 F9

Secondly, as 2011p1 doesn’t exist yet, is there a way to have the query return blank when the query fails due to “file does not exist’ without freezing?

Lastly, I believe the branch systems are still using 2000 or 2003. I.T. is updating all the corp and field computers, but unfortunately all us grunts at the branches where all the real work gets done are at the bottom of the list.
doomsday says:

March 3, 2011 at 7:48 pm

yes good point with code blocks using QueryTables and CommandText is the way to use native Excel , without using addins..
If you try
Dim rst as ADODB.Recordset
you will get a user-defined type not defined error. Thus, where portability to other Excel users is necessary, this method of using querytables seems to be ideal.

However, the real-time status of the query update is shown in the globe in the taskbar.
Pingback: Can I create a pivot table from two separate worksheets? - Page 3

I’d like to adapt this code which could be very helpfull but I get an application error (#1004).

Sub MakeExcelQT()
    
    Dim sConn As String
    Dim sSQL As String
    Dim oQt As QueryTable
    Dim sh As Worksheet
    
    sConn = "DSN=Excel Files;DBQ=C:\Users\pberard.PMICS\Google Drive\DSI\Sources\TheDataBook.xlsx;"
    sConn = sConn & "DefaultDir=C:\Users\pberard.PMICS\Google Drive\DSI\Sources;DriverId=1046;"
    sConn = sConn & "MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT Name, Number FROM TheDataBook.xlsx.TheData WHERE Number >=0 ORDER BY Name DESC"
    
    Set sh = ThisWorkbook.Worksheets.Add
    'msgConn = MsgBox(sConn)
    'Sheets("Base").Range("D1") = sConn
    'msgSQL = MsgBox(sSQL)
    'Sheets("Base").Range("D2") = sSQL
    Set oQt = sh.QueryTables.Add(Connection:=sConn, Destination:=sh.Range("A1"), Sql:=sSQL)
    
    oQt.Refresh
    
End Sub

Sub MakeExcelQT()

Dim sConn As String

Dim sSQL As String

Dim oQt As QueryTable

Dim sh As Worksheet

sConn = "DSN=Excel Files;DBQ=C:\Users\pberard.PMICS\Google Drive\DSI\Sources\TheDataBook.xlsx;"

sConn = sConn & "DefaultDir=C:\Users\pberard.PMICS\Google Drive\DSI\Sources;DriverId=1046;"

sConn = sConn & "MaxBufferSize=2048;PageTimeout=5;"

sSQL = "SELECT Name, Number FROM TheDataBook.xlsx.TheData WHERE Number >=0 ORDER BY Name DESC"

Set sh = ThisWorkbook.Worksheets.Add

'msgConn = MsgBox(sConn)

'Sheets("Base").Range("D1") = sConn

'msgSQL = MsgBox(sSQL)

'Sheets("Base").Range("D2") = sSQL

Set oQt = sh.QueryTables.Add(Connection:=sConn, Destination:=sh.Range("A1"), Sql:=sSQL)

oQt.Refresh

End Sub

Does anyoby has an idea to solve this issue ?
Thanks

Dick Kusleika says:

February 25, 2015 at 10:09 am

Philippe: The code got all mucked up. I’ve edited the post to correct the code and to implement Hans’ excellent advice to clean up the connection string.
Philippe says:

February 25, 2015 at 3:31 pm

Thank you Dick for your help.
The Query now works but how is it that it doesn’t work when the source is a table and why the result of the query is not automatically presented as a table as it is when you do manually the connection ?
Dick Kusleika says:

February 26, 2015 at 7:35 am

I’m not sure, Philippe. It probably has something to do with the ODBC driver for Excel files and what that driver recognizes.
Philippe says:

March 2, 2015 at 3:27 am

Does anybody knows why data from the XL source, even so numeric, could be retreieved as text values and how to solve this issue ?
I tried to used this syntax :

Visual Basic

cast(BaseICML.`MTD_Rn-1` as Numeric) as 'MTD_Rn-1'

1

cast(BaseICML.`MTD_Rn-1` as Numeric) as 'MTD_Rn-1'

but I get an error message (missing operator) !
Thanks for your help
R. BALDAIA says:

April 15, 2015 at 4:45 pm

HELLO

IT’S POSSIBLE TO USE THE SAME CODE WITH

DBQ=http://www. ……\TheDataBook.xlsx; ??

I GET A ERROR MESSAGE “INVALID PROCEDURE CALL”

USING URL HOW CAN I SELECT A RANGE ?

MANY THANKS FOR YOUR HELP