Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Something _like_ "After Print Event" ??

    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.

  2. #2
    calacuccia
    Guest

    Re: Something _like_ "After Print Event" ??

    Hello cri ;-)

    Just one thing. This will be a VB job for sure.

    Will try to help you of course, but mainly I did not want to leave this occasion to say hello.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something _like_ "After Print Event" ??

    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.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something _like_ "After Print Event" ??

    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 ?

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Something _like_

    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

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something _like_ "After Print Event" ??

    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.
    Legare Coleman

  7. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Something _like_

    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

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something _like_

    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.
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something _like_

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

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something _like_

    This should solve it:

    <A target="_blank" HREF=http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/index.htm>http://www.rb-ad.dircon.co.uk/rob/ex...tips/index.htm</A>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •