Update Field In Excel Database

Legacy Poster

• July 22, 2016 18:22

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 

0

• 

  Legacy Poster

  ★ BarTender Hero ★

  • July 25, 2016 19:21

  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/

  0
• 

  Ian Cummings

  Moderator

  ★ BarTender Hero ★

  • July 27, 2016 13:41

  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.

  0

Please sign in to leave a comment.