Jump to content
guygilan

Update Field In Excel Database

Recommended Posts

guygilan

Hello Everybody!

 

I've encountered a problem while using bartender:

I would like that when I print labels from an excel database I could use the data entry form to update a certain field in excel. 
For example after printing a label for a product update in excel its current location in the warehouse.

 

I read in can't be done directly with bartender and I have to use VB Scripting for it.

I would very much appreciate if you could help me write the necessary code to the task.

 

 

Thank you,

Guy 

  • Like 2

Share this post


Link to post
Share on other sites
derrikf

I'm having the same issue here. I've found the following code, but am still have issues configuring it correctly...

 

'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.Update

 

I'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.

 

For reference I found the code from this forum post:

http://forums.seagullscientific.com/index.php?/topic/1687-write-serial-number-back-to-xls-file/

Share this post


Link to post
Share on other sites
Ian C - BarTender ninja @ Seagull

Sounds like you have zero records in your returned record set which is why you get an error when you try to set a value from an assumed record field.  You probably want to test for that before using it.

Share this post


Link to post
Share on other sites

×