none
Defaulting to a Network Printer for a Series of Different Printouts, Regardless of User RRS feed

  • Question

  • How can I set Application.ActivePrinter to a specific network printer, regardless of which user runs the macro?

    In recording part of a macro, I opened the Print dialog & selected the desired printer, resulting in the following code:

         Application.ActivePrinter = '\\Server Name\Printer Name on Ne16:'

    Recording the same step on User A's PC results in the same line but with the printer "on Ne09:."

    Recording it on User B's PC shows it "on Ne11:."

    There are various printouts to be done & I certainly don't want the users to have to select the printer from the Print dialog for each printout. The printer in question is not the default printer for any of the users.

    How can I specify the .ActivePrinter within the code so that it will work correctly regardless of which user runs the macro?

    This is in Office 2007.
    Friday, January 18, 2013 3:07 PM

All replies

  • Either use code like

    If Application.Username = "User A" Then  Application.ActivePrinter = '\\Server Name\Printer Name on Ne09:'

    If Application.Username = "User B" Then  Application.ActivePrinter = '\\Server Name\Printer Name on Ne11:'

    etc....

    Or create a lookup table to pull the printer connection:

    Application.ActivePrinter = '\\Server Name\Printer Name on " & Application.Vlookup(Application.username, Range("PrinterTable"),2,False)

    Friday, January 18, 2013 4:10 PM
  • In other words, I have to know ahead of time which port the printer is connected to on each user's machine?

    Ugh!

    Friday, January 18, 2013 5:02 PM
  • No, you don't need to know ahead of time - but you didn't say how long your list of users is. You can use VBA to determine all the printers attached to a machine, then choose the printer using logic of some sort.  Vist Chip Pearson's site

    http://www.cpearson.com/excel/GetPrinters.aspx

    Friday, January 18, 2013 6:11 PM
  • That looks very promising. Thank you, Bernie.
    Friday, January 18, 2013 6:24 PM