Bartender: Save Printed Data to Excel
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-
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).RowSet 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.RowBasically 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 -
Thanks John!
It works
0
請登入寫評論。
評論
2 條評論