vba Excel 正在等待另一个应用程序完成 OLE 操作

Excel is waiting for another application to complete an OLE action

提问by SliderSteve

Before you go for the obvious: Application.DisplayAlerts = Falsehas not solved my problem.

在你去做显而易见的事情之前：Application.DisplayAlerts = False还没有解决我的问题。

I have written a VBA procedure (initiated in Excel 2010) which loops around an array containing different Excel files. The loop opens the file, refreshes the data, saves and closes the file for each item in the array. I have written an error catch sub routine so I log which excel files have failed to open/refresh/save etc so a user can manually check them.

我编写了一个 VBA 过程（在 Excel 2010 中启动），它围绕包含不同 Excel 文件的数组循环。循环打开文件，刷新数据，保存并关闭数组中每个项目的文件。我编写了一个错误捕获子例程，所以我记录了哪些 excel 文件未能打开/刷新/保存等，以便用户可以手动检查它们。

Some files are quite large and involve a large amount of data moving across the network; sometimes I get a dialog box with: Excel is waiting for another application to complete an OLE action.

有些文件非常大，涉及大量数据在网络上移动；有时我会收到一个对话框：Excel is waiting for another application to complete an OLE action。

I could use Application.DisplayAlerts = Falseto disable the message but this would presumably disable all alerts so I couldn't catch the errors?

我可以Application.DisplayAlerts = False用来禁用该消息，但这可能会禁用所有警报，因此我无法捕获错误？

Further I have tested using the line and it doesn't stop the dialog box pop-up. If I press enter it carries on but will likely pop-up again a few minutes later.

此外，我已经使用该行进行了测试，它不会停止弹出对话框。如果我按 Enter 它会继续，但可能会在几分钟后再次弹出。

Is there a way to stop is message specifically without stopping other alerts?

有没有办法在不停止其他警报的情况下专门停止消息？

NB. My process has a control instance of Excel which runs the VBA and opens the workbooks to be refreshed in a separate instance.

注意。我的流程有一个 Excel 控制实例，它运行 VBA 并打开要在单独实例中刷新的工作簿。

Thanks for your help

谢谢你的帮助

An extract of my code is below which contains the refresh elements

我的代码摘录如下，其中包含刷新元素

Sub Refresh_BoardPivots_Standard()
'    On Error GoTo Errorhandler

Dim i
Dim errorText As String
Dim x
Dim objXL As Excel.Application
Set objXL = CreateObject("Excel.Application")

GetPivotsToRefresh ' populate array from SQL
For Each i In StandardBoardPiv
DoEvents
'If File_Exists(i) Then
    If isFileOpen(i) = True Then
    errorText = i
    Failed(failedIndex) = errorText
    failedIndex = failedIndex + 1
    Else
    objXL.Visible = True 'False
     objXL.Workbooks.Open FileName:=i
        If objXL.ActiveWorkbook.ReadOnly = False Then
        BackgroundQuery = False
        Application.DisplayAlerts = False
        objXL.ActiveWorkbook.RefreshAll
        objXL.Application.CalculateFull
        objXL.Application.DisplayAlerts = False
        objXL.ActiveWorkbook.Save
        objXL.Application.DisplayAlerts = True
        objXL.Quit
        Else
        errorText = i
        Failed(failedIndex) = errorText
        failedIndex = failedIndex + 1
        objXL.Application.DisplayAlerts = False
        objXL.Quit
        Application.DisplayAlerts = True
        End If
    End If
'        Else
'        errorText = i
'        Failed(failedIndex) = errorText
'        failedIndex = failedIndex + 1
'    End If
DoEvents
If Ref = False Then
Exit For
End If

Next i

Exit Sub

'Errorhandler:
'
'errorText = i
'Failed(failedIndex) = errorText
'failedIndex = failedIndex + 1

'Resume Next
End Sub

采纳答案by TheSilkCode

"Waiting for another application to complete an OLE action" isn't an alert message you can just turn off and forget, sometimes the macro will be able to continue on after, but in my experience if you are getting that error its only a matter of time until the problem crashes/freezes your whole macro so it should definitely be troubleshot and corrected.

“等待另一个应用程序完成 OLE 操作”不是您可以关闭并忘记的警告消息，有时宏将能够继续运行，但根据我的经验，如果您遇到该错误，这只是一个问题直到问题崩溃/冻结整个宏的时间，因此绝对应该排除故障并更正。

I only get that error when I am using additional Microsoft Office Applications (other than the Excel that is running the code) as objects and one of them has an error- the Excel running the code doesn't know that an error occurred in one of the other applications so it waits and waits and waits and eventually you get the "Waiting for another application to complete an OLE action" message...

当我使用其他 Microsoft Office 应用程序（运行代码的 Excel 除外）作为对象并且其中一个出现错误时，我只会收到该错误 - 运行代码的 Excel 不知道其中之一发生了错误其他应用程序，因此它等待，等待，等待，最终您会收到“正在等待另一个应用程序完成 OLE 操作”消息...

So to troubleshoot this sort of problem you got to look for the places you use other MSO apps... In your example, you have an additional instance of Excel and you are pulling data from Access, so its most likely one of those two that is causing the problems...

因此，要解决此类问题，您必须查找使用其他 MSO 应用程序的地方...在您的示例中，您有一个额外的 Excel 实例并且您正在从 Access 中提取数据，因此它很可能是这两个应用程序之一导致问题...

Below is how I would re-write this code, being more careful with where the code interacts with the other MSO apps, explicitly controlling what is happening in them.. The only piece I couldn't really do much is GetPivotsToRefreshbecause I cant see what exactly youre doing here, but in my code I just assumed it returned an array with a list of the excel files you want to update. See code below:

下面是我将如何重新编写此代码，在代码与其他 MSO 应用程序交互的位置更加小心，明确控制其中发生的事情..唯一我不能真正做的事情是GetPivotsToRefresh因为我看不到什么正是你在这里做的，但在我的代码中，我只是假设它返回了一个数组，其中包含要更新的 excel 文件的列表。见下面的代码：

Sub Refresh_BoardPivots_Standard()
Dim pivotWB As Workbook
Dim fileList() As Variant
Dim fileCounter As Long

Application.DisplayAlerts = False
fileList = GetPivotsToRefresh 'populate array from SQL
For fileCounter = 1 To UBound(fileList, 1)
    Set pivotWB = Workbooks.Open(fileList(fileCounter, 1), False, False)
    If pivotWB.ReadOnly = False Then
        Call refreshPivotTables(pivotWB)
        pivotWB.Close (True)
    Else
    '... Error handler ...
        pivotWB.Close (False)
    End If
Next
End Sub
Public Sub refreshPivotTables(targetWB As Workbook)
Dim wsCounter As Long
Dim ptCounter As Long
For wsCounter = 1 To targetWB.Sheets.Count
    With targetWB.Sheets(wsCounter)
        If .PivotTables.Count > 0 Then
            For ptCounter = 1 To .PivotTables.Count
                .PivotTables(ptCounter).RefreshDataSourceValues
            Next
            .Calculate
        End If
    End With
Next
End Sub

So I created my own 'refreshPivotTables' but you could have embedded that into the master sub, I just thought the loops and loop counters might get a little messy at that point...

所以我创建了我自己的“refreshPivotTables”，但你可以将它嵌入到主子中，我只是认为循环和循环计数器可能会在那时变得有点混乱......

Hope this helps, TheSilkCode

希望这会有所帮助，TheSilkCode