Workflow to read MS.Excel file using VBscript

OL Connect Professional Workflow
1

Dear All,

There are 2 excel file located in the local drive, and there are more than 1 Workflow process will open and read the excel files at the same times, for data lookup, using VBScript.

I noticed that, sometimes there are no value being lookup.
Will it due to more than 1 workflow process is accessing the same Excel File at the same time?

Thanks and Regards,
Janice

2

It depends how your script is opening the file.

If you use the Excel.Application object, it is likely that you will run into issues because that object is not thread-safe.

If you use FileSystemObject.OpenTextFile to open and read the file, then you must simply make sure to specify the ForReading value for the ioMode function parameter when opening the file to ensure that it can be shared across processes.

3

Hi @Phil ,

Below is my script. Is the script doable?

Const adOpenStatic = 3
Const adLockOptimistic = 3
x = 0
queueSize = 0


excelName = Watch.GetJobFileName

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & excelName &"';Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
Set rs = CreateObject("ADODB.Recordset")
set conn = CreateObject("ADODB.Connection")
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")


companyno = Watch.GetVariable("global.PB_ProjectName_SC")

query = "Select * from [SalesAdminMaintenance$] where Company_No = '"&companyno&"'"
        rs.Open query, cn , adOpenStatic, adLockOptimistic

   Do Until rs.EOF
          if rs.Fields(0).value <> "" then
          companyname = Trim(rs.Fields(0).value)
          contactno = Trim(rs.Fields(2).value)
          emailadd = Trim(rs.Fields(3).value)
          emailcc = Trim(rs.Fields(4).value)

Watch.SetVariable("CompanyName"),companyname
          Watch.SetVariable("CompanyContactNo"),contactno
          Watch.SetVariable("CompanyEmailAddress"),emailadd
          Watch.SetVariable("CompanyCC"),emailcc

   end if
          rs.MoveNext
         Loop

   rs.Close

cn.Close
Set FSO= Nothing
4

I see one potential issue with your code: you use adOpenStatic as your cursor type, which means new data may not be immediately available to the cursor. While that is unlikely to cause you any problem, it’s something to consider.

Since all you’re doing with your cursor is navigate forward to find specific records, I’d recommend using adOpenForwardOnly (i.e. 0) as the cursor type, which will improve performance and eliminate that remote possibility I mentioned above.

Note also that since you don’t use the FSO object, you should remove all references to it.

Finally, your loop goes through all records returned by your query. For each record, you set local process variables to the values found in the fields. That means it’s always the values found in the last record that actually get stored in the variables. If that’s really what you want, then it’s ok. But if you only want the first value and then exit the loop, you should use the exit do statement once you have found the first value, which will help performance…