Results 1 to 10 of 10

Print to a specific Printer using VBA

This is a discussion on Print to a specific Printer using VBA within the Excel Questions forums, part of the Question Forums category; Hi all, I'm using this code to print a sheet using VBA to a specific printer, called Lexmark E350d: Sub ...

  1. #1
    Board Regular
    Join Date
    Aug 2010
    Posts
    94

    Default Print to a specific Printer using VBA

    Hi all,

    I'm using this code to print a sheet using VBA to a specific printer, called Lexmark E350d:

    Sub MyPrint()
    Dim sCurrentPrinter As String
    Const MyPrinter As String = "Lexmark E350d"
    sCurrentPrinter = ActivePrinter
    ActivePrinter = MyPrinter
    ActiveSheet.PrintOut ' ActivePrinter = sCurrentPrinter
    End Sub


    Upon executing the code, I get Run-time error '1004': Method 'ActivePrinter' of object '_Global' failed - what does this error mean, debugging highlights the code highlighted in blue above. Thanks in advance!
    Last edited by justanotheruser; Jul 17th, 2011 at 12:03 PM. Reason: fix code, this bit is blue

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,297

    Default Re: Print to a specific Printer using VBA

    What code do you get if you record a macro while setting that printer as the active printer?
    Microsoft MVP - Excel

  3. #3
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,440

    Default Re: Print to a specific Printer using VBA

    Shouldn't it be

    Code:
    Application.ActivePrinter
    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    Board Regular
    Join Date
    Aug 2010
    Posts
    94

    Default Re: Print to a specific Printer using VBA

    Hi - thanks for your fast response!

    Code:
    Sub PrinttoLexmark()
    '
    ' PrinttoLexmark Macro
    ' this is a macro used to print to Lexmark e350d printer.
    '
    
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
    End Sub
    This printer is not set as the default printer either, so I selected it but it doesn't seem to add the printer name? Thanks again.

  5. #5
    Board Regular
    Join Date
    Aug 2010
    Posts
    94

    Default Re: Print to a specific Printer using VBA

    hi VoG - where should that code go?

  6. #6
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,440

    Default Re: Print to a specific Printer using VBA

    Try like this

    Code:
    Sub MyPrint()
    Dim sCurrentPrinter As String
    Const MyPrinter As String = "Lexmark E350d"
    sCurrentPrinter = Application.ActivePrinter
    Application.ActivePrinter = MyPrinter
    ActiveSheet.PrintOut '
    Application.ActivePrinter = sCurrentPrinter
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    Board Regular
    Join Date
    Aug 2010
    Posts
    94

    Default Re: Print to a specific Printer using VBA

    Same error message unfortunately!

  8. #8
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,440

    Default Re: Print to a specific Printer using VBA

    Try printing to the Lexmark then run this - it should give you the correct string to use

    Code:
    Sub test()
    MsgBox Application.ActivePrinter
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  9. #9
    Board Regular
    Join Date
    Aug 2010
    Posts
    94

    Default Re: Print to a specific Printer using VBA

    ah yes, I fixed it - because it's a network printer it was "on Ne06:". Thanks again!

    Any chance you could look at this thread How do you make a macro dynamic? about a macro for copying the contents of a shape?

  10. #10
    Board Regular concreteinterface's Avatar
    Join Date
    Jul 2008
    Posts
    97

    Default Re: Print to a specific Printer using VBA

    Guys-
    I'm just starting to learn VBA on my own. I wanted you to know this thread helped me out with what I was trying to accomplish. Thank you. Here's how I understand this. Is this correct?

    Sub MyPrint()


    'I don't know what this does
    Dim sCurrentPrinter As String


    'Converts the actual printer name to an easy name. In this case "MyPrinter"
    Const MyPrinter As String = "HP0EC7A7 (HP Photosmart 6510 series) on Ne01:"


    'I'm guessing this tells the system that the active printer before the macro is run is actually named sCurrentPrinter
    sCurrentPrinter = Application.ActivePrinter


    'This makes the active printer, MyPrinter
    Application.ActivePrinter = MyPrinter


    'This prints out the active sheet.
    ActiveSheet.PrintOut '


    'This switches the program back to whatever the active printer was before you ran the macro
    Application.ActivePrinter = sCurrentPrinter


    End Sub

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