Suppressing the Excel dialog for waiting for an OLE action

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi all...

I have written an application which uses the PDF Creator for creating PDF's.
The application is loading different Excel files, fills them with some data and then
puts the output in the PDF Creator's print queue. Then the app merges all single
PDF's to one PDF. Depending on the users' choice, there are sometimes many PDF's
to create, for example more than 150 files.

On some machines, merging the PDF's can take a long time and then Excel displays
a message "Excel is waiting for another application to complete an OLE action".

My question: is it possible to hide this message? (as the message is regularly displayed
until the PDF Creator has finished his job) I tried to use Application.DisplayAlerts, but
that did not work.

Thanks in advance for any help and suggestions :)

Regards :)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Mourad,

I've seen that message before, and I'm not sure if we can actually suppress it or not. I'm not entirely sure that Excel is the owner of the message, (maybe the OS is?) although the wording would lead you to believe so.

I wonder if, to avoid situations like that, it might be better to combine the files more frequently? Say after every 10 documents merge them, then continue priting more? I don't think it would help the speed (in fact it would probably make it slower) but with less files to work with at a time maybe it would improve the stability?
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi Ken...

many thanks for your suggestions. In fact, I also do not know if Excel really owns this message; I supposed it. That's a thing, I can try to find out. Thanks also for the idea to split the merging in more parts. I will give a try, however I must then merge that part PDF documents in one document (my customer needs this). Unfortunately the PDF Creator is in my opinion not well documented, perhaps you have a code for this?

If I let the PDF Creator not merge the documents and I only fill the print queue and then merge the docs manually by using the Print Monitor, then the OLE message does not appear. So I had also the idea to send an API message to the print monitor, I am working on it but not ready yet. If this will work, I think the PDF Creator will be independent from Excel and work in background and make the users' life a little bit easier.

Best regards :)
 

Jan Karel Pieterse

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
17
Reaction score
0
Points
0
The message can be suppressed using a windows API call.
I already posted a reply with a link to a page that shows the solution, but the post is pending approval from an admin.
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi Jan Karel...

great, thanks. So I will wait until the post is approved :)

Best regards :)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I already posted a reply with a link to a page that shows the solution, but the post is pending approval from an admin.

Grrr.... I'll approve it right now. I need to look into this, as it's not supposed to moderate posts for users with more than 5 posts...
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Okay, approved.

Now... question... does suppressing the message leave the routine running until it completes? It's not indicating that the "program is not responding" or anything?
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi all...

many thanks, Jan Karel, a very smart solution. I will try it out and report here about my experience. However this can take some days before I am ready.

Best regards :)
 

Jan Karel Pieterse

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
17
Reaction score
0
Points
0
Hi Ken,

I wouldn't know what effect it has, but I suspect it might leave your code waiting endlessly.
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi all...

I have now tested the API functions for suppressing the OLE message and this worked,
as the message did not appear again in my tests. However, as predicted by Ken and
Jan Karel, the PDF Creator process remains "attached" to Excel and then Excel waits
and waits and waits. So this is not really practicable.

So I tried another solution, consisting in getting the handle from the PDF Creator print
monitor and send to this window a keystroke Ctrl+A = combine the docs. The PDF
Creator works then for himself; for my customer the solution is suffisent and he is
happy. This is an excerpt of the test code...
Code:
  Option Explicit
  
'
' Constants
'
' ..............................................................................
  
' Private...
  
  Private Const mlchInputKeyboard                         As Long = 1
  Private Const mlchEventKeyDown                          As Long = &H0
  Private Const mlchEventKeyUp                            As Long = &H2
  
'
' API
'
' ..............................................................................
  
  Private Declare Sub apiCopyMemory Lib "kernel32" _
          Alias "RtlMoveMemory" (pDst As Any, _
                                 pSrc As Any, _
                                 ByVal ByteLen As Long)
   
  Private Declare Function apiEnumChildWindows Lib "user32" _
          Alias "EnumChildWindows" (ByVal hWndParent As Long, _
                                    ByVal lpEnumFunc As Long, _
                                    ByVal lParam As Long) As Long
  
  Private Declare Function apiFindWindowExtended Lib "user32" _
          Alias "FindWindowExA" (ByVal hWnd1 As Long, _
                                 ByVal hWnd2 As Long, _
                                 ByVal lpsz1 As String, _
                                 ByVal lpsz2 As String) As Long
  
  Private Declare Function apiGetDesktopWindow Lib "user32" _
          Alias "GetDesktopWindow" () As Long
  
  Private Declare Function apiGetWindowText Lib "user32" _
          Alias "GetWindowTextA" (ByVal hwnd As Long, _
                                  ByVal lpString As String, _
                                  ByVal cch As Long) As Long
  
  Private Declare Function apiSendInput Lib "user32.dll" _
          Alias "SendInput" (ByVal nInputs As Long, _
                                   pInputs As apiInput, _
                             ByVal cbSize As Long) As Long
  
  Private Declare Function apiSetForegroundWindow Lib "user32" _
          Alias "SetForegroundWindow" (ByVal hwnd As Long) As Long
          
'
' Variables
'
' ..............................................................................
  
' Handle...
  
  Private mlvhCreatorHandle                               As Long
  
' Title...
  
  Private mlvhCreatorTitle                                As String
  
'
' Types
'
' ..............................................................................
  
  Private Type apiInput
    
    dwType          As Long
    xi(0 To 23)     As Byte
    
  End Type
  
  Private Type apiInputKeyboard
    
    wVk             As Integer
    wScan           As Integer
    dwFlags         As Long
    time            As Long
    dwExtraInfo     As Long
    
  End Type
  
