Excel: Print to specific printer (Excel 2000)

Topic

New Reply

I have two printers. One for printing to tray 2 en one for printing to tray 3.
I recorded two macro’s for printing to each printer.

It works perfect on 1 workstation. But when I want to use the same macro on another workstation, it seems the printername is different (printerdriver is installed on the server).

The first section of the name is equal but at the end it says: on Ne. e.g. “ADM01HP LaserJet 4M on Ne04:”

The varies on each workstation. Does anyone know where this last section/number comes from (in Word it doesn’t display this number when I record a macro)?

Reply | Quote

Replies

I saved a print macro on mine and noticed that it put my printer port on the end (Com2). So I would assume that Ne04 is your printer port.
Hope this helps.

Reply | Quote

I saved a print macro on mine and noticed that it put my printer port on the end (Com2). So I would assume that Ne04 is your printer port.
Hope this helps.

Reply | Quote

This is from Word, but here is some code I use to switch printers. If you want to simply switch printers, take of the “On …”. The code will still work.

Sub SelectHP4K()

strPrinter = Left(ActivePrinter, Len(ActivePrinter) – 9)

If strPrinter “fs1HP4K_3rd” Then
ActivePrinter = “fs1HP4K_3rd”
End If

End Sub

Reply | Quote

This is from Word, but here is some code I use to switch printers. If you want to simply switch printers, take of the “On …”. The code will still work.

Sub SelectHP4K()

strPrinter = Left(ActivePrinter, Len(ActivePrinter) – 9)

If strPrinter “fs1HP4K_3rd” Then
ActivePrinter = “fs1HP4K_3rd”
End If

End Sub

Reply | Quote

Here’s how I set up a worksheet to handle switching to the non-default printer. First, in a blank Excel workbook I started the macro recorder:

Sub Macro1()
'
' Macro1 Macro
' Using File | Print | select printer | Close to read each
' available printer on the computer.
'
    Application.ActivePrinter = "IBM InfoPrint 40 on Ne00:"
    Application.ActivePrinter = "NetworkServerLexmark Optra C710 on Ne02:"
    Application.ActivePrinter = "NetworkServerHP DeskJet 1600C on Ne01:"
    Application.ActivePrinter = "HP LaserJet 1100 (MS) on LPT1:"
End Sub

Not only does this give you the exact names of each printer on the system but also the full command you can copy and paste into your code.

However, to create a portable worksheet I turn the printer names into variables. On a ‘control’ sheet or in some other convenient location I set up named ranges to contain the desired printer and default printer names. Then the following code is used to specify which printer to use:

ReportPrinter = Worksheets("Control").Range("UsePrinter").Value
DefaultPrinter = Worksheets("Control").Range("DefaultPrinter").Value
'
'Set printer to use for reports
    Application.ActivePrinter = ReportPrinter
'
'Reset printer to default printer once job is complete
    Application.ActivePrinter = DefaultPrinter

With this code in place, the workbook becomes much more portable. Use the macro recorder to generate a list of available printers and then copy the names into the variable-holding worksheet cells. No changes to the actual VBA code are then necessary to move or copy the file around.

Reply | Quote

Thanks for our reply, but still I have the problem of differences in the name (portnr) of the same printer between workstations.
I want to make one macro that can be used on different workstations. If I have to ‘test” on each workstation how the printer exactly is called by recording a macro, it takes too much time. If I use the printername without “on ne00” the macro doesn’t work.

Have any idea’s

Reply | Quote

If the workstations are on different segments of the network, then the printer will be addressed differently from each of them. It is possible to use the WinAPI to get a list of device names available to the workstation, including the port, but it is non-trivial code in Office 2000. here is some information that applies to Access specifically but is generally applicable to VBA, since PrtDevNames is part of the Windows API.

Reply | Quote

If the workstations are on different segments of the network, then the printer will be addressed differently from each of them. It is possible to use the WinAPI to get a list of device names available to the workstation, including the port, but it is non-trivial code in Office 2000. here is some information that applies to Access specifically but is generally applicable to VBA, since PrtDevNames is part of the Windows API.

Reply | Quote

This (rather kludgy) code gets the list of all available printers into an array called sPrinterlist:

Option Explicit

Dim sPrinterlist() As String
Dim iCount As Integer
Sub GetPrinterlist()
    Dim sOldprinter As String
    Dim sPrev As String
    Dim sNew As String
    Dim iLoop As Integer
    iCount = 0
    sPrev = ""
    sOldprinter = Application.ActivePrinter
    ReDim sPrinterlist(2)
    Do While (sPrev = "" Or sPrev  sNew)
        sPrev = sNew
        SendKeys "{home}{down " & iCount & "}~"
        Application.Dialogs(xlDialogPrinterSetup).Show
        sPrinterlist(iCount) = Application.ActivePrinter
        sNew = sPrinterlist(iCount)
        iCount = iCount + 1
        ReDim Preserve sPrinterlist(iCount)
    Loop
    iCount = iCount - 1
    Application.ActivePrinter = sOldprinter
    For iLoop = 1 To iCount
        MsgBox sPrinterlist(iLoop)
    Next
End Sub

Reply | Quote

Thanks!
Eventually I solved it according this code:

Option Explicit

Public sOldprinter As String
Public sPrev As String
Public sNew As String
Public iLoop As Integer

Public KeuzePrinter As String
Public Sheet1Printer As String
Public Sheet2totEindPrinter As String

Public sPrinterlist() As String
Public iCount As Integer
Public i As Integer

Option Explicit
Sub LongDocuments()

iCount = 0
sPrev = “”
sOldprinter = Application.ActivePrinter
ReDim sPrinterlist(0)

