Bartender: Save Printed Data to Excel Follow

0
Avatar
Dang Nguyen

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

2 comments

0
Avatar
John Kral
Comment actions Permalink

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
Avatar
Dang Nguyen
Comment actions Permalink

Thanks John!

It works

 

Please sign in to leave a comment.