USB name changing

J

jimmywanna

Board Regular
Joined
Mar 18, 2002
Messages
182
Hello my peers,

I wondered if anyone could help me with a probelem. I have a selection of workbooks which print worksheets and labels for chemotherapy drugs. I have made a macro that prints to two different printers, here it is:

Sub LABELPRINT()
' BOTH Macro
' Macro recorded 20/04/2006 by default user
Sheets("LAYOUT").Select
Application.ActivePrinter = "Eltron TLP2742 on LPT1:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Eltron TLP2742 on LPT1:", Collate:=True
Sheets("Sheet3 (2)").Select
Application.ActivePrinter = "HP LaserJet 2200 Series PCL on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"HP LaserJet 2200 Series PCL on Ne02:", Collate:=True
ActiveWorkbook.Close SaveChanges:=False
End Sub

It has worked well for years, but for some reason The "name" of the printer is changing periodically. Last week the section : Application.ActivePrinter = "HP LaserJet 2200 Series PCL on Ne02:" worked with the name Application.ActivePrinter = "HP LaserJet 2200 Series PCL on Ne00".

Firstly would anyone know why this would happen, secondly is there any way i can change the macro so that it finds the name of the printer first, then tells the sheet to print out on it.

Currently I am having to change it manually on around 70 workbooks.

Thank you in advance for any help.

Jamie
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Here is some basic code
Code: 
'=============================================================================
'- PRINT TO PRINTER WITH UNKNOWN Ne:
'- Try to set ActivePrinter and trap errors
'- Brian Baulsom February 2003
'=============================================================================
Sub PRINT_UNKNOWN_Ne()
    Dim PrinterName As String
    Dim PortNumber As Integer
    Dim PrinterPort As String
    Dim PrinterFullName As String
    Dim PrinterFound As Boolean
    '-------------------------------------------------------------------------
    PrinterName = "Brother HL-1440 series"
    PrinterFound = False
    On Error Resume Next       ' SET ERROR TRAPPING
    '-------------------------------------------------------------------------
    '- LOOP Ne: NUMBERS
    For PortNumber = 0 To 12
        PrinterPort = "Ne" & Format(PortNumber, "00") & ":"
        PrinterFullName = PrinterName & PrinterNumber & " on " & PrinterPort
        '---------------------------------------------------------------------
        '- TRY TO SET ACTIVEPRINTER
        Application.ActivePrinter = PrinterFullName
        '---------------------------------------------------------------------
        '- IF ERROR TRY NEXT PRINTER
        If Err.Number = 0 Then
            ws.PrintOut            ' PRINT SHEET
            PrinterFound = True
            Exit For
        Else
            Err.Clear   ' clear for next error
        End If
        '---------------------------------------------------------------------
    Next PortNumber
    '-------------------------------------------------------------------------
    '- RESULT
    If PrinterFound Then
        MsgBox ("Printed on printer" & vbCr & PrinterFullName)
    Else
        MsgBox (PrinterName & vbCr & "Printer not found")
    End If
    '------------------------------------------------------------------------
End Sub
'=============================================================================
 
Upvote 0
My attention has been drawn to a coding error in my other message.
ws.Printout should read Activesheet.Printout
sorry for any problems. This is one of the difficulties when trapping errors. Others do not show.
Code: 
'=============================================================================
'- PRINT TO PRINTER WITH UNKNOWN Ne:
'- Try to set ActivePrinter and trap errors
'- Brian Baulsom February 2003
'=============================================================================
Sub PRINT_UNKNOWN_Ne()
    Dim PrinterName As String
    Dim PortNumber As Integer
    Dim PrinterPort As String
    Dim PrinterFullName As String
    Dim PrinterFound As Boolean
    '-------------------------------------------------------------------------
    PrinterName = "Brother HL-1440"
    PrinterFound = False
    On Error Resume Next       ' SET ERROR TRAPPING
 
    '-------------------------------------------------------------------------
    '- LOOP Ne: NUMBERS
    For PortNumber = 0 To 12
        PrinterPort = "Ne" & Format(PortNumber, "00") & ":"
        PrinterFullName = PrinterName & PrinterNumber & " on " & PrinterPort
        '---------------------------------------------------------------------
        '- TRY TO SET ACTIVEPRINTER
        Application.ActivePrinter = PrinterFullName
        '---------------------------------------------------------------------
        '- IF ERROR TRY NEXT PRINTER
        If Err.Number = 0 Then
            '-----------------------------------------------------------------
            'ALTERNATIVE LINES OF CODE TO SAVE PAPER WHEN TESTING
            'ActiveSheet.PrintOut            ' PRINT SHEET
            ActiveSheet.PrintPreview       ' PRINT PREVIEW TO TEST
            '-----------------------------------------------------------------
            PrinterFound = True
            Exit For
        Else
            Err.Clear   ' clear for next error
        End If
        '---------------------------------------------------------------------
    Next PortNumber
    '-------------------------------------------------------------------------
    '- RESULT
    If PrinterFound Then
        MsgBox ("Printed on printer" & vbCr & PrinterFullName)
    Else
        MsgBox (PrinterName & vbCr & "Printer not found")
    End If
    '------------------------------------------------------------------------
