Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing the Active Printer (Excel 97/SR2)

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

    <pre> Application.ActivePrinter = "SRVNT01PRT_SCLR <font color=red>on Ne01</font color=red>:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, ActivePrinter:= _
    "SRVNT01PRT_SCLR <font color=red>on Ne01</font color=red>:", Collate:=True
    Application.ActivePrinter = "SRVNT01PRT_ADM5SI <font color=red>on Ne00</font color=red>:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, ActivePrinter:= _
    "SRVNT01PRT_ADM5SI <font color=red>on Ne00</font color=red>:", Collate:=True</pre>


    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)? <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Changing the Active Printer (Excel 97/SR2)

    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:

    <pre>Application.ActivePrinter = "Printer_Name on LPT1:"</pre>



    Andrew C

  3. #3
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing the Active Printer (Excel 97/SR2)

    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:<pre>CurPrntr= Application.ActivePrinter
    If Right(CurPrntr,5)="Ne00:" then Application.ActivePrinter = _
    PrinterOnNE01
    Else Application.ActivePrinter = PrinterOnNE00
    Print Routine here
    Application.ActivePrinter = CurPrntr</pre>


  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing the Active Printer (Excel 97/SR2)

    And if the user has more than 2 printers mapped?
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Changing the Active Printer (Excel 97/SR2)

    Interesting, 'cos I get:

    Application.ActivePrinter = "HP DeskJet 1600C ColorSmart on abcdedc22-sm-s-c"
    <switch>
    Application.ActivePrinter = "HP LaserJet 5Si on abcdedc12-sm-s"

    It may also depend on your Lan environment and setup.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •