+ Reply to Thread
Results 1 to 2 of 2

Troubleshooting VBA code using BarTender to print labels

Hybrid View

  1. #1
    Registered User
    Join Date
    05-07-2021
    Location
    STL USA
    MS-Off Ver
    2014
    Posts
    14

    Question Troubleshooting VBA code using BarTender to print labels

    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!

  2. #2
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    2,984

    Re: Troubleshooting VBA code using BarTender to print labels

    try

    btFormat.PrintOut(False, False)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Code Troubleshooting
    By Keegan1116 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2024, 08:18 AM
  2. QR Code Labels - print out from spreadsheet on button press
    By scottsinclair in forum Excel General
    Replies: 1
    Last Post: 12-08-2021, 06:53 PM
  3. Open shell "Bartender", Print labels, then close
    By mk3ll00 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2021, 06:43 PM
  4. [SOLVED] Deleting Duplicate Row code troubleshooting
    By ds16 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-07-2012, 04:31 PM
  5. Troubleshooting: Code works in some instances, but not all....
    By JP777 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-26-2012, 11:51 PM
  6. Troubleshooting Print Preview
    By ddodson in forum Excel General
    Replies: 0
    Last Post: 02-01-2011, 11:42 AM
  7. Troubleshooting code for export to text file.
    By gshock in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-03-2008, 07:44 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1