Jump to content


< Back to Forum


 

Using Excel


  • Please log in to reply

#1 SupportTeam 11 February 2003 - 06:46 PM

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