Vb Script Loop Follow

0
Avatar
Legacy Poster

Greetings; I have managed to embed a VB script into a barcode that hopefully opens a network based Excel spreadsheet when the barcode is scanned. When I run the "test" it works fine. It opens the Excel page that I have linked to the script. However it keeps opening it up again and again. I can't seem to find the right syntax to end the loop. Any help would be appreciated. The code is below.

 

Cheers

Ron

 

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("N:\_REPAIRS\RMAs\RMA_Issued\RMA140829-1.xlsM")
objExcel.Application.Visible=True
objWorkbook.Worksheets(1).Activate

3 comments

0
Avatar
Ian Cummings
Moderator
Comment actions Permalink

In what type of VB Script are you using?  It's probably best to use an event based VB Script so that the code only runs on that particular event.

 

For your reference, here is an example VB script that connects to an Excel file and adds a record.  A quick Google search will give you plenty of examples for running queries and reading records. 

 

*Note that the connection string might well be different for you depending on the file type.

'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\Example.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
 
'Open the Excel worksheet and add a new record with data from the label populating some fields.
Set rs = CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM [Sheet1$]", objConn, adOpenStatic, adLockOptimistic, adCmdText
rs.AddNew
     Field("Sheet1$.Product") = Format.NamedSubStrings("btProduct").Value
     Field("Sheet1$.Description") = Format.NamedSubStrings("btDescription").Value
     Field("Sheet1$.Price") = Format.NamedSubStrings("btPrice").Value
rs.Update

'Close the worksheet and Excel file connection.
rs.Close
objConn.Close

0
Avatar
Legacy Poster
Comment actions Permalink

Thanks Ian; I guess I should give more information. I don't want to auto-populate any fields in the spreadsheet, just open it up after scanning a hidden script in the barcode. Once the spreadsheet is open the technician has to manually input various information in a few fields. The unit then goes to another test area where another technician does something and enters that information on the spreadsheet. I just want each scan to open the same network form for additional information. Right now what I have does that but keeps looping to open the correct spreadsheet again and again. I can't close them quick enough. I guess what I need is the syntax to close the command after it has opened it up once.

 

Cheers and thanks for your help.

 

Ron

0
Avatar
Domingo Rodriguez
Moderator
Comment actions Permalink

If instead of the VBScript code to open the Excel spread sheet, you write:

 

MsgBox ("This is my message")

 

At print time, how many times will this message appear, once or a lot of times? If the message appears a lot of times, then the VBScript event is firing more times than what you want. As Ian C. advised, you would write instead your code in an "Event Control Scripts" VBScript source.

Please sign in to leave a comment.