A couple of weeks ago, I posted some code to print labels on a Dymo LabelWriter 450. I wanted to post the finished code because it has a few more tricks in it.
Function PrintBoardFileLabel(ws As Worksheet) As Boolean
Dim bReturn As Boolean
Dim vaPrinters As Variant
Dim i As Long
Const sLABELFILE As String = "C:\BoardFile.Label"
Const sMSGNOFILE As String = "Label file not found at "
Const sMSGNODYMO As String = "Dymo label printer not found."
Const sSOURCE As String = "PrintBoardFileLabel()"
On Error GoTo ErrorHandler
bReturn = True
If Len(Dir(sLABELFILE)) > 0 Then
If mdyAddin Is Nothing Or mdyLabel Is Nothing Then
CreateDymoObjects
End If
If Not mdyAddin Is Nothing Or Not mdyLabel Is Nothing Then
vaPrinters = Split(mdyAddin.GetDymoPrinters, "|")
For i = LBound(vaPrinters) To UBound(vaPrinters)
If mdyAddin.IsPrinterOnline(vaPrinters(i)) Then
mdyAddin.SelectPrinter vaPrinters(i)
Exit For
End If
Next i
mdyAddin.Open sLABELFILE
mdyLabel.SetField "Text", ws.Range("rngComp1Serial").Value & " " & ws.Range("rngProdOrder").Value & _
vbNewLine & StripItem(ws.Range("rngCustomer").Value) & " " & ws.Range("rngPO").Value
mdyAddin.Print2 1, True, 1
Else
Err.Raise glHANDLED_ERROR, sSOURCE, sMSGNODYMO
End If
Else
Err.Raise glHANDLED_ERROR, sSOURCE, sMSGNOFILE & sLABELFILE
End If
ErrorExit:
On Error Resume Next
PrintBoardFileLabel = bReturn
Exit Function
ErrorHandler:
bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function
The procedure is now a function that returns a Boolean because I use the error logging scheme described in PED. But if you ignore all that stuff, there are a couple of changes worth noting.
First, I made the Dymo objects module level variables by putting this at the top of the module
Private mdyAddin As Object
Private mdyLabel As Object
and moved the creation of these variables into a separate procedure
Private Sub CreateDymoObjects()
Set mdyAddin = CreateObject(“Dymo.DymoAddin”)
Set mdyLabel = CreateObject(“Dymo.DymoLabels”)
End Sub
In addition to converting these to late-bound (using CreateObject and the Object variable type instead of setting a reference) so that it works well on different PCs, I needed to keep these objects live through the whole session. In the cases where multiple labels would be printed, I didn’t want to incur the overhead of creating and destroying the Dymo objects each time. The module level variables stay in scope until the add-in is closed and I check in my code whether they exist yet.
Another change I made was to find the proper printer. In my first iteration, I had one printer. So I used the GetDymoPrinters method with impunity. When I connected a second printer for testing, this no longer worked and I needed something more robust. The GetDymoPrinters returns a pipe (|) delimited string. This code
vaPrinters = Split(mdyAddin.GetDymoPrinters, "|")
For i = LBound(vaPrinters) To UBound(vaPrinters)
If mdyAddin.IsPrinterOnline(vaPrinters(i)) Then
mdyAddin.SelectPrinter vaPrinters(i)
Exit For
End If
Next i
splits the returned printer names into a Variant array. I then loop through that array and check the IsPrinterOnline property. When I find one that returns True, I use SelectPrinter to make it the “one” and exit the loop.
Almost all of the methods in the Dymo library return True or False indicating success or failure. I should have, but haven’t, written code like this
If mdyAddin.Open(sLABELFILE) Then
That would prevent errors if someone moves or renames the label file. Always build in some potential errors for job security (just kidding, don’t do that). Someday when I have some more time, I’ll tighten up the code further. But for now, it will have to do.
Hello,
I have been messing with the code for sometime now and I can’t get the printer to print text. It just prints blank labels?
I have the same setup as you.
Thanks.