End Sub
'=============================================================================
 
Upvote 0
Hi Brian,

I know it has been a couple years since you posted your beautiful code, but I just wanted to tell you that your code worked perfectly (after I added a declaration for PrinterNumber as string), and it has greatly improved the applicability of my own code.

In fact, I just joined the website to thank you, although I had used this great website multiple times for my VBA studying.

Thanks again for sharing!

BrianB said:
My attention has been drawn to a coding error in my other message.
ws.Printout should read Activesheet.Printout
sorry for any problems. This is one of the difficulties when trapping errors. Others do not show.
Code: 
'=============================================================================
'- PRINT TO PRINTER WITH UNKNOWN Ne:
'- Try to set ActivePrinter and trap errors
'- Brian Baulsom February 2003
'=============================================================================
Sub PRINT_UNKNOWN_Ne()
    Dim PrinterName As String
    Dim PortNumber As Integer
    Dim PrinterPort As String
    Dim PrinterFullName As String
    Dim PrinterFound As Boolean
    '-------------------------------------------------------------------------
    PrinterName = "Brother HL-1440"
    PrinterFound = False
    On Error Resume Next       ' SET ERROR TRAPPING
 
    '-------------------------------------------------------------------------
    '- LOOP Ne: NUMBERS
    For PortNumber = 0 To 12
        PrinterPort = "Ne" & Format(PortNumber, "00") & ":"
        PrinterFullName = PrinterName & PrinterNumber & " on " & PrinterPort
        '---------------------------------------------------------------------
        '- TRY TO SET ACTIVEPRINTER
        Application.ActivePrinter = PrinterFullName
        '---------------------------------------------------------------------
        '- IF ERROR TRY NEXT PRINTER
        If Err.Number = 0 Then
            '-----------------------------------------------------------------
            'ALTERNATIVE LINES OF CODE TO SAVE PAPER WHEN TESTING
            'ActiveSheet.PrintOut            ' PRINT SHEET
            ActiveSheet.PrintPreview       ' PRINT PREVIEW TO TEST
            '-----------------------------------------------------------------
            PrinterFound = True
            Exit For
        Else
            Err.Clear   ' clear for next error
        End If
        '---------------------------------------------------------------------
    Next PortNumber
    '-------------------------------------------------------------------------
    '- RESULT
    If PrinterFound Then
        MsgBox ("Printed on printer" & vbCr & PrinterFullName)
    Else
        MsgBox (PrinterName & vbCr & "Printer not found")
    End If
    '------------------------------------------------------------------------
End Sub
'=============================================================================
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

JohnZ1156
  • Solved
VBA Macro to print table rows from a start date to an end date.
Replies
4
Views
273
Joe4
Joe4
R
  • Solved
Excel 365 VBA - Printer Ports not Static
Replies
4
Views
348
John_w
J
JohnZ1156
  • Solved
Print only visible Filtered Rows from Excel Table
Replies
2
Views
656
JohnZ1156
JohnZ1156
C
  • Solved
Message box over print preview
2
Replies
14
Views
2K
Jaafar Tribak
Jaafar Tribak
JohnZ1156
  • Question
MS Excel VBA code to switch printer to manual Feed
Replies
8
Views
2K
JohnZ1156
JohnZ1156

Forum statistics

Threads
1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top