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.

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

Update: Here’s what the macro recorder returns in 2010

Yuck. Here’s the results from the above code.

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

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

    Sub idee()
      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
  2. Some more reducement

    Sub idee()
      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
  3. 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.

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

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



    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.

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

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

    Does anyoby has an idea to solve this issue ?

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

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

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

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

    but I get an error message (missing operator) !
    Thanks for your help

  12. HELLO


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




Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.