Read Excel Rows and loop the Script

Hi,



Please find the script below, I want to read the excel file rows and columns and make the script run so many times depending on number of rows I have in the excel sheet. I need help on below script which I wrote, please suggest where I went wrong... If you have any sample code, please provide me.



'This function is to loop the records based on the below function "Function ReadLandAgentPay(Fname,Sname)"



Function ReadDataFromExcel()

Fname = "E:\\PropertyData.xls"

Sname = "Payment_Schedule"

CreateSCArr1 = ReadLandAgentPay(Fname,Sname)

For i = 1 to CreateSCArr1

s = ""

For j = 1 to 11

s = s + VarToString(Excel.Cells(i, j)) + "#"

Next

Next

ReadDataFromExcel = s

 

'Closes the driver

Call Excel.ActiveWorkbook.Save

Call Excel.Workbooks.Close

End Function

Excel.ActiveWorkbook.Save Excel.Workbooks.Close



'This will pass the excel loop info to the above function

Function ReadLandAgentPay(Fname,Sname)

Dim Driver, i

Set Driver = DDT.ExcelDriver(Fname, Sname)

RecNo = 0

CreateSCArr1 = CreateVariantArray(0, 0)

 

'Record Loop

While Not DDT.CurrentDriver.EOF

.CurrentDriver.EOF

 

'Column Loop

For i = 0 To DDT.CurrentDriver.ColumnCount - 1

PropNum = PropertyCount

i = 0 .CurrentDriver.ColumnCount - 1PropNum = PropertyCount

 

'Iterates through the properties

For j = 0 to (PropNum - 1)

PropName = Properties(j).Name

Next

Next

VarArrayRedim CreateSCArr1, RecNo

RecNo = RecNo + 1

 

DDT.CurrentDriver.Next()

WEnd

 

'Closing the driver

Call DDT.CloseDriver(Driver.Name)

Log.Message("Total Number of Agent Payments created: "& RecNo)

ReadLandAgentPay = CreateSCArr1

End Function