vba Excel 2010 通过宏执行 SQL 语句时冻结

Excel 2010 freezes when executing SQL statement through macro

提问by BillSchwartzky

I am having a problem with executing a SQL query through a macro vs by simply clicking "Refresh" in excel 2010.

我在通过宏执行 SQL 查询时遇到问题，而只需单击 excel 2010 中的“刷新”。

the set up:

设置：

1. I open a new excel 2010 worksheet.
2. go to the "Data" tab
3. Under the "Get External Data" section I click "From Other Sources" and from that drop down I click "From Microsoft Query"
4. I then choose the proper ODBC connection, cancel through the "Query Wizard" window and close the "Add Tables" window
5. Then in the "Microsoft Query" window I simply click the "SQL" button, enter my query and hit ok
6. It executes the query in the "Microsoft Query" window, then I close that window, select the cell I want it to populate in when prompted and hit ok.
7. The query is then populated in my spreadsheet.

1. 我打开一个新的 excel 2010 工作表。
2. 转到“数据”选项卡
3. 在“获取外部数据”部分下，我单击“来自其他来源”，然后从该下拉菜单中单击“来自 Microsoft Query”
4. 然后我选择正确的 ODBC 连接，通过“查询向导”窗口取消并关闭“添加表”窗口
5. 然后在“Microsoft Query”窗口中，我只需单击“SQL”按钮，输入我的查询并点击确定
6. 它在“Microsoft Query”窗口中执行查询，然后我关闭该窗口，选择我希望它在提示时填充的单元格并点击确定。
7. 然后将查询填充到我的电子表格中。

Here is where the issue comes up:

这是问题出现的地方：

If I right click on the table and select "Refresh" from the right-click pop up menu the data will refresh and the little spinning globe at the bottom of the screen which says "Running background query..." will appear and spin until the query has finished running and excel will not freeze and I can work in other tabs or other worksheets.

如果我右键单击表格并从右键单击弹出菜单中选择“刷新”，数据将刷新并且屏幕底部的小旋转地球仪显示“正在运行后台查询...”将出现并旋转直到查询已完成运行，excel 不会冻结，我可以在其他选项卡或其他工作表中工作。

However, if I execute that exact statement through a macro:

但是，如果我通过宏执行该确切语句：

Range("A6").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

The spinning globe does not appear, excel stops responding, sometimes goes white, and the mouse turns into the spinning "not responding" circle when hovered over excel. At this point I am unable to work in excel at all until the query finishes executing and excel starts responding again.

旋转地球不出现，excel停止响应，有时会变白，鼠标悬停在excel上时变成旋转的“无响应”圆圈。在这一点上，我根本无法在 excel 中工作，直到查询完成执行并且 excel 再次开始响应。

This is an issue because for most of my reports I use cells in excel to allow users to insert parameters into the SQL statements that I execute.

这是一个问题，因为对于我的大多数报告，我使用 Excel 中的单元格来允许用户将参数插入到我执行的 SQL 语句中。

Example:

例子：

Dim oQuery As QueryTable
Dim oDate As String

Set oQuery = ActiveWorkbook.Sheets("Sheet1").ListObjects(1).QueryTable

oDate = Range("B1")

oQuery.CommandText = "exec database.dbo.ExampleProcedure @SuppliedDate = '" + oDate + "'"

oQuery.Refresh False

This was not an issue in Excel 2003, but only came up when I switched to 2010 and had to change the query table vb code as shown in the example below

这在 Excel 2003 中不是问题，但只有在我切换到 2010 并且不得不更改查询表 vb 代码时才出现，如下例所示

in 2003:

2003年：

 Set oQuery = Sheet1.QueryTables(1)

in 2010:

在2010年：

Set oQuery = ActiveWorkbook.Sheets("Sheet1").ListObjects(1).QueryTable

Also it only happens on about 75% of the reports I write and is only a noticeable issue when the SQL statements takes more than a few seconds to refresh. This is becoming a pretty big issue, so I'd be extremely grateful to anyone who could help. Thanks so much.

此外，它仅发生在我编写的大约 75% 的报告中，并且仅当 SQL 语句需要几秒钟才能刷新时才会出现明显问题。这正在成为一个非常大的问题，因此我将非常感谢任何可以提供帮助的人。非常感谢。

回答by Ben

Here it is:

这里是：

BackgroundQuery:=False 

This means "Don't let anyone do anything until the query has finished".

这意味着“在查询完成之前不要让任何人做任何事情”。

Excel is doing what you asked. If you want people to be able to continue, you need to change Falseto True.

Excel 正在按照您的要求执行操作。如果您希望人们能够继续，则需要更改False为True.

If you need to perform further processing after the query, you have two options (apart from speeding up the queries which I assume you have already done).

如果您需要在查询后执行进一步处理，您有两个选择（除了加快我假设您已经完成的查询）。

Either use a DoEvents loop while you wait for the query to complete (check QueryTables.Item(1).Refreshingto see if it is still running),

在等待查询完成时使用 DoEvents 循环（检查QueryTables.Item(1).Refreshing它是否仍在运行），

Sub RefreshQueryAndWait(ByVal oQuery As QueryTable)
  '' Assume oQuery is the current query
  oQuery.Refresh BackgroundQuery:=False
  Do While oQuery.Refreshing
    DoEvents
  Loop
End Sub

Or:put the second half of the VB code (after the refresh) into a different Sub which you call when the query completes.

或者：将 VB 代码的后半部分（刷新后）放入查询完成时调用的不同 Sub 中。

See http://support.microsoft.com/kb/213187for how to do this.

有关如何执行此操作，请参阅http://support.microsoft.com/kb/213187。

回答by Andrew Muir

I always end my query code with:

我总是以以下方式结束我的查询代码：

ActiveSheet.ListObjects(1).Refresh
Application.CalculateUntilAsyncQueriesDone
ActiveSheet.ListObjects(1).Unlist

As it jogs along the query and ruturns the results

当它沿着查询慢跑并返回结果时