Excel VBA：当宏尝试打开另一个工作簿时，等待另一个应用程序完成 OLE 操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17090129/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: Waiting for another application to complete an OLE action when macro tries to open another workbook
提问by Sam WB
A little background to the title: I've written a macro that gets called on workbook open. It opens a [shared] workbook on a shared directory and pulls in some information to the workbook the user is using.
标题的一点背景：我写了一个宏，它在工作簿打开时被调用。它在共享目录上打开一个 [共享] 工作簿，并将一些信息拉入用户正在使用的工作簿中。
Any user working with this sheet already has the shared directory mapped to their computer (and the macro finds the correct drive letter).
使用此工作表的任何用户都已将共享目录映射到他们的计算机（并且宏会找到正确的驱动器号）。
I've tested this worksheet multiple times with users in my office. I've also tested it and had two people open the workbooks simultaneously to confirm that the macros for both users are able to pull data from the shared workbook concurrently.
我已经在我办公室的用户中多次测试过这个工作表。我还对其进行了测试，并让两个人同时打开工作簿，以确认两个用户的宏都能够同时从共享工作簿中提取数据。
So far, I've had no issues.
到目前为止，我没有遇到任何问题。
This sheet then got rolled out to multiple other users in my company. All in all, about 40 people are expected to use this sheet (not necessarily at the same time.. just in total).
然后这张表被推出给我公司的多个其他用户。总而言之，预计大约有 40 人会使用这张表（不一定同时……只是总共）。
One of the users is located in Poland (I'm located in London).
其中一位用户位于波兰（我位于伦敦）。
When he opens the workbook, he gets a 'Microsoft Excel is waiting for another application to complete an OLE action' notification. The notification comes with an 'OK' button. Pressing this button seems to have no effect and the workbook effectively hangs on this notification.
当他打开工作簿时，他收到“Microsoft Excel 正在等待另一个应用程序完成 OLE 操作”的通知。该通知带有一个“确定”按钮。按此按钮似乎没有效果，工作簿有效地挂在此通知上。
I'm having a lot of trouble resolving this problem as I have not been able to replicate it. Does anyone have an idea why this would come up? Code below:
我在解决这个问题时遇到了很多麻烦，因为我无法复制它。有谁知道为什么会出现这种情况？代码如下：
Sub PreliminaryDataImport()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As Variant
Dim usename As String
usename = Environ("USERNAME")
Dim xlo As New Excel.Application
Dim xlw As New Excel.Workbook, wkbk As New Excel.Workbook
Dim xlz As String, regions As String
Dim LRow As Long, LCell As Long, LRow2 As Long
Dim RegionList As String
RegionList = ""
xlz = Sheet1.Range("o1").Value & "\Region Planning\TestDB.xlsx"
Set xlw = xlo.Workbooks.Open(xlz)
If Not Sheet11.Range("S1").Value = xlw.Worksheets("validation") _
.Range("N1").Value Then
MsgBox "YOU ARE USING AN OUT OF DATE VERSION" & vbLf & _
"Please check your inbox or contact xxxx for the current version."
xlw.Close False
Set xlo = Nothing
Set xlw = Nothing
Call Module7.ProtectSheets
End
End If
x = CheckValidation(usename, xlw)
'~~ Check to see if User has access to view/modify.
'~~ If they have access, return regions
On Error Resume Next
For i = LBound(x) To UBound(x)
regions = regions + " --- " & x(i)
RegionList = RegionList + x(i) & ", "
Sheet1.Cells(i + 2, 33).Value = x(i)
Next
If Err.Number <> 0 Then
MsgBox "You do not have access to view or modify any regions."
xlw.Close False
Set xlo = Nothing
Set xlw = Nothing
End
Else
MsgBox "You have access to view and modify the following regions:" & vbLf _
& vbLf & regions & "---"
I believe the issue occurs somewhere within this section of the code as the msgbox on the last line doesn't show up prior to the notification. I haven't been able to run in debug from his machine as he's located remotely and that would be a large effort (should only be done if absolutely necessary).
我相信问题发生在代码的这一部分中，因为最后一行的 msgbox 在通知之前没有显示。由于他位于远程，我无法从他的机器进行调试运行，这将是一项巨大的工作（只有在绝对必要时才应该这样做）。
Anyone have ideas on why this one user is getting this error? I'm particularly confused because it's only him having the issue.
任何人都知道为什么这个用户会收到此错误？我特别困惑，因为只有他有问题。
采纳答案by Joe
One thing that looks a bit suspicious is that you're creating a new instance of Excel
看起来有点可疑的一件事是您正在创建 Excel 的新实例
Dim xlo As New Excel.Application
Normally this is done so that a hidden instance of Excel can be used to open a workbook that you don't want to show to the user, but I don't see any code to hide this second instance, i.e.:
通常这样做是为了让隐藏的 Excel 实例可用于打开您不想向用户显示的工作簿，但我没有看到任何隐藏第二个实例的代码，即：
xlo.Visible = False
Since you open and close the shared workbook quickly, and you have ScreenUpdating = False in your main Excel instance, you may be able to do this in your main Excel instance without the overhead of creating a new Excel instance.
由于您快速打开和关闭共享工作簿，并且您的主 Excel 实例中有 ScreenUpdating = False，因此您可以在主 Excel 实例中执行此操作，而无需创建新 Excel 实例的开销。
Also you aren't calling xlo.Quit to close the second Excel instance, so it may hang around in the background...
此外，您没有调用 xlo.Quit 来关闭第二个 Excel 实例，因此它可能会在后台徘徊...
An alternative approach would be to use OleDb to read from the shared workbook, in which case you don't need to open it at all.
另一种方法是使用 OleDb 从共享工作簿中读取数据，在这种情况下，您根本不需要打开它。
