Exporting Data To Excel
i need some help
i am trying to save automatically (export) some data from the bartender to an excel file
the label that i am making is receiving 20 barcodes (by scanner) and print the label with these 20 barcodes and also a label serial number
i would like to get an excel table that summarize for each label serial number what 20 barcodes apear in it
i am pretty sure that this can be done but i have no idea how...
someone can help?
thanks,
Sharon
-
Legacy Poster
★ BarTender Hero ★
Sharon,
You could use one of BarTender's pre-built logging solutions to write to the system database or create a text file log. This can be set under [b]Administer[/b] > [b]Log Setup[/b]
Alternatively, I have used the code below to program my own export script when a label is printed. This script is in my OnIdenticalCopies VB event at the document level (found by going to [b]View[/b] > [b]View Options[/b] > [b]VB Scripting Tab[/b]
[code]'Place script below at to
'If Format.IsPrinting = False then Exit sub
Dim objCon
Dim strSQL
Set objCon = CreateObject("ADODB.Connection")
'Create connection (in the case of xls file in the same folder as btw file)
objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='DataCollection.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
objCon.Open
'Create statement
strSQL = "INSERT INTO [Sheet1$] (Data1) values ('" & BarcodeData & "')"
'Run SQL
objCon.Execute strSQL
'Close connection
objCon.Close
Set objCon = Nothing[/code]
You could take this code and expand it to include more columns. The 'Data1' Just refers to my first column, and 'BarcodeData' refers to my data source shared name. So you can take that line and copy it many times changing the column name and data source name to write more things. Obviously you will have to change the other text a bit to account for your file name and location.0 -
Legacy Poster
★ BarTender Hero ★
I have tried to use this with a modification to fill more than one field at once , a multiple from one sql statement as below
'Create statement
strSQL = "INSERT INTO [Sheet1$] (Power,LotNo,ExpiryDate,Qty,BoxNumber) values ('" & Power1&LotNo1&ExpDate1&Qty1&BoxNumber & "')"
'Run SQL
objCon.Execute strSQLand it brings up the error number of query values and destination fields are not the same, I think I have tried every variation on this without success and had a few other people look at this with me. If anyone knows where I am going wrong let me know thanks. (I think the values are wrong because the & is joining them into one long number rather than seperating them .
0 -
Legacy Poster
★ BarTender Hero ★
I have tried to use this with a modification to fill more than one field at once , a multiple from one sql statement as below
'Create statement
strSQL = "INSERT INTO [Sheet1$] (Power,LotNo,ExpiryDate,Qty,BoxNumber) values ('" & Power1&LotNo1&ExpDate1&Qty1&BoxNumber & "')"
'Run SQL
objCon.Execute strSQLand it brings up the error number of query values and destination fields are not the same, I think I have tried every variation on this without success and had a few other people look at this with me. If anyone knows where I am going wrong let me know thanks. (I think the values are wrong because the & is joining them into one long number rather than seperating them .
I also tried this
strSQL = INSERT INTO [Sheet1$] (Power,LotNo,ExpiryDate,Qty) VALUES('" & Power1 & "', '" & LotNo1 &"', '" & ExpDate1 &"', '" & Qty1 &"')"
andstrSQL = INSERT INTO [Sheet1$] (Power,LotNo,ExpiryDate,Qty) VALUES('" & Power1 & "', '" & LotNo1 &"', '" & ExpDate1 &"', '" & Qty1 &"');"
nothing seems to work .
0 -
Legacy Poster
★ BarTender Hero ★
Sorry to resurrect an old thread but I have a similar situation that I don't know how to solve.
I have an excel database in which all my Serial Numbers and Test related data is stored. I have managed to set queries to query one serial number at a time (via a barcode scanner) and print only data that contains a "Passed" remark. Works great but I also want to export whatever I printed plus the data that was retrieved to an excel sheet so that I can have a record of what was printed and queried.
So my question is: How can I export data that was queried and printed into another excel sheet? I am a VB newbie let alone VB newbie to Bartender so please walk me through it.
Thank you in advance..
0 -
Legacy Poster
★ BarTender Hero ★
anyone?
0 -
Legacy Poster
★ BarTender Hero ★
Does anyone have a working example of this?
0 -
Shotaro Ito
★ BarTender Hero ★
BarTender's database connection is read only and you cannot write back the value to database.
In Automation edition or above, you can log desired field to CSV file via Administer > Log setup > Text file log > Log print job information, which might helps.
0 -
MUHAMMAD ABBAS
★ BarTender Hero ★
One silly question, the above VBA code where will I add path of my excel file?
0 -
gurkaN GURBUZ
★ BarTender Hero ★
This is working
i found it too many years later :D
strSQL = "INSERT INTO [Sheet1$] (item, serialnumber) values ('" & Format.Objects("Serial").value & "','" & Format.Objects("item").value & "')"
0
Please sign in to leave a comment.
Comments
9 comments