Macro to Print to a Nominated Printer

DavidWF · Jan 30, 2016

I’d appreciate some further guidance, this time with a printing macro.

On a previous system I had two macros, each attached to a button for printing. I had the option of either printing to a .pdf file, or to my physical printer. I want to set up the same process again but I can’t work out how/where to locate the names of the printers in a form that the macro can recognize. I created a new macro but it doesn’t look anything like the earlier macros and doesn’t contain any reference to the selected printer. The new macro, since it doesn't contain anything that directs the output, simply prints to the last, selected printer.

The guts of the previous macros were:
To a .pdf file:

Application.ActivePrinter = "PrimoPDF on Ne00:"
ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,""PrimoPDF on Ne00:"",,TRUE,,FALSE)"

To the HP printer:

Application.ActivePrinter = "HP Officejet 5600 series on Ne03:"
ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,""HP Officejet 5600 series on Ne03:"",,TRUE,,FALSE)"

The new macro shows:
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False

I’ve looked in Devices and Printers and can’t find anything that resembles "PrimoPDF on Ne00:" or
"HP Officejet 5600 series on Ne03:". I can find the printers OK but they lack the “ . . . on Ne??” bit at the end.

Where do I look to find the names that the macro needs?

In case it’s relevant, I’m using Win10 and MS Office 2013. The earlier macros were under Win7 and, probably, MS Office 2007 (or maybe 2010).

pgc01 · Jan 30, 2016

Hi

If you are using Office 2013 you can use the Application.Printers collection to get the info on the printers installed.

Check:

https://msdn.microsoft.com/en-us/library/office/ff835733.aspx

DavidWF · Jan 30, 2016

Thanks for the very prompt response pgc01.

I copied the complete coding from the link you provided. When I ran the macro it stopped on the 3rd Dim statement, with "prtLoop as Printer" highlighted and with the pop-up:
Compile error:
User-defined type not defined

How do I resolve?

Thanks
David

pgc01 · Jan 30, 2016

Hi David

That means that you have to set a reference in the vba editor in Tools->References. Unfortunately I don't have excel 2013 installed here and I can't give you the name of the reference to set (you can try to look for it).

One thing that will usually solve the problem is to use a generic object declaration instead

Code:

Dim prtLoop As Object

If it doesn't work, and you can't find the reference to the printers objects, an alternative is to use Chip Pearson's code:

Printers In VBA

Very easy to use, returns a list of the names of the printers.

Andrew Poulsom · Jan 30, 2016

Pedro,

It seems that the code in the first link you posted relates to Access. There is no such object in Excel 2013.

DavidWF · Jan 30, 2016

Thanks again PGC - I'll do battle and see how I go. Unless . . . . Andrew - can you offer any further guidance?

(Guys - I'm going off-line for about 3 hr so I'll be late responding)

pgc01 · Jan 30, 2016

Thank you Andrew

I don't have xl2013 installed. I saw the code in an Office 2013 page and did not explore this carefully enough.

BTW, do you mean that even if you have access 2013 installed you still can't use the Printer object even if you set reference to the access object library? (and creating an access application)?

P. S. David, you can use Chip Pearson's code. I've used it several times in the past and it's very easy to use.

Andrew Poulsom · Jan 30, 2016

I modified the code in the link to use Access late-bound. That said I'm not sure it will give the OP what he wants.

Code:

Sub ShowPrinters()
    Dim objAccess As Object
    Dim strCount As String
    Dim strMsg As String
    Dim prtLoop As Object
     
    On Error GoTo ShowPrinters_Err
    Set objAccess = CreateObject("Access.Application")
 
    If objAccess.Printers.Count > 0 Then
        ' Get count of installed printers.
        strMsg = "Printers installed: " & objAccess.Printers.Count & vbCrLf & vbCrLf
     
        ' Enumerate printer system properties.
        For Each prtLoop In objAccess.Printers
            With prtLoop
                strMsg = strMsg _
                    & "Device name: " & .DeviceName & vbCrLf _
                    & "Driver name: " & .DriverName & vbCrLf _
                    & "Port: " & .Port & vbCrLf & vbCrLf
            End With
        Next prtLoop
     
    Else
        strMsg = "No printers are installed."
    End If
     
    ' Display printer information.
    MsgBox Prompt:=strMsg, Buttons:=vbOKOnly, Title:="Installed Printers"
     
ShowPrinters_End:
    Exit Sub
     
ShowPrinters_Err:
    MsgBox Prompt:=Err.Description, Buttons:=vbCritical & vbOKOnly, _
        Title:="Error Number " & Err.Number & " Occurred"
    Resume ShowPrinters_End
     
End Sub

DavidWF · Jan 30, 2016

Brilliant!!

Sorry for the late response, I got badly side-tracked.

Andrew - that coding opened up Access and also generated a list of the installed printers, but unfortunately that list more or less duplicated what I can already see in Devices and Printers; it lacked the critical "on Ne??" bit on the end of each printer name.

I then followed the Chip Pearson link and, through my currently poor understanding of VBA, was gob-smacked by the coding and how to implement it. My simple mind suggested that "download a working XLS workbook" might be the way to go, so I did. "List Printers" brought a pop-up with another "List Printers" button and hitting that gave me exactly what I was looking for - a list of the installed printers with " . . . on Ne00:", " . . . on Ne01:" after each name. Perfect!

PGC (Pedro?) / Andrew - thank you both so very much for your assistance. You have jointly reinforced my admiration for the helpfulness and knowledge that I gain from this site, and the consequent resolution of my problems.

pgc01 · Feb 2, 2016

I'm glad it helped. Thanks for the feedback.

Macro to Print to a Nominated Printer

DavidWF

Board Regular

Excel Facts

pgc01

MrExcel MVP

DavidWF

Board Regular

pgc01

MrExcel MVP

Andrew Poulsom

MrExcel MVP

DavidWF

Board Regular

pgc01

MrExcel MVP

Andrew Poulsom

MrExcel MVP

DavidWF

Board Regular

pgc01

MrExcel MVP

Similar threads

Forum statistics

Share this page

Macro to Print to a Nominated Printer

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock