在 Excel VBA 中取消外部查询

Cancel External Query in Excel VBA

提问by Steven

I have created an Excel Spreadsheet which helps with data analysis from an Oracle database.

我创建了一个 Excel 电子表格，它有助于从 Oracle 数据库进行数据分析。

The user enters then clicks the "Refresh Query" button which generates a query for Oracle to execute. The query takes a minute or so to complete. Although the VBA code does not hang on ".Refresh", all Excel windows remain frozen until the query completes.

用户输入然后单击“Refresh Query”按钮，该按钮生成供 Oracle 执行的查询。查询需要一分钟左右才能完成。尽管 VBA 代码不会挂在“.Refresh”上，但所有 Excel 窗口都保持冻结状态，直到查询完成。

Sub refreshQuery_click()
    Dim queryStr as String

    ' Validate parameters and generate query   
    ' ** Code not included **
    '

    ' Refresh Query
    With ActiveWorkbook.Connections("Connection").OLEDBConnection
        .CommandText = queryStr
        .Refresh
    End With
End Sub

Is there a way for the user to manually cancel the query (calling .CancelRefresh) while the Excel user-interface is frozen?

当 Excel 用户界面冻结时，用户是否可以手动取消查询（调用 .CancelRefresh）？

EDITI don't know if the following is worth noting or regular behavior. While the query is executing, all open Excel windows (including the VBA Editor) become "Not Responding" in Task Manager. Neither pressing Esc nor Ctrl+Break will cancel the script. Also, calling DoEvents (either before or after .Refresh) does not change this behavior.

编辑我不知道以下是否值得注意或常规行为。执行查询时，所有打开的 Excel 窗口（包括 VBA 编辑器）在任务管理器中都变为“无响应”。按 Esc 或 Ctrl+Break 都不会取消脚本。此外，调用 DoEvents（在 .Refresh 之前或之后）不会改变这种行为。

回答by Ben McCormack

Here's a method that I know will work. However, there are some complications.

这是一种我知道会奏效的方法。但是，有一些并发症。

Here's how it's done:

这是它的完成方式：

• Put the spreadsheet with the data in a separate workbook. This worksheet should execute the refresh query when it's opened and then close once the data is updated.
• Create a batch file to call the "Data" Excel file.
• Within a different workbook, create a procedure (macro) for the user to call. This procedure will call the batch file, which subsequently calls the Excel file. Since you are calling a batch file and not Excel directly, the Excel procedure will continue because the command shell is released so quickly and opens the other Excel file in a different thread. This allows you to continue working within the main Excel file.

• 将包含数据的电子表格放在单独的工作簿中。此工作表应在打开时执行刷新查询，然后在数据更新后关闭。
• 创建批处理文件以调用“数据”Excel 文件。
• 在不同的工作簿中，创建一个供用户调用的过程（宏）。此过程将调用批处理文件，该批处理文件随后调用 Excel 文件。由于您正在调用批处理文件而不是直接调用 Excel，因此 Excel 过程将继续进行，因为命令外壳被释放得如此之快，并在不同的线程中打开另一个 Excel 文件。这允许您继续在主 Excel 文件中工作。

Here are some complications:

以下是一些并发症：

• I included a method to alert the user that the data has been udpated. There are timing issues where it's possible to try to check if the data has been update when the workbook is not accessible, which forces the user to try to update values. I included a method called my time which pauses the execution of the code so it only checks every so many seconds.
• The updated worksheet will pop up in a new window, so the user will need to click on their original worksheet and keep working. You could learn to hide this if you're comfortable with Windows scripting (I haven't learned that yet).

• 我包含了一种方法来提醒用户数据已被更新。存在计时问题，当工作簿不可访问时，可以尝试检查数据是否已更新，这会迫使用户尝试更新值。我包含了一个名为 my time 的方法，它会暂停代码的执行，因此它只每隔几秒检查一次。
• 更新后的工作表将在新窗口中弹出，因此用户需要单击其原始工作表并继续工作。如果您熟悉 Windows 脚本，您可以学会隐藏它（我还没有学会）。

Here are some files and code. Be sure to read the comments in the code for why some things are there.

这是一些文件和代码。请务必阅读代码中的注释，了解为什么会出现某些内容。

FILE: C:\DataUpdate.xls

文件：C:\DataUpdate.xls

We'll make a workbook called "DataUpdate.xls" and put it in our C:\ folder. In cell A1 of Sheet1, we'll add our QueryTable which grabs external data.

我们将制作一个名为“DataUpdate.xls”的工作簿并将其放在我们的 C:\ 文件夹中。在 Sheet1 的单元格 A1 中，我们将添加获取外部数据的 QueryTable。

Option Explicit

Sub UpdateTable()

Dim ws As Worksheet
Dim qt As QueryTable

Set ws = Worksheets("Sheet1")
Set qt = ws.Range("A1").QueryTable

qt.Refresh BackgroundQuery:=False

End Sub

Sub OnWorkbookOpen()
Dim wb As Workbook
Set wb = ActiveWorkbook

