Error setting ActivePrinter

Question

I want to automate directing a printout to a specific printer (in a macro). I'm using the following code to test setting the preferred printer:

Sub printReport()

'

' printReport Macro Testing

'

Const RxPrinter As String = "RxPrinter on Ne00"

Const PDFCreator As String = "PDFCreator"

Const deskPDF As String = "deskPDF"

Const EPSON As String = "EPSON NX125 NX127 Series"

    Application.ActivePrinter = RxPrinter

    'Application.ActivePrinter = PDFCreator

    'Application.ActivePrinter = deskPDF

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _

        IgnorePrintAreas:=True

End Sub

____________

When I run this sub with DEBUG>run it issues error 1004 Method 'ActivePrinter' of object '_Application' failed. I've seen several examples in other topics on this forum [should say on other forums] that use the type of code that I'm using, that apparently do work OK.

I'm using EXCEL 2010 with Windows 7.

TIA for any help,

Phil

Answer 1

Assuming you have inserted a module containing the code from Chip's site, you could put it to use like so:

Sub printReport()

'

' printReport Macro Testing

'

    Const RxPrinter As String = "RxPrinter on Ne00"

    Const PDFCreator As String = "PDFCreator"

    Const deskPDF As String = "deskPDF"

    Const EPSON As String = "EPSON NX125 NX127 Series"

    Dim sPrinters() As String

    Dim lCt As Long

'Get a list of all printers with their ports

    sPrinters = GetPrinterFullNames

Loop through the list of printers until a matching name is found:

    For lCt = LBound(sPrinters) To UBound(sPrinters)

        If sPrinter(lCt) Like RxPrinter & "*" Then

'Found one...

            Print Application.ActivePrinter = sPrinters(lCt)

            'Application.ActivePrinter = PDFCreator

            'Application.ActivePrinter = deskPDF

            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _

                                                 IgnorePrintAreas:=True

            Exit For

        End If

    Next

End Sub

Answer 2

Hi Phil,

If you run this line of code:

Application.Dialogs(xlDialogPrinterSetup).show

does it show the ports?

No.

The code from Pearson's website...

http://www.cpearson.com/Excel/GetPrinters.aspx,

and using the download link...

http://www.cpearson.com/Zips/ActivePrinter.zip

is the code that I used.

I will show the output of both methods if I can include an attachment. Is there a way?

Phil

Answer 3

Hi Phil,

If you run this line of code:

Application.Dialogs(xlDialogPrinterSetup).show

does it show the ports?

Answer 4

If you record a macro setting the printer, does it record the exact same name as in your constant?

Jan,

Thanks for your reply. You were on the right track with *exact same name as in your constant.*I had previously recorded selecting a printer other than the active printer. Unfortunately, the code from the recorded macro did not show any application.activeprinter =

But you got me to thinking about exact same name. Turns out you need to

"RxPrinter"  the name that devices and printers displays

"RxPrinter on Ne00:" is required to work with ActivePrinter.

I used some code from Charles Pearson's website to display printers and their associated port. That display is what led to try using the "on Ne00:" of the printer name. However,  I've done some experimentation with printers that apparently don't have a port associated with them and I don't find any way to use application.activeprinter with them.

Phil

Answer 5

If you record a macro setting the printer, does it record the exact same name as in your constant?