Hello all, I am having trouble using VBA to print labels through BarTender. I am specifically having issues finding a way to get BarTender to accept a printing function.
Sub PrintInventoryLabels()
Dim btApp As Object
Dim btFormat As Object
Dim wsTable As Worksheet
Dim tbl As ListObject
Dim partNumber As String
Dim lotNumber As String
Dim printQty As Long
Dim filePath As String
Dim lastRow As Integer
Dim currentRow As Integer
Dim userResponse As Integer
'start bartender
Set btApp = CreateObject("BarTender.Application")
Set wsTable = ThisWorkbook.Sheets("Table") ' Sheet where the table "PrintTable" is located
' set file path
filePath = "C:\Users\xxxxxxxxxxxxxxx\OneDrive - xxxxxxxxxxxxxxxxx"
'get object
Set tbl = wsTable.ListObjects("PrintTable")
lastRow = tbl.ListRows.Count
currentRow = 1 ' Start from the first row
' Looping!
Do While currentRow <= lastRow
' Get the values...
partNumber = CStr(tbl.DataBodyRange(currentRow, 2).Value) ' Part Number
lotNumber = CStr(tbl.DataBodyRange(currentRow, 3).Value) ' Lot Num
If IsNumeric(tbl.DataBodyRange(currentRow, 4).Value) Then
printQty = CLng(tbl.DataBodyRange(currentRow, 4).Value) ' Converrt 2 Long
Else
MsgBox "Invalid print quantity at row " & currentRow & ". Skipping this row.", vbExclamation
GoTo SkipRow
End If
' Debuggin
Debug.Print "Row " & currentRow & ": PartNumber = " & partNumber & ", LotNumber = " & lotNumber & ", PrintQty = " & printQty
' Open bartender format
Set btFormat = btApp.Formats.Open(filePath, False, "")
' set btender fieldds
btFormat.SetNamedSubStringValue "PartNumber", partNumber
btFormat.SetNamedSubStringValue "LotNumber", lotNumber
' Set the quantity of identical labels?
btFormat.IdenticalCopiesOfLabel = printQty
' Print the labels??
On Error Resume Next
btFormat.PrintOut
If Err.Number <> 0 Then
MsgBox "Error printing labels: " & Err.Description, vbCritical
End If
On Error GoTo 0
' Close format
btFormat.Close (False)
' Ask the user 4 next step
userResponse = MsgBox("Row " & currentRow & " printed." & vbCrLf & _
"Choose an option:" & vbCrLf & _
"1: Continue to next row" & vbCrLf & _
"2: Reprint last job" & vbCrLf & _
"3: Cancel", vbQuestion + vbAbortRetryIgnore, "Next Step")
Select Case userResponse
Case vbAbort ' Cancel
MsgBox "Operation cancelled by user."
Exit Sub
Case vbRetry ' Reprint the current row
' No action needed, will reprint in the next loop iteration
Case vbIgnore ' Continue to the next row
currentRow = currentRow + 1
End Select
SkipRow:
Loop
' Quit BarTender application
btApp.Quit
' Cleanup
Set btFormat = Nothing
Set btApp = Nothing
MsgBox "All labels have been processed."
End Sub
I am currently getting a msgbox error: "Error printing labels: Argument not optional".
I was getting a syntax error no matter how I formatted the btFormat.Print() OR the btFormat.PrintOut fuctions. I went down the rabbit hole a bit and now I do not know where else to go.
Thank you to anyone able to assist or attempt to assist. It is much appreciated!
Bookmarks