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

Question

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

这是一个非常简单的例子:

在 Excel 2003 中，我会打开一个新工作簿。

单击“数据”，然后单击“导入外部数据”，然后单击“新建数据库查询”。

然后它会提示您选择一个数据源，因此我将选择我想从中查询的数据库(已经设置了 ODBC 连接)。

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

为简单起见，我将选择 *来自名为 Agents 的表这只是为公司工作的代理商及其EmployeeIds 的列表.

select * from Agents

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

因此，我在 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

我创建了一个运行此宏的按钮并将其粘贴在 B1 中.因此用户打开报告，在 A1 中输入名称，点击按钮，该代理及其 ID 出现在下表中。真的很简单吧？但我不能让它在 Excel 2010 中工作。

这是我的步骤和以下错误:

我打开 Excel 2010，然后转到“数据”选项卡。

在“获取外部数据”部分下，我单击“来自其他来源”并从下拉列表中选择“来自 Mircrosoft 查询”。

然后弹出Choose Data Source框，和上面的步骤3，4，5基本一样。

然后我编写相同的宏，创建按钮并将其分配给宏，但是当我单击按钮时，出现以下错误:

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

我点击调试，调试器突出显示这一行

Set oQuery = Sheet1.QueryTables(1)

我尝试使这条线更具体，如下所示:

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

但我只是得到同样的错误。

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

Answer 1

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

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

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

蒂姆