Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Macro to Select Printer?

This is a discussion on Macro to Select Printer? within the Excel Questions forums, part of the Question Forums category; Hi, I'd asked a question a few weeks ago how to do this, but it was never answered. Now I'm ...

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Posts
    145

    Default Macro to Select Printer?

    Hi,

    I'd asked a question a few weeks ago how to do this, but it was never answered. Now I'm just wondering if it's even possible.

    I know that a macro can select print areas of various tabs and send them to your printer, but can a macro be written to select 1 of several printers? Just curious.

    Thanks,
    Ed

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default

    Something like this.

    Application.ActivePrinter = "\\MDTNTB\bri1pl on Ne01:"

    You can record a macro to get the exact path for your printer

  3. #3
    Board Regular
    Join Date
    Aug 2002
    Posts
    145

    Default

    I don't understand that formula. I copied and saved it as a macro in a test spreadsheet, but nothing happens if I run it. Obviously I'm a bit challenged when it comes to programming/code.

    Ed

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default

    Its not a formula, its a macro, that can't be done with a formula.

    Another approach is to use something like this:

    Application.Dialogs(xlDialogPrinterSetup).Show

    If you need help on how or where to enter this, look here:

    http://www.mrexcel.com/tip038.shtml
    http://www.mrexcel.com/tip019.shtml
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Board Regular
    Join Date
    Aug 2002
    Posts
    145

    Default

    Thanks! I did figure out how to run the macro and it showed the path to my 2 printers. Now my question is where would I put the command in the following macro to send it to a particular printer?

    Sub printabrenewal()
    Application.ScreenUpdating = False
    Sheets("Input").Select
    Range("A1:h151").Select
    ActiveSheet.PageSetup.PrintArea = Selection.Address
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Census").Select
    Range("A1:I60").Select
    ActiveSheet.PageSetup.PrintArea = Selection.Address
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End Sub

    My active printer is \\ATLREG\ATR07_130 but I'd like to make this macro-button send to the other printer, which is \\ATLREG\ATR07_63 .. can it do this without changing the active printer on the PC?

    I tried putting your command (with my printer path) in the 2nd line of the macro, but I got the error

    Run-time error '1004':

    Method 'Active.Printer' of object '_Application' failed

    Any suggestions?

  6. #6
    Board Regular
    Join Date
    Aug 2002
    Posts
    145

    Default

    Is this the correct code to select a printer in a macro?

    Application.ActivePrinter = "\\ATLREG\ATR07_130"

    I've tried inserting this line in the above macro in several different places, but I keep getting the Run-time error.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default

    No, it isn't. Because you have to supply the port to which is connected.

    Try this. Manually change the printer to the one that you want.

    Then, go to the VBE, and press Ctrl G. In there, type the following

    ?Application.ActivePrinter

    the text that it spits out, is the one that you need to use in your macro.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    Board Regular
    Join Date
    Aug 2002
    Posts
    145

    Default

    Wow, thanks! That worked. But it actually changed the default printer of the PC.

    Is there no way to send the file to the printer I insert in the code without changing the default printer of the PC? Or do I need to just hardcode a line at the end of the macro to switch back to the original printer?

    Ed

  9. #9
    New Member
    Join Date
    May 2002
    Location
    Colorado
    Posts
    49

    Default A different approach......

    What I have is one printer with two trays of different size paper. I reinstalled this printer through control panel. It shows up as 'printer name' (copy 2). Paper tray #1 is the default tray for the original printer. Paper tray #2 is set to be the default of the 'copy 2' printer. Then I use the following macros in my "print" macro:
    Sub Ptray1x()
    Application.ActivePrinter = "SHARP AL-1000 Series on LPT1:"
    End Sub
    Sub Ptray2x()
    Application.ActivePrinter = "SHARP AL-1000 Series (Copy 2) on LPT1:"
    End Sub

    Now if I want to print from paper tray #1, I add Ptray1x to my print macro. Likewise, Ptray2x uses tray #2. It works quite well. Maybe this will help.

  10. #10
    Board Regular
    Join Date
    Aug 2002
    Posts
    145

    Default

    Correction .. it changed the printer setting for anything else I do in Excel. All other programs still send to the original/default printer. Hmm ...

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com