Changing the Active Printer (Excel 97/SR2)

Topic

New Reply

When I record a macro for changing the printer, then look at the code this is what I get.

    Application.ActivePrinter = "SRVNT01PRT_SCLR on Ne01:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, ActivePrinter:= _
        "SRVNT01PRT_SCLR on Ne01:", Collate:=True
    Application.ActivePrinter = "SRVNT01PRT_ADM5SI on Ne00:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, ActivePrinter:= _
        "SRVNT01PRT_ADM5SI on Ne00:", Collate:=True

the prt_adm5si was the default printer, then I change to prt_sclr then back to prt_adm5si. My issue is what is highlited in red above. This number changes depending on which printer was installed first. Is this number part of excel or windows. I have several people that use a shared add-in and I would like to be able to change their printer with code. My problem is no one has their printers installed in the same order, so what is Ne01 for me may be Ne02 for someone else. Any ideas on how to get around this (other than uninstalling each persons printers and reinstalling in a specific order)?

Reply | Quote

Replies

The following (or something along the same lines) should work provided that each user has the same name (Printer_Name in this example) for the printer in question, assuming it is mapped to LPT1:

Application.ActivePrinter = "Printer_Name on LPT1:"

Andrew C

Reply | Quote

My understanding is that it is part of Windows and lodged in the registry – the exact location depending on the OS. I had a similar problem defining a fax-printer on the server.
Try something along the lines of:

CurPrntr= Application.ActivePrinter
If Right(CurPrntr,5)="Ne00:" then Application.ActivePrinter = _
            PrinterOnNE01
   Else Application.ActivePrinter = PrinterOnNE00
Print Routine here
Application.ActivePrinter = CurPrntr

Reply | Quote

And if the user has more than 2 printers mapped?

Reply | Quote

Interesting, ‘cos I get:

Application.ActivePrinter = “HP DeskJet 1600C ColorSmart on abcdedc22-sm-s-c”

Application.ActivePrinter = “HP LaserJet 5Si on abcdedc12-sm-s”

It may also depend on your Lan environment and setup.

Reply | Quote