Bartender: Save Printed Data to Excel

Dang Nguyen

• 25. Juni 2021 09:47
• Bearbeitet

I have an excel file named Data.xlsx and a sheet named Data. Besides, I also have a template for printing 4 text linked to 1 barcode. 

The procedure: 

1. Scan 4 text value

2. Print label contained 4 text value and the QR barcode (4 text value contained inside the barcode)

3. VB Script capture the QR barcode value and send to Data.xlsx (column A)

4. VB Script capture the print time and send to Data.xlsx (column B)

I can do this with a litte trick using ////// Code 002 ////// since I have added a counta+1 for the columb A to cells(1,3) in the excel file. Then the next data will be added to the next row.

My question is why I can not do the same with ////// Code 001 ////// using the line as below to add data to the next row in excel.

masterNextRow = objWorkbook.Worksheets("Data").Range("A" & objWorkbook.Worksheets("Data").rows.count).End(xlUp).Offset(1).Row

It always tell me an error is OnIdenticalCopies (Line 6): Unknown runtime error

Please HELP!

 

 

////// Code 001 //////

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Dang Nguyen\Desktop\Database\Data.xlsx")

Dim masterNextRow

masterNextRow = objWorkbook.Worksheets("Data").Range("A" & objWorkbook.Worksheets("Data").rows.count).End(xlUp).Offset(1).Row

objWorkbook.Worksheets("Data").Cells(masterNextRow,1).value = Format.NamedSubStrings("barcode").Value
objWorkbook.worksheets("Data").Cells(masterNextRow,2).value = now()

objWorkbook.save

objWorkbook.close

objExcel.quit

////// Code 002 //////

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Dang Nguyen\Desktop\Database\Data.xlsx")

Dim masterNextRow

masterNextRow = objWorkbook.Worksheets("Data").Cells(1,3).value

objWorkbook.Worksheets("Data").Cells(masterNextRow,1).value = Format.NamedSubStrings("barcode").Value
objWorkbook.worksheets("Data").Cells(masterNextRow,2).value = now()

objWorkbook.save

objWorkbook.close

objExcel.quit

0

• 

  John Kral

  ★ BarTender Hero ★

  • 29. Juni 2021 19:36

  This is likely due to a missing reference file for either Excel or VBA in general. Can you try to define the xlUp as follows?

  xlUp = -4162

  Let me know if that solves the problem. If not, we may need to break out your line there a bit more to see if there is another issue that could be causing it.

  masterNextRow = objWorkbook.Worksheets("Data").Range("A" & objWorkbook.Worksheets("Data").rows.count).End(xlUp).Offset(1).Row

  Set objSheet = objWorkbook.Worksheets("Data")

  LastRow = objSheet.Rows.Count 'note this is a static number so you can probably just input "A1048576"

  xlUp = -4162

  Set lastdatacell = objSheet.Range("A" & LastRow).End(xlUp)
  Set offsetcell = objSheet.Range("A" & LastRow).End(xlUp).Offset(1)
  masterNextRow = offsetcell.Row

  Basically we are seeing if it is only the xlUp that's the issue and if not, where else it might exist. My guess is that defining the xlUp will resolve your issue - though I cannot easily validate/duplicate with my setup.

  0
• 

  Dang Nguyen

  ★ BarTender Hero ★

  • 8. Juli 2021 15:04

  Thanks John!

  It works

   

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.