Importing data from SQL database in Excel 2003 VS Excel 2010 (VBA)

Question

So, my company finally upgraded to MS Office 2010. Up until now I've been working in 2003. I am a SQL programmer, and I constantly create reports in Excel that pull data from our database. Most of the time, I will create macros that accept parameters that the users will type into specific cells, alter the query, and then refresh it according to the parameters.

Here is a really simple example:

1. In Excel 2003 I would open a new workbook.
2. Click on "Data" then "Import External Data" then "New Database Query".
3. It then prompts you to choose a data source, so I would select the database I wanted to query from (which had been set up with an ODBC connection already).

4. I then cancel out of the Query Wizard windows and then when I'm in the Microsoft Query Editor, I just enter in my query.

   • For simplicity, I'll be selecting * from a table called Agents which is just a list of the agents that work for the company and their EmployeeIds.

   • select * from Agents

5. Then I "x" out of the query editor, and a box pops up called "Import Data" where it asks where you want to put the data: in an existing worksheet? a new worksheet? etc. I just have it return the data in the existing worksheet starting in Cell A2

So then I write this simple macro in the Visual Basic Editor in a module for the workbook:

Sub Refresh()

Dim oQuery as QueryTable
Dim oAgent as String

set oQuery = Sheet1.QueryTables(1)

oAgent = Sheet1.Range("A1")

oQuery.CommandText = "select * from Agents where Agent = '"+oAgent+"'"
oQuery.Refresh

End Sub

I create a button that runs this macro and stick it in B1. So the user opens the report, types a name into A1, hits the button and that Agent and their Id comes up in the table below. Really simple right? But I can't get this to work in Excel 2010.

Here are my steps and the error that follows:

1. I open Excel 2010, and go to the "Data" tab.
2. Under the section "Get External Data" I click on "From Other Sources" and select "From Mircrosoft Query" from the drop down.
3. Then the Choose Data Source box pops up and it is basically the exact same as steps 3,4 and 5 above.

Then I write the same macro, create the button and assign it to the marco, but when I click the button I get the following error:

Run-time error '9':
Subscript out of range

I hit debug and the debugger highlights this line

Set oQuery = Sheet1.QueryTables(1)

I tried making this line more specific like so:

Set oQuery = WorkBooks("Book 1").Sheets("Sheet 1").QueryTables(1)

But I just get the same error.

So basically what I need to know is how to do this type of thing in Excel 2010. But here is an interesting note: if I create this report in Excel 2003, save it as a .xls, then open it in 2010, it will work. I can even save a copy as a .xlsm then open that and it will work with this same macro. It's only when I create the report in 2010 that I can't get it to work. It seems for some reason that it just can't find the query to alter its command text and then refresh. Please help, I've been stuck on this for days!

Answer 1

In XL2007 and 2010 querytables are contained in a "ListObject" within the worksheet, so you just need to adjust your code to:

Set oQuery = WorkBooks("Book 1").Sheets("Sheet 1").ListObjects(1).QueryTables(1)

http://msdn.microsoft.com/en-us/library/ff841237.aspx

Tim