Jump to content


Photo
- - - - -

Update Field In Excel Database

update excel database connectivity vb scripting script

  • Please log in to reply
2 replies to this topic

#1 guygilan

guygilan

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 22 July 2016 - 10:22 AM

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 



#2 derrikf

derrikf

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 25 July 2016 - 11:21 AM

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.seagul...ck-to-xls-file/



#3 Ian C - BarTender ninja @ Seagull

Ian C - BarTender ninja @ Seagull

    Technical Services Specialist, EMEA

  • Administrators
  • 2,821 posts
  • LocationMadrid

Posted 27 July 2016 - 05:41 AM

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.







Also tagged with one or more of these keywords: update, excel, database, connectivity, vb, scripting, script

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users