Pausing until Excel has finished

[Milothicus]

this doesn't actually have anything to do with access, but the vba code would be the same if it were... and yes, i really do need the code to wait for excel.

i'm exporting data from another program (solidworks, actually) and creating reports in excel. in my options form, i allow the user to choose wether they'd like the report to print, or just preview the report, to make sure it's right before printing... the problem comes once the print preview has been open for about 5 seconds... i get a 'server busy' error in my solidworks window, i'm assuming because the code is trying to continue working on the spreadsheet, but since the report's in print preview, it can't do anything.

any idea how to pause the code until the print preview has either been printed or closed?

 

[modest]

Can you open the print preview in a dialog window? If not you may have to use a little API.

Additionally, you could get the handle of the window and use the form's timer event to see if the window is still open... if it is don't do anything, if not, continue with the code.

Does that help at all? There may be an easier way, but that's all I have on the top of my head.

 

[The_Doc_Man]

Look up "DoEvents" in the Help Files. See if that looks productive in your situation. It is a method you could invoke in some contexts. It may be relevant.

 

[Milothicus]

I've looked at that DoEvents thing before, but can't really understand its logic. the help file isn't very clear, either

what i'm trying to do sounds... sort of ... like passing control to the operating system, but how will it know when to get it back? wouldn't that be dependent on what other windows/programs are open? how will it know to watch just the print preview in excel?

also their example doesn't exactly clarify things:

DoEvents Function Example
This example uses the DoEvents function to cause execution to yield to the operating system once every 1000 iterations of the loop. DoEvents returns the number of open Visual Basic forms, but only when the host application is Visual Basic.

' Create a variable to hold number of Visual Basic forms loaded 
' and visible.
Dim I, OpenForms
For I = 1 To 150000    ' Start loop.
    If I Mod 1000 = 0 Then     ' If loop has repeated 1000 times.
        OpenForms = DoEvents    ' Yield to operating system.
    End If
Next I    ' Increment loop counter.

modest, how would i get the handle of the window? that's not a function i've seen before. is this something that could be triggered when the print preview is closed? i need to keep the entire workbook open, and just close the print preview so i can print another worksheet...

 

[Dembrey]

See if this helps:

http://www.mvps.org/access/api/api0004.htm

 

[Milothicus]

Hmm... it looks like that would wait for the whole excel application to close...

reading back now, i see i didn't mention some important facts...

i'm printing anywhere from 1 to 5 reports from the same spreadsheet, so i need excel to stay open for more processing once the print preview has been either closed or printed...

i did find that there is sort of an 'OnPrint' event that i can place in the workbook, but is there a way to use that event to trigger something in my solidoworks code?

I suppose what i'm asking is... how do i pass information from a macro running in one program to a macro running in another?

 

[modest]

Each window has a "handle" or ID that the sytems uses to track it. By getting the window's handle when the window opens, you can test to see if it exists.

Additionally, each window has a process loop that constantly sends information to the OS. Each window performs events, which the user could capture to perform other events when the events occur. In pseudocode it may look something like this:

Select Case TheWindowEvent
   Case Mouse Move:
   Case Mouse Click:
   Case Window Close:
End Select

So when the window closes, you could put an event to let the program know it's closed and to do something else. I was doing something similar with a query window.

See here


I hope this helps.

 

[Milothicus]

Here's what was recommended on an excel help forum. i'm not entirely sure how it works, but i think it just filters out all OLE popups. I'll be trying it today.

Private Declare Function _
CoRegisterMessageFilter Lib "OLE32.DLL" _
(ByVal lFilterIn As Long, _
ByRef lPreviousFilter) As Long


Put the declaration (Private Declare Function etc.) at the top of a regular
bas module. If you are going to call it from a different module, change the
declaration from Private to Public.

Now, find where you are starting up your Excel instance (either set
[variable] = new Excel.Application or CreateObject(...). Put the line

CoRegisterMessageFilter 0&, lngMsgFilter

just before it. Now find where you are destroying your Excel instance
([variable].Quit: set Excel = nothing) and put the line

CoRegisterMessageFilter lngMsgFilter, lngMsgFilter

just after it. You will also need to declare the lngMsgFilter variable in
your routine. This will stop the message appearing when solidworks thinks
that Excel has stopped responding. You could experiment with putting these
lines just before and after where you call the printpreview if you are
worried about loosing all messages should Excel hang for another reason.

 

[TotalMediaBiz]

Pausing until Excel has finished...

I don't know if this will help with your other application, but to pause a VB macro for Excel, I used:

Do While ActiveWindow.View = xlPrintPreview
Loop



Once I press 'Close' or go through the print dialog from the print preview, I am able to resume the macro. In my case, in the next line of code, I call a UserForm object, but I assume it would work the same for other functions.

It has taken me DAYS to find this, and the answer now seems so simple. If only I could find a way to get my UserForms to display numbers as fractions...

 

[Milothicus]

so simple, yet so smart.... i think i'd rather use that, since i don't really know what my current method does. i'm guessing it just passes all messages into that variable, but i don't know for sure....