Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Breda, Netherlands
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel: Print to specific printer (Excel 2000)

    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<number>. e.g. "ADM01HP LaserJet 4M on Ne04:"

    The <number> 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)?

  2. #2
    Lounger
    Join Date
    Aug 2002
    Location
    Sierra Madre, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel: Print to specific printer (Excel 2000)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel: Print to specific printer (Excel 2000)

    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

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel: Print to specific printer (Excel 2000)

    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:<pre>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</pre>

    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:<pre>ReportPrinter = Worksheets("Control").Range("UsePrinter").Value
    DefaultPrinter = Worksheets("Control").Range("DefaultPrinter").Valu e
    '
    'Set printer to use for reports
    Application.ActivePrinter = ReportPrinter
    '
    'Reset printer to default printer once job is complete
    Application.ActivePrinter = DefaultPrinter</pre>

    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.

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    Breda, Netherlands
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel: Print to specific printer (Excel 2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel: Print to specific printer (Excel 2000)

    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.
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel: Print to specific printer (Excel 2000)

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

    <pre>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
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    New Lounger
    Join Date
    Jan 2001
    Location
    Breda, Netherlands
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel: Print to specific printer (Excel 2000)

    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

Posting Permissions

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