Something _like_ “After Print Event” ??

Topic

New Reply

How to determine the moment in VBA/Excel 97 the print job was sent to spooler/printer ? I trigger a sub which hides empty rows for printing with the BeforePrint event, however I want to revert the display to normal modus a.s.a.p., meaning the moment it would not alter/influence the print job anymore.

Reply | Quote

Replies

I had a similar requirement in an application that I wrote, and unfortunately I did not find an easy solution. What I ended up doing was to have the application set up its own Menu and tool bars and replace the Print buttons with buttons that execute my own VBA code. You could also just replace the buttons on the standard menus and tool bars it this is the only change you need to make. Then the VBA code displays a print setup form if necessary, makes the format changes, uses the .Printout method to print he necessary ranges, and then changes the formats back. The really complex part comes in managing when your menu and tool bar changes get made and when you change them back to the standard Excel commands. There are many events that have to do this, and Excel is not very consistent about when some of these events fire. Good luck.

Reply | Quote

LegareColeman, thank you for your response. I do not think I will go to this extreme. I am pretty sure Excel gets some ‘feedback’ from the spooler/print by the operating system and/or spooler/printer software, only how to retrieve it in VBA ?

Reply | Quote

Hi cri,

This sounds like something that should be able to be done via a Windows API call.
I don’t know enough about the topic to point you to a specific one, but that’s the direction I’d look in if I had to do something like this.

Gary

Reply | Quote

But I don’t think that feedback from the spooler/printer driver is what you want. I think that what you want is feedback from Excel when it finishes giving the print file to the spooler/print driver. It could be hours (days) before the spooler finishes printing, and the spooler does not really know when it has all of the printing.

Reply | Quote

Hi Legare,

I guess I was thinking more of the latter from the spooler: “I’ve got all of your print file”, rather than “I’m finished printing the file”.

Does Excel ‘know’ any more about the status of the print file than the initial PrintOut command – which starts sending the print file to the spooler? I’m only guessing, but my thought was that all the application knows how to do is “PrintOut” and that Windows takes it from there, including giving feedback to the application about status.

We need a good Win API person here to answer our questions!

Gary

Reply | Quote

Excel has to create the print image and send it to the printer driver. Windows really doesn’t do much in this process at all. So, yes, Excell does know when the last of the print file has been sent to the driver. The print driver knows that this print file has been closed, but does not know if another is going to be opened or not (the program could close the file between pages for example). So the driver really does not know when the print is complete. Unfortunately, Excel does not raise an event when it completes the printing.

Reply | Quote

LegareColeman, thank you for comfirming my suspicion that this might be another inexplicable omission by Microsoft.

Reply | Quote

This should solve it:

http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/index.htm

Reply | Quote