'
' Private
'
' ..............................................................................
  
' Childs...
  
  Private Function mlfhChild(ByVal Handle As Long, _
                             ByVal Params As Long) As Long
    
    Dim b As Boolean
    Dim t As String
    Dim n As Long
    Dim r As Long
    
'   Errors...
    
    On Error Resume Next
    
'   Title..
    
    t = String(255, " ")
    r = apiGetWindowText(Handle, t, 255)
    
'   Check...
    
    If Len(t) > 0 Then
      
'     Reset...
      
      b = CBool(InStr(1, UCase(t), UCase("PDF Creator")) > 0) Or _
          CBool(InStr(1, UCase(t), UCase("PDFCreator")) > 0)
      
'     Check...
      
      If b And mlvhCreatorHandle < 1 Then
        
        mlvhCreatorHandle = Handle
        mlvhCreatorTitle = t
        
      End If
      
    End If
    
'   Return...
    
    mlfhChild = True
    
  End Function
  
  Private Function mlfhChildEnumerator(Handle As Long) As Long
    
    Dim r As Long
    
'   Errors...
    
    On Error Resume Next
    
'   Reset...
    
    mlvhCreatorHandle = 0
    mlvhCreatorTitle = ""
    
'   Enumerate...
    
    apiEnumChildWindows Handle, AddressOf mlfhChild, 0
    
'   Return...
    
    mlfhChildEnumerator = mlvhCreatorHandle
    
  End Function
  
'
' Public
'
' ..............................................................................
  
' Enumerate...
  
  Public Function mlfpCreatorEnumerate() As Long
    
    Dim r         As Long
    Dim t(0 To 3) As apiInput
    Dim k(0 To 3) As apiInputKeyboard
    
'   Errors...
    
    On Error Resume Next
    
'   Return...
    
    r = mlfhChildEnumerator(apiGetDesktopWindow)
    
'   Check....
    
    If r > 0 Then
      
'     Inputs...
      
      k(0).wVk = vbKeyControl
      k(1).wVk = vbKeyA
      k(2).wVk = vbKeyA
      k(3).wVk = vbKeyControl
      
      k(0).dwFlags = mlchEventKeyDown
      k(1).dwFlags = mlchEventKeyDown
      k(2).dwFlags = mlchEventKeyUp
      k(3).dwFlags = mlchEventKeyUp
      
      t(0).dwType = mlchInputKeyboard
      t(1).dwType = mlchInputKeyboard
      t(2).dwType = mlchInputKeyboard
      t(3).dwType = mlchInputKeyboard
      
'     Copy...
      
      apiCopyMemory t(0).xi(0), k(0), Len(k(0))
      apiCopyMemory t(1).xi(0), k(1), Len(k(1))
      apiCopyMemory t(2).xi(0), k(2), Len(k(2))
      apiCopyMemory t(3).xi(0), k(3), Len(k(3))
      
'     Activate...
      
      apiSetForegroundWindow mlvhCreatorHandle
      
'     Message...
      
      apiSendInput 4, t(0), Len(t(0))
      
'     Activate...
      
      apiSetForegroundWindow Application.hwnd
      
    End If
    
'   Return...
    
    mlfpCreatorEnumerate = r
    
  End Function
  
'
' EOF
'
' ..............................................................................
PS: I did not use SendMessageA or PostMessageA, as some applications are ignoring
the "press of Ctrl" message before it reach its destination; Excel is for example doing
such things. And there are surely some possible optimizations for the code :)

Just because I am curios, I am trying now to let the PDF Creator to print the document
after he has combined the docs. The problem is here, that I don't know the moment,
when the PDF Creator has finished combining the docs. I am searching a way how to
do this; perhaps a way can be to get the listview items (when reached one, then the
monitor is ready to print). Of course, any ideas and suggestion for this are welcome :)

Finally, thanks all for the suggestions and help :)

Best regards :)
 
Last edited:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
maninweb said:
I don't know the moment, when the PDF Creator has finished combining the docs

My standard route to this is to use DIR to check when the file appears to the OS. (Just nest the check in a Do/Loop.)

Wow... that's a whole lot of API's to work with. I'm still curious to know if you could just loop the cominining process... say print till there is five docs in the queue, combine them, then print five more, combine them with the already consolidated one and so on. I guess the issue there would be the same as what you're dealing with now though... when has the combining completed so that it is safe to move on?
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi Ken...

Thanks :)

Yes, the idea to combine the documents is IMHO still an option, however this requires some deeper changes in my code, so I moved it a little bit backwards. I will also test this, but this will take some time. In any case, I will report here.

Best regards :)
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi all...

just want to inform you, I have not forgotten this thread. But because I am very busy for the moment,
I did not have the time until now to continue on this problem. But I will continue as soon as possible :)

Best regards :)
 

DavidON

New member
Joined
Jan 11, 2012
Messages
1
Reaction score
0
Points
0
A solution.

I had exactly same the same problem with PDFCreator. My solution was a simple AutoHotKey macro. It sat in the background and every 60 seconds checked for this message. If it found it, it send an "Enter" to the message.

The result was that either the wait was actually finished and the Excel macro continued normally or the wait condition was not yet complete and so the message re-appeared - and was handled by the AutoHotKey macro within the next minute.

This saved my life on a project where I was having to build multi-page documents from hundreds of thousands of scans.

Regards,
David.
 
Top