Vb Script Loop

Legacy Poster

• September 26, 2014 19:25

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

0

• 

  Ian Cummings

  Moderator

  ★ BarTender Hero ★

  • September 27, 2014 09:19

  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
• 

  Legacy Poster

  ★ BarTender Hero ★

  • September 28, 2014 03:26

  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
• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • October 16, 2014 10:29

  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.

  0

Please sign in to leave a comment.