Mark records being printed from Excel database Follow

Avatar
Francisco García de Polavieja

Question

BarTender is capable of only reading from a database, but I'd like to mark a row as read in Excel once it's done. Is this possible?

Answer

The best way to go about doing this is to create an additional field in the Excel worksheet to act as a "Label printed" flag. When a record is used to print a label a value is written to the "Label printed" field marking it as having been printed. A query can be used when connecting to the Excel file which omits any records that have the "Label printed" field set.

Unfortunately BarTender in terms of its database connectivity is a read-only application.  This means that in order to write back a value to a printed database record we will need to use BarTender's built-in VB Scripting capability.  Here are the sample files:

Excel_Update.btw

Fruits.xls

 In the Excel file there are two fields, one for a product and the other which flags when the product's record has been printed. 

In the BarTender label format I have made a connection to the Excel file, which assumes it will be found in the "C:\Seagull" folder.  It uses a query to only find and therefore possibly print those products (records) that do not have the word "YES" in the "Printed" column.  The select record at print time option is turned on allowing the user to select which of the records that have not been printed, are to be printed.

During the print job the VB script contained in the object in red placed off the label, runs a section of code that connects to the Excel file, finds the record being printed and sets the "Printed" field to the value "YES" so that next time the user prints it will not be available in the select at print time dialog to choose.  The VB script is as follows.


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.Jet.OLEDB.4.0;Data Source='C:\Seagull\Fruits.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 "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.Update

The items marked in bold can be changed according to your Excel path and filename, and the names of the fields you wish to find and update.  You can use this example to apply for your own label printing needs. 

Was this article helpful?

Please sign in to leave a comment.