vba Microsoft Excel 正在等待另一个应用程序完成 OLE 操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22379815/
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
Microsoft Excel is waiting for another application to complete an OLE action
提问by Vinod Chelladurai
I am running a macro through a vb script that imports more than 1000 records from database and copies them into an excel sheet and then send an email saying that the report is ready. When i import less records(100 or 200) it is running fine. But when I import the entire records(more than 1000), I get a window message "Microsoft Excel is waiting for another application to complete an OLE action " even though the program is running fine .
我正在通过一个 vb 脚本运行一个宏，该脚本从数据库中导入 1000 多条记录并将它们复制到 Excel 表中，然后发送一封电子邮件，说报告已准备就绪。当我导入较少的记录（100 或 200）时，它运行良好。但是当我导入整个记录（超过 1000 条）时，即使程序运行良好，我也会收到一条窗口消息“Microsoft Excel 正在等待另一个应用程序完成 OLE 操作”。
Is there any way to hide this message. Also, If i hide this message, will the program continues to run? Below is my code:
有什么办法可以隐藏这个消息。另外，如果我隐藏此消息，程序会继续运行吗？下面是我的代码：
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rst = New ADODB.Recordset
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
cn.Open ("User ID=flt" & _
";Password=flts1hp" & _
";Data Source=SIH_PROD" & _
";Provider=MSDAORA.1" & _
";PLSQLRSet=0")
Set oxcel = New Excel.Application
Set wbk = oxcel.Workbooks.Add()
With oxcel
.ActiveSheet.Name = "Report"
strFileName = "C:\Users\extract.xlsx"
'headings
For i = 0 To rs.Fields.Count - 1
.Cells(row, col) = rs.Fields(i).Name
.Rows(row).RowHeight = 45
.Cells(row, col).WrapText = True
col = col + 1
Next
.Range("A2").Select
.Selection.CopyFromRecordset rs
With wbk
.Application.DisplayAlerts = False
On Error GoTo Error_Message
.SaveAs (strFileName), AccessMode:=xlExclusive, _
ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
.Close
End With
.Quit
End With
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Done"
.Body = "Done"
.Send
End With
Set OutApp = Nothing
Set OutMail = Nothing
End Sub
回答by MrPandav
by reading your Question(withouth getting into your code)
通过阅读您的问题（无需进入您的代码）
Yes - Alert message can be Diabled using
是 - 可以使用禁用警报消息
`Application.DisplayAlerts = False`
but that won't solve the problem ,
但这并不能解决问题，
your programme will still not execute.Since there will be some deadlocks in function calls, (I have had this problem once )
你的程序仍然不会执行。因为函数调用中会有一些死锁，（我曾经遇到过这个问题）
Try debuggin with breakpoint and narrow it down to the erroneous function call
尝试使用断点调试并将其缩小到错误的函数调用
Hope this will help
希望这会有所帮助
回答by newstockie
You can use this code before the long running tasks
Application.IgnoreRemoteRequests = True
您可以在长时间运行的任务之前使用此代码
Application.IgnoreRemoteRequests = True
Can put it back to original after the task ends using Application.IgnoreRemoteRequests = False
任务结束后可以恢复原状使用 Application.IgnoreRemoteRequests = False
回答by Arnaud
I had this issue for an other strange reason : in an Excel script I was writting to an other excel workbook. And sometimes, this message appeared. What a mess for debugging such situation because everything is blocked until you kill the target excel file (the one where I was supose to write inside). At the end, I found the problem : a bug of Office 2013 (because this pb doesn't exist in Office 2010) ? I was trying to put a text (but not so big, in reality, with some vbLf inside...) in a standard cell, with a standard width. By changing the size of the column to 100 (at the end, something different from the original standard size), before writing inside the pb disappered! Ex. before writing in the cells :
由于另一个奇怪的原因，我遇到了这个问题：在 Excel 脚本中，我正在写入另一个 Excel 工作簿。有时，会出现此消息。调试这种情况真是一团糟，因为在您杀死目标 excel 文件（我想在里面写的那个文件）之前，一切都被阻止了。最后发现了问题：Office 2013的一个bug（因为这个pb在Office 2010中不存在）？我试图在标准单元格中放置一个文本（但实际上不是那么大，里面有一些 vbLf ......），具有标准宽度。通过将列的大小更改为 100（最后，与原始标准大小不同的内容），在 pb 内部写入之前消失了！前任。在写入单元格之前：
DocExcel.Sheets(1).Select
DocExcel.Sheets(1).Cells.ColumnWidth = 100
DocExcel.Sheets(1).Cells(1, 1).Select
Strange but it works...
奇怪，但它有效......
