Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    409
    Thanks
    37
    Thanked 5 Times in 5 Posts

    Macro that Prints to Another Printer (2007)

    I have some macros that print out named ranges from worksheets.

    How do I include the name of a non-default printer in the macro? I am guessing that I would use the name from the print dialog window, but I'm lost past that.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that Prints to Another Printer (2007)

    In older versions of Excel it would be

    ActiveWorkbook.PrintOut ActivePrinter:="My Other Printer"

    using the name from the Print dialog. Or you could change the active printer and reset it:

    Dim strOriginalPrinter As String
    ' Store name of current printer
    strOriginalPrinter = Application.ActivePrinter
    ' Set another printer
    Application.ActivePrinter = "My Other Printer" ' use name from print dialog
    ' Print something
    ActiveSheet.PrintOut
    ' Restore original printer
    Application.ActivePrinter = strOriginalPrinter

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    409
    Thanks
    37
    Thanked 5 Times in 5 Posts

    Re: Macro that Prints to Another Printer (2007)

    I will try this - but I'm guessing that you haven't tried it yet in 2007?

    Am I reading this correctly - that the first suggestion will change the default after the macro is done, while the second will allow me to go back to the original default?

    Further, what would I cut out of either statement to just get the print dialog box instead?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that Prints to Another Printer (2007)

    I don't have Office 2007, so I cannot try there.

    Specifying a printer in the PrintOut instruction will leave that printer the active printer (in Excel, not system-wide).

    If you want to display the print dialog, you'd use

    Application.Dialogs(xlDialogPrint).Show

  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    409
    Thanks
    37
    Thanked 5 Times in 5 Posts

    Re: Macro that Prints to Another Printer (2007)

    New problem: the name for the networked printer in the print dialog box is too long to see, and it doesn't seem to copy and paste.

    I looked in Printers and Faxes, but the name there looks like a nickname.

    Suggestions?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that Prints to Another Printer (2007)

    Does this macro (originally from Jim Rech) help?

    Sub ListPrinters()
    Dim wshNetwork As Object
    Dim oDrives As Object
    Dim oPrinters As Object
    Dim i As Integer
    Set wshNetwork = CreateObject("WScript.Network")
    Set oDrives = wshNetwork.EnumNetworkDrives
    Set oPrinters = wshNetwork.EnumPrinterConnections
    For i = 0 To oPrinters.Count - 1 Step 2
    Debug.Print "Port " & oPrinters.Item(i) & " = " & oPrinters.Item(i + 1)
    Next i
    End Sub

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro that Prints to Another Printer (2007)

    You can set it manually to the printer you want, then in the Immediate window in the VBEditor, type:
    <code>?Application.Activeprinter</code>
    and you will see the name to use.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    409
    Thanks
    37
    Thanked 5 Times in 5 Posts

    Re: Macro that Prints to Another Printer (2007)

    Thanks folks.

    I'll keep the last 2 suggestions in mind, but I think bringing up the printer dialog box might not be a bad idea for this workbook anyway.

Posting Permissions

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