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?

Answer 1

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.

Answer 2

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.