'I put this If statement in so I can change the file's
'name and then edit the file without code
'running.  You may find a better way to do this.
If ActiveWorkbook.Name = "DataUpdate.xls" Then


    UpdateTable
    'I update a cell in a different sheet once the update is completed.
    'I'll check this cell from the "user workbook" to see when the data's been updated.
    Sheets("Sheet2").Range("A1").Value = "Update Table Completed    " & Now()
    wb.Save

    Application.Quit

End If


End Sub

In the ThisWorkbookobject in Excel, there's a procedure called Workbook_Open(). It should look like the following so it executes the update code when it is opened.

在ThisWorkbookExcel的对象中，有一个名为 Workbook_Open() 的过程。它应该如下所示，以便在打开时执行更新代码。

Private Sub Workbook_Open()
OnWorkbookOpen

End Sub

NOTE: I found a bug when this file closed if 1) you accessed the file from the command line or shell and 2) you have the Office Live Add-in installed. If you have the Office Live Add-in installed, it will throw an exception on exit.

注意：如果 1) 您从命令行或 shell 访问该文件，并且 2) 您安装了 Office Live 加载项，我在关闭此文件时发现了一个错误。如果您安装了 Office Live 加载项，它会在退出时引发异常。

FILE: C:\RunExcel.bat

文件：C:\RunExcel.bat

Next, we're going to create a batch file that will open the Excel file we just made. The reason that call the Excel file from within the batch file and not directly from the other Excel file using Shell is because Shell will not continue until the other application closes (at least when using Excel.exe "c:\File.xls"). The batch file, however, runs its code and then immediately closes, thus allowing the original code that called it to continue. This is what will let your uses continue working in Excel.

接下来，我们将创建一个批处理文件，该文件将打开我们刚刚制作的 Excel 文件。从批处理文件中调用 Excel 文件而不是直接从使用 Shell 的其他 Excel 文件调用 Excel 文件的原因是，在其他应用程序关闭之前，Shell 不会继续（至少在使用 时Excel.exe "c:\File.xls"）。然而，批处理文件运行其代码然后立即关闭，从而允许调用它的原始代码继续。这将使您的用户继续在 Excel 中工作。

All this file needs is:

所有这个文件需要的是：

cd "C:\Program Files\Microsoft Office\Office10\" 
Excel.exe "C:\DataUpdate.xls"

If you're handy with Windows Scripting, you do fancy things like open the window in a hidden mode or pass a parameter of the file name or Excel location. I kept it simple with a batch file.

如果您熟练使用 Windows 脚本，您可以做一些奇特的事情，例如以隐藏模式打开窗口或传递文件名或 Excel 位置的参数。我用一个批处理文件保持简单。

FILE: C:\UserWorkbook.xls

文件：C:\UserWorkbook.xls

This is the file that the user will open to "do their work in." They'll call the code to update the other workbook from within this workbook and they'll still be able to work in this workbook while this one is updating.

这是用户将打开以“在其中进行工作”的文件。他们将调用代码来更新此工作簿中的另一个工作簿，并且在更新此工作簿时，他们仍然能够在此工作簿中工作。

You need a cell in this workbook where you'll check the "Update Table Completed" cell from the DataUpdate workbook. I chose cell G1 in Sheet1 for my example.

您需要此工作簿中有一个单元格，您将在其中检查 DataUpdate 工作簿中的“更新表已完成”单元格。我选择了 Sheet1 中的单元格 G1 作为示例。

Add the following code to a VBA module in this workbook:

将以下代码添加到此工作簿中的 VBA 模块：

Option Explicit

Sub UpdateOtherWorkbook()
Dim strFilePath As String
Dim intOpenMode As Integer
Dim strCallPath As String
Dim strCellValue As String
Dim strCellFormula As String
Dim ws As Worksheet
Dim rng As Range

Set ws = Worksheets("Sheet1")
Set rng = ws.Range("G1")
strCellFormula = "='C:\[DataUpdate.xls]Sheet2'!A1"
'This makes sure the formula has the most recent "Updated" value
'from the data file.
rng.Formula = strCellFormula

strFilePath = "C:\RunExcel.bat"
intOpenMode = vbHide

'This will call the batch file that calls the Excel file.
'Since the batch file executes it's code and then closes,
'the Excel file will be able to keep running.
Shell strFilePath, intOpenMode

'This method, defined below, will alert the user with a
'message box once the update is complete. We know that
'the update is complete because the "Updated" value will
'have changed in the Data workbook.
AlertWhenChanged

End Sub

'

'

Sub AlertWhenChanged()

Dim strCellValue As String
Dim strUpdatedCellValue As String
Dim strCellFormula As String
Dim ws As Worksheet
Dim rng As Range

Set ws = Worksheets("Sheet1")
Set rng = ws.Range("G1")
strCellFormula = "='C:\[DataUpdate.xls]Sheet2'!A1"

strCellValue = rng.Value
strUpdatedCellValue = strCellValue

'This will check every 4 seconds to see if the Update value of the
'Data workbook has been changed.  MyWait is included to make sure
'we don't try to access the Data file while it is inaccessible.
'During this entire process, the user is still able to work.
Do While strCellValue = strUpdatedCellValue
    MyWait 2
        rng.Formula = strCellFormula
    MyWait 2
        strUpdatedCellValue = rng.Value
    DoEvents
