Set printer in VBA

[jrisebo]

Here is some code that I use to print multiple sheets by pressing a button. I want it to change to a specific printer, called "PDFcreator" in my printer list. What can I add to do that? Thanks

Private Sub CommandButton1_Click()
'PRINT LRFD ATTACHED CALC'S WITH DB-SAE'S
'

'
Sheets("ATT INFO").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("ATT Service").Select
ActiveSheet.PageSetup.PrintArea = "$A$55:$F$137"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("ATT Strength").Select
ActiveSheet.PageSetup.PrintArea = "$A$56:$F$154"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("ATT Extreme").Select
ActiveSheet.PageSetup.PrintArea = "$A$56:$F$154"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("INPUT").Select
End Sub

 

[doofusboy]

Here's some code I used to allow user to change to Zebra printer. I then changed back to what they started from so they didn't have to [which is I capture the active default printer upfront]

    Set dlgSetPrinter = Application.Dialogs(xlDialogPrinterSetup)
    ' capture default active printer
    strPrinter = dlgSetPrinter.Application.ActivePrinter
    ' prompt change to Zebra printer; capture name of Zebra printer
    dlgSetPrinter.Show
    strZebraPrinter = Application.ActivePrinter

 

[jrisebo]

zebra printer is the name of a printer in your printer list?

 

[Glory]

That's exactly what I was going to ask.

What about if the printer is shared? Do you need the network path, how does that work?

 

[jrisebo]

I posted as shown, and it brings up a printer list box that I can pick which one to use. Great Success! Thanks

 

[Glory]

It looks like Access after 2002 had this great object. I wonder if there's anything like it in Excel. I already checked, it doesn't have exactly the same thing.

Set Application.Printer = Application.Printers("Name")

 

[Norie]

You could also specify the ActivePrinter argument when using PrintOut.

 

[Glory]

Much better.