Using Excel

[SupportTeam]

We have recieved may questions about retrieving data from Excel. Below is an example of the VB Script which can be used to write data into Excel and then read back results. The script the below just needs to be copied into a VoiceGuide "Run VBScript" module. If anyone has any questions about it please post them here...

 

'takes 3 Result Variable inputs,

'writes the inputs into an Excel spreadsheet,

'reads the results calculated by Excel in other cells

'returns results back to VoiceGuide.

 

Dim excel, workbook, sheet

Dim Result1 , Result2 , Result3 , sReturnedRVs

 

Set excel = CreateObject("Excel.Application")

Set workbook = excel.Workbooks.Open("C:\SomeSpreadsheet.xls")

workbook.Sheets("Sheet1").Select

 

workbook.ActiveSheet.Cells(2, 2).Value = $RV[CallerInput1] 'cell B2

workbook.ActiveSheet.Cells(2, 3).Value = $RV[CallerInput2] 'cell C2

workbook.ActiveSheet.Cells(2, 4).Value = $RV[CallerInput3] 'cell D2

 

'every time new values are entered Excel recalculates any formulas,

'allowing us to read data from formula cells at any time...

 

Result1 = Abs(Cint(workbook.ActiveSheet.Cells(2, 22).Value)) 'cell V2

Result2 = Abs(Cint(workbook.ActiveSheet.Cells(2, 15).Value)) 'cell O2

Result3 = Abs(Cint(workbook.ActiveSheet.Cells(2, 24).Value)) 'cell X2

 

sReturnedRVs = "[Result1]{" & Result1 & "}[Result2]{" & Result2 & "}[Result3]{" & Result3 & "}"

 

iRet = WriteResultFile(sReturnedRVs)

 

'the 3 result varaibles can now be accessed in the script using:

'$RV[Result1] $RV[Result2] $RV[Result3]

'msgbox sReturnedRVs

 

workbook.close False

excel.quit

 

set excel = Nothing

set workbook = Nothing

 

 

 

function WriteResultFile(strResult)

const ForReading=1, ForWriting=2, ForAppending=8

Dim filename, fso, ts, outdata, outdata2, outdata3

 

filename = "VGRUNRESULT_$RV_DEVICEID.TXT"

set fso = CreateObject("Scripting.FileSystemObject")

set ts = fso.OpenTextFile(filename, ForWriting, True)

ts.WriteLine(strResult)

ts.Close

WriteResultFile = 0

end function