Printer and Printer ports

[wbstadeli]

Hi guys,

I have a label printing to a label printer using a macro, however I dont know how to properly set this up for printing from multiple computers. Here is the scenario: 4 computers all linked to this "Network" Printer/label writer. I have this printer named different on each computer (DYMO LabelWriter 450 Twin Turbo - Shop1, DYMO LabelWriter 450 Twin Turbo - Shop2, DYMO LabelWriter 450 Twin Turbo - Shop3, and DYMO LabelWriter 450 Twin Turbo - Server) so that i can use the a specific defined paper size from each computer when this macro is ran. The problem is, the Ne number keeps changing on me occasionally and then the code won't work. What i need is the label to print, regardless of the Ne number. Is there a loop function i can add in to my current code to make this work? the Ne number has always been less then 10 so maybe loop thru a Ne1-10?? Any help would be appreciated!!

Sub Printlabelwithdymo()
On Error Resume Next
shop1
server
shop2
shop3
End Sub
Sub shop1()
Dim Defaultprinter As String
Defaultprinter = Application.ActivePrinter
Application.ActivePrinter = "DYMO LabelWriter 450 Twin Turbo - Shop1 on Ne06:"
    With Worksheets("Label")
        With .PageSetup
            .PaperSize = 134
            .Orientation = xlLandscape
        End With
        Worksheets("Label").PrintOut preview:=False, From:=1, To:=1
        Application.ActivePrinter = Defaultprinter
        Worksheets("Label").PageSetup.PaperSize = xlPaperLetter
    End With
End Sub
Sub server()
Dim Defaultprinter As String
Defaultprinter = Application.ActivePrinter
Application.ActivePrinter = "DYMO LabelWriter 450 Twin Turbo - Server on Ne05:"
    With Worksheets("Label")
       With .PageSetup
            .PaperSize = 154
            .Orientation = xlLandscape
        End With
        Worksheets("Label").PrintOut preview:=False, From:=1, To:=1
        Application.ActivePrinter = Defaultprinter
        Worksheets("Label").PageSetup.PaperSize = xlPaperLetter
    End With
End Sub
Sub shop2()
Dim Defaultprinter As String
Defaultprinter = Application.ActivePrinter
Application.ActivePrinter = "DYMO LabelWriter 450 Twin Turbo - Shop2 on Ne05:"
    With Worksheets("Label")
        With .PageSetup
            .PaperSize = 152
            .Orientation = xlLandscape
        End With
        Worksheets("Label").PrintOut preview:=False, From:=1, To:=1
        Application.ActivePrinter = Defaultprinter
        Worksheets("Label").PageSetup.PaperSize = xlPaperLetter
    End With
End Sub
Sub shop3()
Dim Defaultprinter As String
Defaultprinter = Application.ActivePrinter
Application.ActivePrinter = "DYMO LabelWriter 450 Twin Turbo - Shop3 on Ne07:"
    With Worksheets("Label")
        With .PageSetup
            .PaperSize = 133
            .Orientation = xlLandscape
        End With
        Worksheets("Label").PrintOut preview:=False, From:=1, To:=1
        Application.ActivePrinter = Defaultprinter
        Worksheets("Label").PageSetup.PaperSize = xlPaperLetter
    End With
End Sub

 

[John_w]

Insert this function (which I've changed slightly from the original author's version) in your module:

'Written: November 28, 2009
'Author:  Leith Ross
'Summary: Finds a printer by name and returns the printer name and port number.

Function FindPrinter(ByVal PrinterName As String) As String

    'This works with Windows 2000 and up
    
    Dim Arr As Variant
    Dim Device As Variant
    Dim Devices As Variant
    Dim Printer As String
    Dim RegObj As Object
    Dim RegValue As String
    Const HKEY_CURRENT_USER = &H80000001
    
    Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    RegObj.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr
    
    For Each Device In Devices
        RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
        Printer = Device & " on " & Split(RegValue, ",")(1)
        'If InStr(1, Printer, PrinterName, vbTextCompare) > 0 Then  'original code
        If UCase(Device) = UCase(PrinterName) Then
            FindPrinter = Printer
            Exit Function
        End If
    Next
      
End Function

Call it like this:

    Dim printer As String
    printer = FindPrinter("DYMO LabelWriter 450 Twin Turbo - Shop1")
    If printer <> vbNullString Then
        Application.ActivePrinter = printer
    Else
        MsgBox "Printer not found"
    End If

 

[wbstadeli]

Thank you very much John, this works great!