在 Excel 2003 VS Excel 2010 (VBA) 中从 SQL 数据库导入数据

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

提问by BillSchwartzky

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.

所以，我的公司终于升级到了 MS Office 2010。直到现在我一直在 2003 年工作。我是一名 SQL 程序员，我经常在 Excel 中创建报告，从我们的数据库中提取数据。大多数时候，我会创建宏来接受用户将在特定单元格中键入的参数，更改查询，然后根据参数刷新它。

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

1. 在 Excel 2003 中，我将打开一个新工作簿。
2. 单击“数据”，然后单击“导入外部数据”，然后单击“新建数据库查询”。
3. 然后它会提示您选择一个数据源，因此我会选择我想要查询的数据库（已经设置了 ODBC 连接）。

4. 然后我取消查询向导窗口，然后当我在 Microsoft 查询编辑器中时，我只需输入我的查询。

   • 为简单起见，我将从*名为的表中进行选择，该表Agents只是为公司工作的代理及其EmployeeIds.

   • select * from Agents

5. 然后我从查询编辑器中“x”出来，弹出一个名为“导入数据”的框，它询问您要将数据放在哪里：在现有工作表中？一个新的工作表？等。我只是让它返回现有工作表中的数据，从单元格开始A2

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

然后我在 Visual Basic 编辑器的工作簿模块中编写了这个简单的宏：

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.

我创建了一个运行此宏的按钮并将其粘贴到B1. 因此，用户打开报告，在 中键入名称A1，点击按钮，该代理及其 ID 出现在下表中。真的很简单吧？但是我无法在 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.

1. 我打开 Excel 2010，然后转到“数据”选项卡。
2. 在“获取外部数据”部分下，我单击“来自其他来源”并从下拉列表中选择“来自 Mircrosoft 查询”。
3. 然后弹出选择数据源框，基本和上面的步骤3、4、5完全一样。

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!

所以基本上我需要知道的是如何在 Excel 2010 中执行此类操作。但这里有一个有趣的说明：如果我在 Excel 2003 中创建此报表，将其另存为 .xls，然后在 2010 中打开它，它将工作。我什至可以将副本另存为 .xlsm，然后打开它，它将使用相同的宏。只有当我在 2010 年创建报告时，我才能让它发挥作用。似乎出于某种原因，它只是找不到更改其命令文本然后刷新的查询。请帮忙，我已经被困在这个问题上好几天了！

采纳答案by Tim

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

在 XL2007 和 2010 中，查询表包含在工作表中的“ListObject”中，因此您只需将代码调整为：

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

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

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

Tim

蒂姆