Printing with VBA

[Asator]

I know there's a few different ways to store the default printer as a string, but is there a way I can then prompt the user from a popup with a list of printers, and set their selection as the default printer?

application.dialogs(xldialogprintersetup).show only returns true when they select a printer. I can use wscript.network to list all printers and set a default printer, but I don't think there's a way to have it return the default printer (in UNC format).

TL;DR - I want to store the current default printer, prompt the user with a list of installed printers, set their selection as default, then reset the printer to their pre-macro default when done.

Is this possible?

 

[Asator]

No, but the process launches and the xlapp.activeprinter does return the current default printer.

 

[Andrew Poulsom]

How can Excel show a dialog if it's not visible?

 

[Asator]

That's a good point. I was hoping I could call that speicific dialog box to set the printer, but I guess that's not going to work.

Any ideas on how I could accomplish this? Is there a way I can generate a popup window with a drop down menu whose values are populated by an array? I could enumerate all the printers from wscript.network to create that array...

 

[Andrew Poulsom]

Have you tried making Excel visible in you code?

 

[Asator]

Just tried it in my test code:

Sub setprinter()
Dim xlApp As Object
Dim prConfig As Object
On Error GoTo fail
Set xlApp = CreateObject("excel.application")
Set prConfig = CreateObject("wscript.network")
    prOld = Left(xlApp.ActivePrinter, InStr(xlApp.ActivePrinter, " on Ne") - 1)
    xlApp.Visible = True
    Do Until prResponse = True
        prResponse = xlApp.Dialogs(9).Show 'xlDialogPrinterSetup
    Loop
    xlApp.Visible = False
    prNew = Left(xlApp.ActivePrinter, InStr(xlApp.ActivePrinter, " on Ne") - 1)

Same error...

 

[Andrew Poulsom]

Try including this as well:

xlApp.Workbooks.Add

 

[Asator]

Interdasting. That seems to have fixed it.

When excel opens a new instance, is the ActivePrinter always set to to the default printer (until changed)?

 

[Asator]

Also it works without making excel visble now. Great success.