Write Serial Number Back To Xls File
Hello,
i have a problem using Bartender 10 Professional.
We are using a xls Datasource, with a item list, including specific Item information and want to write back the last used serial number back after printing, so the next print job can continue from that value.
Datasource
itemlist.xls
Item DescriptionA DescriptionB Serialnumber
111 DesA111 DescB111 1
222 DesA222 DescB222 1
333 DesA333 DescB333 1
Printing 1 Label for item 111 and 3 for item 222 should result in
Item DescriptionA DescriptionB Serialnumber
111 DesA111 DescB111 2
222 DesA222 DescB222 4
333 DesA333 DescB333 1
The Option to write back the Value in the serialization Dialog can´t be selected if a xls file is used as Datasource and I´m not familiar to VB Scripting.
An example file or VB Code would be very helpfull.
Thanks for any kind of Support
-
Hello,
I have tried to fix it on my own and found some useful Information in some other posts.
The example from the following post http://seagullscientific.invisionzone.com/index.php?/topic/1049-saving-serialization-number-to-start-next-print/ was taken and modified to fit my needs.
Since I use a German Version of Bartender I can only imagine how Buttons and other things are called in the English Version. Please apologize me, if there is something not obvious. Additionally I attached an example file.
What to do:
1. Included a field serial in the xls file, as value I entered 1 which would be the next number to be printed
2. Added the Serial field in Bartender linked to the serial field of the xls file
3. Modified the Properties for the Serial field and activated Serialization, you have to check Serialize copies under the Settings field of Serialization.
Otherwise, the serial number for one item would be the same on every label If printed in one print job as copies.
4. Add the VBScript for OnIdenticalCopies event (Add a new text item, modify it and change the type to Visual Basic Scripting - Event Scripts)
5. Use the below code for the Script, modify it for your needs and to fit your environment.
--- OnIdenticalCopies event - Code Start ---
'Define the object names.
dim objConn
dim strConn
dim rs
varSerial = 0
'Define the database connection parameter constants.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
'Connect to the Excel file.
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Seagull\items.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
'Create a record set of one record which is the current record being printed.
Set rs = CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM [Sheet1$]", objConn, adOpenStatic, adLockOptimistic, adCmdText
rs.Find "Item = '" & Field("Sheet1$.Item") & "'"
varSerial = rs.Fields("Serial")
'Increase the Serial by one for each printed label
rs.Fields("Serial") = varSerial + 1
rs.Update--- Code End---
6. Open the Database tool and modify the Database Options of the xls file, change Jet OLEDB:Database Locking Mode to 0
7. Be happy
It is possible to print multiple Items with a different Quantity of labels at one time.
If there is a better way to do it, please give me a info.
Greetings
Michael
0 -
Until we support such a feature directly you choice is to either implement a VB script like you have done above, or control a BarTender process entirely via automation. For the latter, that means creating your own stand-alone application that handles the database transaction on one side and controlling the BarTender process on the other. Note that BarTender automation requires the Automation edition of BarTender or higher.
The below white paper provide an introduction to this subject:
0 -
I'm running into the same issue. I will attempt to use a similar VB script like what judge posted above for the time being.
I am currently evaluating the Bartender software to replace Teklynx Labelview software in all of our companies buildings. The Labelview software has supported incrementing the value in an external file for 15+ years. For our automotive industry use, this feature is a must-have. If our customer ever receives two boxes with the same serial number, it can result in rejections and has huge financial ramifications. To prevent this we have used a simple external txt file, which is incremented by one by the labelview software for each label printed. That text file is then referenced by all of our customer label files, so no two boxes to leave the building are ever the same, or at least they are years upon years apart, by the time it resets and wraps around again. If I can get this to work, we will almost certainly switch all our users to Bartender, as this has been our only sticking point.
0 -
Ok, I got the code to work, basically the same code as above with the Item line commented out, where it was incrementing my xls file serial number by oine on each print. Then I setup my label to pull that value from the xls file for the barcodes, and it said it could not update because the file was read-only, so after a little digging I found the jet locking mode option as judge said and set it to zero, saved it reopened, and I'm still getting the read-only issue. Can anyone give me a hand in figuring out why? I know I'm close.
0 -
Here is some example code that you would normally use on the OnNewRecord event:
'Define the object names. dim objConn dim strConn dim rs 'Define the database connection parameter constants. Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adCmdText = &H0001 'Connect to the Excel file. Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&Format.Directory&"\Test.xls;Extended Properties=""Excel 12.0;HDR=YES;""" 'Create a record set of one record which is the current record being printed. Set rs = CreateObject("ADODB.Recordset") rs.open "SELECT * FROM [Sheet1$]", objConn, adOpenStatic, adLockOptimistic, adCmdText rs.Find "Product = '" & Field("Sheet1$.Product") & "'" 'Set the "Printed" field of the record to be "YES" thus flagging it as having been printed. rs.Fields("Printed") = "YES" rs.UpdateIf you still have problems open up the database connection properties (Data Link Properties) as used by BarTender for the main database connection and for "Extended Properties" remove the IMEX=1 reference by editing the value.
0 -
Here is some example code that you would normally use on the OnNewRecord event:
'Define the object names. dim objConn dim strConn dim rs 'Define the database connection parameter constants. Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adCmdText = &H0001 'Connect to the Excel file. Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&Format.Directory&"\Test.xls;Extended Properties=""Excel 12.0;HDR=YES;""" 'Create a record set of one record which is the current record being printed. Set rs = CreateObject("ADODB.Recordset") rs.open "SELECT * FROM [Sheet1$]", objConn, adOpenStatic, adLockOptimistic, adCmdText rs.Find "Product = '" & Field("Sheet1$.Product") & "'" 'Set the "Printed" field of the record to be "YES" thus flagging it as having been printed. rs.Fields("Printed") = "YES" rs.UpdateIf you still have problems open up the database connection properties (Data Link Properties) as used by BarTender for the main database connection and for "Extended Properties" remove the IMEX=1 reference by editing the value.
I tried some of your suggestions. Here is the errors I get:
Prior to removing the IMEX=1 line, I get "Cannot update. Database or object is read only"
I then removed IMEX=1 and got "The Microsoft Jet database cannot open the file, It is already open exclusively by another user"
0 -
As the error suggests, you cannot have the Excel file open elsewhere as it opens in exclusive mode. This is part of the limitation in using Excel as opposed to a proper database with proper record locking features.
0 -
As the error suggests, you cannot have the Excel file open elsewhere as it opens in exclusive mode. This is part of the limitation in using Excel as opposed to a proper database with proper record locking features.
Problem is, its not open elsewhere. Bartender itself is opening it twice, first to pull the information to display on the screen, then again to try to write back to it, so its not working, I guess I'll have to try with perhaps a different type of database, but this is becoming quite a workaround for what should be a basic feature
0 -
Where can i find the OnNewRecord options ? I´m using the verision 10.1 RS1 of Bartender Pro version
0 -
SBUBandit: Part of the problem of using Excel I suppose, which is not a true database.
gummio: You need to use an event based VB script.
0 -
'Define the object names. dim objConn dim strConn dim rs varSerial = 0 'Define the database connection parameter constants. Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adCmdText = &H0001 'Connect to the Excel file. Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='M:\Correct Labels\Inspection Labels\inspectTable.xlsx';Extended Properties=""Excel 12.0 Xml;HDR=YES;""" 'Create a record set of one record which is the current record being printed. Set rs = CreateObject("ADODB.Recordset") rs.open "SELECT * FROM [Sheet1$]", objConn, adOpenStatic, adLockOptimistic, adCmdText rs.Find "id = '" & Field("Sheet1$.id") & "'" varSerial = rs.Fields("Serial") 'Increase the Serial by one for each printed label rs.Fields("Serial") = varSerial + 1 rs.UpdateI'm getting the following error in the VB script editor:
OnIdenticalCopies (Line 21): Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
0 -
If there is no record in your record set search, then you cannot set a variable from a field in your non-existent record.
0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
12 Kommentare