Excel is Waiting for another Application to complete OLE Action

  • Thread starter Thread starter SchoutenCC
  • Start date Start date
S

SchoutenCC

Hi All,

I've made a couple of macros which are giving me errors and I just
cannot figure out how to get rid of these. Hope anybody here can help
me.

I'm getting the following errors:
- Excel is Waiting for another Application to complete OLE Action
Have tried the suggestion from
http://groups.google.com.au/group/m...r+application+to+complete+an+OLE+action&hl=en
but it still displays the error message for me.
- Method delete of class picture cannot be found
Have tried the suggestion from
http://groups.google.com/group/micr...fc62e1942178?hl=nl&lnk=st&q=#95aafc62e1942178
but it still displays the error message for me.

So, basically I'm starting to think I'm implementing the solutions
incorrectly.
Here is part of my VBA: (Alt-F11 --> VBAProject (Macro-menu.xls) -->
Modules --> Module1
---
Option Explicit
Private Declare Function CoRegisterMessageFilter Lib
"OLE32.DLL" (ByVal lFilterIn As Long, ByRef lPreviousFilter) As Long

Sub CPO()
'Hide OLE blocked process message box & disable events
'On Error Resume Next
Dim lMsgFilter As Long
CoRegisterMessageFilter 0&, lMsgFilter
Application.EnableEvents = False

(...)

'Prepare objects
Dim xlApp As Object: Set xlApp = New Excel.Application
xlApp.Visible = True: xlApp.Interactive = True
xlApp.Application.Workbooks.Open sPathJet
'--> XLA add-in
Dim oDist: Set oDist = CreateObject("PdfDistiller.PdfDistiller")
'--> Using one CreateObject statement, found elsewhere this might
cause a problem

'Open CPO file and update
xlApp.Application.Workbooks.Open sPathCpo

(...)

xlApp.Application.Run Dir(sPathJet) & "!JetMenu", "Report"
'--> The error occurs usually while Jet is updating its report
(sometimes lasting over two hours), but with unpredictable intervals/
not at reproducible moments

(...)
oDist.FileToPdf sFilename & ".ps", sFilename & ".pdf", ""
'--> Using an often-suggested PDF-conversion statement

(...)

MyWorkbook.Close SaveChanges:=False
xlApp.Quit

'Restore OLE blocked process message box & enable events
CoRegisterMessageFilter lMsgFilter, lMsgFilter
Application.EnableEvents = True
End Sub
 
Anybody please?
Click to expand...

Em! It is a real pain. The only way I have found to get out of this is "Task
Manager" to kill Excel. As a result my code has frequent "Saves" built in.
 
Let's start here: Where are you using this code? Excel? If so, why are
you instantiating another Excel Application Object?

What is the purpose of the code?

--JP
 
Let's start here: Where are you using this code? Excel? If so, why are
you instantiating another Excel Application Object?

What is the purpose of the code?

--JP
Click to expand...

My purpose is to automate updating excel sheets; to be specific, i've
got about a dozen excel files that need to be opened and then need
changing a parameter before clicking on the update button of
JetReports, after which they need to be saved as PDF.
My approach was to create a macro in a single excel menu-type sheet
(that also defines the parameters) specific to each file so I can
launch whichever sheet needs to be updated at any time.
Why the new object? Well, I don't know - i guess I thought I had to.
I suppose my main question now is am I making any fundamental mistakes
in placing the above code in a module in the calling sheet and nothing
at all in the called sheet? Specifically of course when it comes to
the fixes proposed in the two links in my first post (http://
groups.google.com.au/group/microsoft.public.excel.programming/
browse_frm/thread/72df0d453fa8eebb?hl=en&lr=&rnum=1&prev=/groups%3Fq
%3DMicrosoft%2BExcel%2Bis%2Bwaiting%2Bfor%2Banother%2Bapplication%2Bto
%2Bcomplete%2Ban%2BOLE%2Baction%26hl%3Den) and (http://
groups.google.com/group/microsoft.public.excel/browse_thread/thread/
fc35e72e78c2edb4/95aafc62e1942178?hl=nl&lnk=st&q=#95aafc62e1942178),
am I handling those correctly?
 
The first thing I would do is stop instantiating Excel. Many of the
objects (Workbook, Range) implicitly refer to the Application Object,
so if you are in Excel you don't need an explicit Application Object
reference.

Also, have you tried stepping through the code (using F8) to see where
specifically it throws an error?

I think the OLE error is coming from your API call to the
CoRegisterMessageFilter function, any particular reason you are using
that?

--JP
 
The first thing I would do is stop instantiating Excel. Many of the
objects (Workbook, Range) implicitly refer to the Application Object,
so if you are in Excel you don't need an explicit Application Object
reference.

Also, have you tried stepping through the code (using F8) to see where
specifically it throws an error?

I think the OLE error is coming from your API call to the
CoRegisterMessageFilter function, any particular reason you are using
that?

--JP
Click to expand...

I'll try changing it to a single-instance version, will let you know.
There are two errors, one is some kind of ole conflict message (excel
is waiting for another application to complete) which is thrown at
seemingly random intervals (not consistent in number of times thrown,
nor in moment of execution). The CoRegisterMessageFilter function is
supposed to be a fix for that as detailed in the first link of the two
posted above. The second error is thrown when closing down the called
(worker) sheet. It says it cannot find a delete method for a picture,
though the error seems to have remained after having deleted the
company logo (?). It was suggested I delete all code that manipulates
the logo, though none of the child worker sheets contain any VBA
module, excluding that as an option.
 
Back
Top