因此，我的公司最终升级到了 MS Office 2010。到目前为止，我一直在 2003 年工作。我是一名 SQL 程序员，我不断地在 Excel 中创建报告，从数据库中提取数据。大多数时候，我会创建接受用户在特定单元格中键入的参数的宏，更改查询，然后根据参数刷新它。
这是一个非常简单的示例：
- 在 Excel 2003 中我将打开一个新工作簿。
- 单击“数据”，然后单击“导入外部数据”，然后单击“新数据库查询”。
- 然后它会提示您选择数据源，因此我会选择要从中查询的数据库（已使用 ODBC 连接设置）。
-
然后，我取消查询向导窗口，然后当我在 Microsoft 查询编辑器中时，我只需输入我的查询。
-
然后我“x”出了查询编辑器，弹出一个名为“导入数据”，它询问您要将数据放在哪里：在现有工作表中？新的工作表？等等。我只是让它返回从单元格 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 查询”。
- 然后弹出“选择数据源”框，与上面的步骤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 年创建报告时，我无法让它工作。似乎由于某种原因，它只是找不到查询来更改其命令文本然后刷新。请帮忙，我已经被这个问题困扰好几天了！
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:
- In Excel 2003 I would open a new workbook.
- Click on "Data" then "Import External Data" then "New Database Query".
- 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).
-
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.
-
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:
- I open Excel 2010, and go to the "Data" tab.
- Under the section "Get External Data" I click on "From Other Sources" and select "From Mircrosoft Query" from the drop down.
- 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!
发布评论
评论（1）
在 XL2007 和 2010 中，查询表包含在工作表内的“ListObject”中，因此您只需将代码调整为：
http://msdn.microsoft.com/en-us/library/ff841237.aspx
蒂姆
In XL2007 and 2010 querytables are contained in a "ListObject" within the worksheet, so you just need to adjust your code to:
http://msdn.microsoft.com/en-us/library/ff841237.aspx
Tim