Loop

MsgBox "Data Has Been Updated!"

End Sub

'

'

Sub MyWait(lngSeconds As Long)
Dim dtmNewTime As Date

dtmNewTime = DateAdd("s", lngSeconds, Now)

Do While Now < dtmNewTime
    DoEvents
Loop


End Sub

As you can see, I constantly updated the formula in the "Listening Cell" to see when the other cell was updated. Once the data workbook has been updated, I'm not sure how you'd force an update in code without rewriting all the cells. Closing the workbook and reopening it should refresh the values, but I'm not sure of the best way to do it in code.

如您所见，我不断更新“侦听单元格”中的公式以查看其他单元格的更新时间。更新数据工作簿后，我不确定您如何在不重写所有单元格的情况下强制更新代码。关闭工作簿并重新打开它应该会刷新值，但我不确定在代码中执行此操作的最佳方法。

This whole process works because you're using a batch file to call Excel into a different thread from the original file. This allows you to work in the original file and still be alerted when the other file has been updated.

整个过程之所以有效，是因为您正在使用批处理文件将 Excel 调用到与原始文件不同的线程中。这使您可以在原始文件中工作，并且在其他文件更新时仍会收到警报。

Good luck!

祝你好运！

回答by Ben McCormack

EDIT: Rather than include a more complete answer in this same answer, I've created a separate answer dedicated entirely to that solution. Check it out below (or above if it gets voted up)

编辑：我没有在同一个答案中包含更完整的答案，而是创建了一个完全专用于该解决方案的单独答案。在下面查看（如果投票通过，则在上方查看）

Your users can break the VBA function by pressing Ctrl+Break on the keyboard. However, I've found that this can cause your functions to randomly break until each time any function is run. It goes away when the computer is restarted.

您的用户可以通过按键盘上的 Ctrl+Break 来中断 VBA 功能。但是，我发现这会导致您的函数随机中断，直到每次运行任何函数为止。计算机重新启动后它就会消失。

If you open this file in a new instance of Excel (meaning, go to Start > Programs and open Excel from there), I think that the only workbook that will be frozen will be the one executing the code. Other intances of Excel shouldn't be affected.

如果您在 Excel 的新实例中打开此文件（即，转到“开始”>“程序”并从那里打开 Excel），我认为唯一将被冻结的工作簿将是执行代码的工作簿。Excel 的其他实例不应该受到影响。

Lastly, you might research the DoEvents functions, which yields execution back to the Operating System so that it can process other events. I'm not sure if it would work in your case, but you could look into it. That way you can do other things while the process is being completed (It's kind of dangerous because the user can then change the state of your application while the process is working).

最后，您可能会研究 DoEvents 函数，该函数将执行返回给操作系统，以便它可以处理其他事件。我不确定它是否适用于您的情况，但您可以调查一下。这样你就可以在进程完成时做其他事情（这有点危险，因为用户可以在进程工作时更改应用程序的状态）。

---

---

I believe I know a way that actually will work, but it's complicated and I don't have the code in front of me. It involves creating a separate instance of the Excel application in codeand attaching a handler to the execution of that instance. You include the DoEvents part of the code in a loop that releases once the application closes. The other instantiated Excel application has the sole purpose of opening a file to execute a script and then close itself. I've done something like this before so I know that it works. I'll see if I can find the code tomorrow and add it.

我相信我知道一种实际可行的方法，但它很复杂，而且我面前没有代码。它涉及在代码中创建 Excel 应用程序的单独实例，并将处理程序附加到该实例的执行中。您将代码的 DoEvents 部分包含在应用程序关闭后释放的循环中。另一个实例化的 Excel 应用程序的唯一目的是打开文件以执行脚本，然后自行关闭。我以前做过这样的事情，所以我知道它有效。我明天看看能不能找到代码并添加它。

回答by Damir Sudarevic

Well, you could consider the old-fashion way -- split the query into smaller batches and use Do Eventsin between batches.

好吧，您可以考虑使用老式方法——将查询拆分为较小的批次并Do Events在批次之间使用。

回答by Peter Smith

You could try XLLoop. This lets you write excel functions (UDfs) on an external server. It includes server implementations in many languages (eg. Java, Ruby, Python, PHP).

你可以试试XLoop。这使您可以在外部服务器上编写 excel 函数 (UDfs)。它包括多种语言（例如 Java、Ruby、Python、PHP）的服务器实现。

You could then connect to your oracle database (and potentially add a caching layer) and serve up the data to your spreadsheet that way.

然后您可以连接到您的 oracle 数据库（并可能添加一个缓存层）并以这种方式将数据提供给您的电子表格。

The XLL also has a feature to popup a "busy" GUI that lets the user cancel the function call (which disconnects from the server).

XLL 还具有弹出“繁忙”GUI 的功能，允许用户取消函数调用（与服务器断开连接）。

BTW, I work on the project so let me know if you have any questions.

顺便说一句，我在做这个项目，所以如果你有任何问题，请告诉我。