How do you prevent printing dialog when using Excel PrintOut method

Question

When I use the PrintOut method to print a Worksheet object to a printer, the "Printing" dialog (showing filename, destination printer, pages printed and a Cancel button) is displayed even though I have set DisplayAlerts = False. The code below works in an Excel macro but the same thing happens if I use this code in a VB or VB.Net application (with the reference changes required to use the Excel object).

Public Sub TestPrint()
Dim vSheet As Worksheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set vSheet = ActiveSheet
    vSheet.PrintOut Preview:=False

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

EDIT: The answer below sheds more light on this (that it may be a Windows dialog and not an Excel dialog) but does not answer my question. Does anyone know how to prevent it from being displayed?

EDIT: Thank you for your extra research, Kevin. It looks very much like this is what I need. Just not sure I want to blindly accept API code like that. Does anyone else have any knowledge about these API calls and that they're doing what the author purports?

Solution 1

When you say the "Printing" Dialog, I assume you mean the "Now printing xxx on " dialog rather than standard print dialog (select printer, number of copies, etc). Taking your example above & trying it out, that is the behaviour I saw - "Now printing..." was displayed briefly & then auto-closed.

What you're trying to control may not be tied to Excel, but instead be Windows-level behaviour. If it is controllable, you'd need to a) disable it, b) perform your print, c) re-enable. If your code fails, there is a risk this is not re-enabled for other applications.

EDIT: Try this solution: How do you prevent printing dialog when using Excel PrintOut method. It seems to describe exactly what you are after.

Solution 2

If you don't want to show the print dialogue, then simply make a macro test as follows; it won't show any print dialogue and will detect the default printer and immediately print.

sub  test()

 activesheet.printout preview:= false

end sub

Run this macro and it will print the currently active sheet without displaying the print dialogue.

Solution 3

The API calls in the article linked by Kevin Haines hide the Printing dialog like so:

1. Get the handle of the Printing dialog window.
2. Send a message to the window to tell it not to redraw
3. Invalidate the window, which forces a redraw that never happens
4. Tell Windows to repaint the window, which causes it to disappear.

That's oversimplified to put it mildly.

The API calls are safe, but you will probably want to make sure that screen updating for the Printing dialog is set to True if your application fails.