Do While (sPrev = “” Or sPrev sNew)
sPrev = sNew
Application.SendKeys “{home}{down ” & iCount & “}~”
Application.Dialogs(xlDialogPrinterSetup).Show
sPrinterlist(iCount) = Application.ActivePrinter
sNew = sPrinterlist(iCount)
iCount = iCount + 1
ReDim Preserve sPrinterlist(iCount)
Loop
iCount = iCount – 1

Application.ActivePrinter = sOldprinter

For i = 1 To Sheets.Count
Sheets(i).Activate

If i = 1 Then
For iLoop = 0 To iCount
Sheet1Printer = Trim(“adm01HP LaserJet 4050 Series PS Excel Bak3”)
If Sheet1Printer = Trim(Left(sPrinterlist(iLoop), 45)) Then
ActivePrinter = sPrinterlist(iLoop)
End If
Next
ActiveWindow.SelectedSheets.PrintOut
ElseIf i > 1 Then
For iLoop = 0 To iCount
Sheet2totEindPrinter = Trim(“adm01HP LaserJet 4050 Series PCL bak2”)
If Sheet2totEindPrinter = Trim(Left(sPrinterlist(iLoop), 40)) Then
ActivePrinter = sPrinterlist(iLoop)
End If
Next
ActiveWindow.SelectedSheets.PrintOut
End If
Next i

Application.ActivePrinter = sOldprinter

End Sub

Reply | Quote

Thanks!
Eventually I solved it according this code:

Option Explicit

Public sOldprinter As String
Public sPrev As String
Public sNew As String
Public iLoop As Integer

Public KeuzePrinter As String
Public Sheet1Printer As String
Public Sheet2totEindPrinter As String

Public sPrinterlist() As String
Public iCount As Integer
Public i As Integer

Option Explicit
Sub LongDocuments()

iCount = 0
sPrev = “”
sOldprinter = Application.ActivePrinter
ReDim sPrinterlist(0)

Do While (sPrev = “” Or sPrev sNew)
sPrev = sNew
Application.SendKeys “{home}{down ” & iCount & “}~”
Application.Dialogs(xlDialogPrinterSetup).Show
sPrinterlist(iCount) = Application.ActivePrinter
sNew = sPrinterlist(iCount)
iCount = iCount + 1
ReDim Preserve sPrinterlist(iCount)
Loop
iCount = iCount – 1

Application.ActivePrinter = sOldprinter

For i = 1 To Sheets.Count
Sheets(i).Activate

If i = 1 Then
For iLoop = 0 To iCount
Sheet1Printer = Trim(“adm01HP LaserJet 4050 Series PS Excel Bak3”)
If Sheet1Printer = Trim(Left(sPrinterlist(iLoop), 45)) Then
ActivePrinter = sPrinterlist(iLoop)
End If
Next
ActiveWindow.SelectedSheets.PrintOut
ElseIf i > 1 Then
For iLoop = 0 To iCount
Sheet2totEindPrinter = Trim(“adm01HP LaserJet 4050 Series PCL bak2”)
If Sheet2totEindPrinter = Trim(Left(sPrinterlist(iLoop), 40)) Then
ActivePrinter = sPrinterlist(iLoop)
End If
Next
ActiveWindow.SelectedSheets.PrintOut
End If
Next i

Application.ActivePrinter = sOldprinter

End Sub

Reply | Quote

This (rather kludgy) code gets the list of all available printers into an array called sPrinterlist:

Option Explicit

Dim sPrinterlist() As String
Dim iCount As Integer
Sub GetPrinterlist()
    Dim sOldprinter As String
    Dim sPrev As String
    Dim sNew As String
    Dim iLoop As Integer
    iCount = 0
    sPrev = ""
    sOldprinter = Application.ActivePrinter
    ReDim sPrinterlist(2)
    Do While (sPrev = "" Or sPrev  sNew)
        sPrev = sNew
        SendKeys "{home}{down " & iCount & "}~"
        Application.Dialogs(xlDialogPrinterSetup).Show
        sPrinterlist(iCount) = Application.ActivePrinter
        sNew = sPrinterlist(iCount)
        iCount = iCount + 1
        ReDim Preserve sPrinterlist(iCount)
    Loop
    iCount = iCount - 1
    Application.ActivePrinter = sOldprinter
    For iLoop = 1 To iCount
        MsgBox sPrinterlist(iLoop)
    Next
End Sub

Reply | Quote

Here’s how I set up a worksheet to handle switching to the non-default printer. First, in a blank Excel workbook I started the macro recorder:

Sub Macro1()
'
' Macro1 Macro
' Using File | Print | select printer | Close to read each
' available printer on the computer.
'
    Application.ActivePrinter = "IBM InfoPrint 40 on Ne00:"
    Application.ActivePrinter = "NetworkServerLexmark Optra C710 on Ne02:"
    Application.ActivePrinter = "NetworkServerHP DeskJet 1600C on Ne01:"
    Application.ActivePrinter = "HP LaserJet 1100 (MS) on LPT1:"
End Sub

Not only does this give you the exact names of each printer on the system but also the full command you can copy and paste into your code.

However, to create a portable worksheet I turn the printer names into variables. On a ‘control’ sheet or in some other convenient location I set up named ranges to contain the desired printer and default printer names. Then the following code is used to specify which printer to use:

ReportPrinter = Worksheets("Control").Range("UsePrinter").Value
DefaultPrinter = Worksheets("Control").Range("DefaultPrinter").Value
'
'Set printer to use for reports
    Application.ActivePrinter = ReportPrinter
'
'Reset printer to default printer once job is complete
    Application.ActivePrinter = DefaultPrinter

With this code in place, the workbook becomes much more portable. Use the macro recorder to generate a list of available printers and then copy the names into the variable-holding worksheet cells. No changes to the actual VBA code are then necessary to move or copy the file around.

Reply | Quote