如何处理"Microsoft Excel正在等待另一个应用程序完成OLE操作". [英] How to deal with "Microsoft Excel is waiting for another application to complete an OLE action"
问题描述
当使用excel自动化其他MS-Office应用程序时,我经常会得到提示,提示Microsoft Excel is waiting for another application to complete an OLE action.
When automating other MS-Office applications with excel, I frequently get ok-only prompts saying that Microsoft Excel is waiting for another application to complete an OLE action.
这仅在自动执行冗长的任务时发生.
This only happens when automating lengthy tasks.
我如何以适当的方式处理此问题?
最近的两个例子(我认为代码不太重要):
Two recent examples (I recon the code is less important):
-
使用
Access.Application
从Excel创建accdb数据库,并通过对大量数据运行相当复杂的SQL查询来填充该数据库.
creating an accdb-Database from Excel with an
Access.Application
and populating it by running rather complex SQL-queries on large amount of data.
Public Function createDB(pathDB As String, pathSQL As String) As String
Dim dbs As DAO.Database
Dim sql As String
Dim statement As Variant, file As Variant
Dim sErr As String, iErr As Integer
With New Access.Application
With .DBEngine.CreateDatabase(pathDB, dbLangGeneral)
For Each file In Split(pathSQL, ";")
sql = fetchSQL(file)
For Each statement In Split(sql, ";" & vbNewLine)
If Len(statement) < 5 Then GoTo skpStatement
Debug.Print statement
On Error Resume Next
.Execute statement, dbFailOnError
With Err
If .Number <> 0 Then
iErr = iErr + 1
sErr = sErr & vbCrLf & "Error " & .Number & " | " & Replace(.Description, vbCrLf, vbNullString)
.Clear
End If
End With
On Error GoTo 0
skpStatement:
Next statement
Next file
End With
.Quit acQuitSaveAll
End With
dTime = Now() - starttime
' Returnwert
If sErr = vbNullString Then sErr = "Keine Fehler"
createDB = "Zeit: " & Now & " | Dauer: " & Format(dTime, "hh:mm:ss") & " | Anzahl Fehler: " & iErr & vbCrLf & sErr
' Log
With ThisWorkbook
'...
.Saved = True
.Save
End With
End Function
使用现有的相当大的.docm
-模板以及返回收据的动态SQL查询,从Word.Application
中的Excel中合并邮件.
create mail merges from Excel in a Word.Application
, using existing and rather large .docm
-templates and dynamic SQL-queries that returns the receipents
Set rst = GetRecordset(ThisWorkbook.Sheets("Parameter").Range("A1:S100"))
With New Word.Application
.Visible = False
While Not rst.EOF
If rst!Verarbeiten And Not IsNull(rst!Verarbeiten) Then
Debug.Print rst!Sql
.Documents.Open rst!inpath & Application.PathSeparator & rst!infile
stroutfile = fCheckPath(rst!outpath, True) & Application.PathSeparator & rst!outfile
.Run "quelle_aendern", rst!DataSource, rst!Sql
.Run MacroName:="TemplateProject.AutoExec.SeriendruckInDokument"
Application.DisplayAlerts = False
.ActiveDocument.ExportAsFixedFormat _
OutputFileName:=stroutfile _
, ExportFormat:=wdExportFormatPDF _
, OpenAfterExport:=False _
, OptimizeFor:=wdExportOptimizeForPrint _
, Range:=wdExportAllDocument _
, From:=1, To:=1 _
, Item:=wdExportDocumentContent _
, IncludeDocProps:=False _
, KeepIRM:=True _
, CreateBookmarks:=wdExportCreateNoBookmarks _
, DocStructureTags:=False _
, BitmapMissingFonts:=True _
, UseISO19005_1:=False
Application.DisplayAlerts = True
For Each doc In .Documents
With doc
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End With
Next doc
End If
rst.MoveNext
Wend
.Quit
End With
注释:
- 以较小的规模运行时(例如,查询较少的记录或使用不太复杂的模板时),两个代码都可以平稳运行.
- 在两种情况下,当我重新出现所有提示时,代码最终都将以所需的结果结束. 因此,我想我没有遇到错误(也不会触发错误处理程序),而是发生了超时.
- When run on a smaller scale (for example, when querying less records or using less complex templates), both codes do run smoothly.
- In both cases, when I
OK
through all the reappearing prompts, the code will eventually finish with the desired results. Therefore, I guess I'm not encountering an error (also it doesn't trigger the error handlers), but rather something like a timeout.
正如其他资料来源所建议的那样,我确实将代码包装到了Application.DisplayAlerts = False
中.但是,这似乎是一个可怕的主意,因为实际上在某些情况下确实需要提醒我.
As suggested on other sources, I do wrap my code into Application.DisplayAlerts = False
. This, however, seems like a horrible idea, since there might actually be cases where I do need to be alerted.
推荐答案
我将在注释中添加@Tehscript链接到的代码.
I'll add the code that @Tehscript linked to in the comments.
您可以通过使用COM API删除VBA的消息筛选器来解决此问题. 这样可以防止COM告诉VBA在显示时显示消息框. 认为您正在呼叫的进程已被阻止.注意,如果过程 确实由于某种原因而被阻止,这将阻止您收到任何 通知. [来源]
You can solve this by using the COM API to remove VBA's message filter. This will prevent COM from telling VBA to displaying a message box when it thinks the process you're calling has blocked. Note that if the process really has blocked for some reason this will prevent you from receiving any notification of that. [source]
我认为这是我在2006年针对相同问题使用的代码(有效).
I think this is the code I used back in 2006 for the same problem (it worked).
Private Declare Function _
CoRegisterMessageFilter Lib "OLE32.DLL" _
(ByVal lFilterIn As Long, _
ByRef lPreviousFilter) As Long
Sub KillMessageFilter()
'''Original script Rob Bovey
'''https://groups.google.com/forum/?hl=en#!msg/microsoft.public.excel.programming/ct8NRT-o7rs/jawi42S8Ci0J
'''http://www.appspro.com/
Dim lMsgFilter As Long
''' Remove the message filter before calling Reflections.
CoRegisterMessageFilter 0&, lMsgFilter
''' Call your code here....
''' Restore the message filter after calling Reflections.
CoRegisterMessageFilter lMsgFilter, lMsgFilter
End Sub
这篇关于如何处理"Microsoft Excel正在等待另一个应用程序完成OLE操作".的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!