Vbs Msexcel

[Bob Arnold]

I've copied and modified an example to enter some $RVs into Excel, but I don't want to specifiy the exact cells, but rather, have it go to the last row and add data. Here is the script, can you help me get the target location down to the end?

 

Thanks

 

Dim xlApp 'Excel.Application

Dim xlBook 'Excel.Workbook

dim xlSht

Dim filename, value1, value2, value3, value4

 

on error resume next

 

filename = "c:\temp\voiceguide\3res.xls"

 

Set xlApp = CreateObject("Excel.Application")

set xlBook = xlApp.WorkBooks.Open(filename)

set xlSht = xlApp.activesheet

 

xlApp.DisplayAlerts = False

 

'write data into the spreadsheet

xlSht.Cells(15, 1) = $RV[GetMonth]$RV[GetDay]

xlSht.Cells(15, 2) = $RV[GetPhoneNum]

xlSht.Cells(15, 3) = $RV[GetCity]

xlSht.Cells(15, 4) = $RV[GetNumPass]

xlSht.Cells(15, 5) = $RV_MONTH$RV_DATE$RV_HOUR$RV_MINUTE

xlSht.Cells(15, 6) = $RV_MINUTE$RV_SECOND

xlSht.Cells(15, 7) = $RV_CIDNUMBER

 

 

 

 

xlBook.Save

xlBook.Close SaveChanges=True

xlApp.Close

xlApp.Quit

 

set xlSht = Nothing

Set xlBook = Nothing

Set xlApp = Nothing

[SupportTeam]

Two possible approaches:

 

1.

Loop through the rows to find the first free row.

eg:

 

'starting at row 15

iRowNumber=15

while xlSht.Cells(iRowNumber, 1) <> ""

iRowNumber = iRowNumber + 1

wend

xlSht.Cells(iRowNumber , 1) = $RV[GetMonth]$RV[GetDay]

xlSht.Cells(iRowNumber , 2) = $RV[GetPhoneNum]

etc...

 

2.

Append data to a comma delimted file - comma delimited files (.csv files) can be read by Excel.

In a Run Program module specify (all on one line):

 

command.com /c echo $RV[GetMonth]$RV[GetDay], $RV[GetPhoneNum], $RV[GetCity], $RV[GetNumPass], $RV_MM$RV_DD$RV_HH, $RV_NN$RV_SS, $RV_CIDNUMBER >> c:\temp\voiceguide\3res.csv

 

Result Variables: $RV_MM $RV_DD $RV_HH $RV_NN $RV_SS are available in v5.0 of VoiceGuide onwards (v5.0 Beta is out now)