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