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?

 

[Andrew Poulsom]

You can use Application.ActivePrinter to get the name of the currently active printer. It's read/write.

 

[Asator]

Hrm. Is there a way to select printers from a drop down menu and have it return that selection as a string?

 

[Andrew Poulsom]

If the user selects a printer from that dialog it becomes the ActivePrinter.

 

[Asator]

It changes the active printer in Excel, but it doesn't change the default printer.

Basically I have an Outlook macro that archives and prints attachments (PDF files, word documents, excel files, etc). I wanted to allow the user to select which printer they want to send to.

Hmm.. thinking out loud here...

Basically I could maybe call excel's printer setup dialog, then store the result of application.activeprinter (subtracting the " on NeXX") before and after the xlDialogPrinterSetup, set the default printer using wscript.network using that string, do my printing, then set it back to the original.

Will test and report.

 

[Asator]

Hmm... Any idea why this would fail? Get the error "Unable to get the Show property of the Dialog class".

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)
    
    Do Until prResponse = True
        prResponse = xlApp.Dialogs(xlDialogPrinterSetup).Show
    Loop
    
    prNew = Left(xlApp.ActivePrinter, InStr(xlApp.ActivePrinter, " on Ne") - 1)
    
MsgBox prOld & " --> " & vbNewLine & prNew
fail:
If Err.Number <> 0 Then
    MsgBox "err: " & Err.Number & vbNewLine & Err.Description
End If
Set xlApp = Nothing
Set prConfig = Nothing

End Sub

 

[Asator]

Bump

 

[Andrew Poulsom]

If you are late binding, Excel's constants won't be exposed, so you need to use the value of xlDialogPrinterSetup which is 9:

prResponse = xlApp.Dialogs(9).Show

 

[Asator]

        xlApp.Dialogs(9).Show

Same error... Show property of Dialog class failed.

 

[Andrew Poulsom]

Is Excel visible?