Help On Array Loop
Hi, I'm trying to read from one excel sheet and write the same data to another excel sheet, below is the code what I wrote, but I'm unable to write all the data to the target sheet... only last...
Forum Discussion
AlexeyKolosov
Staff
StaffHello Anil,
You can create an array of records from the first file and write the array elements to the second one:
Sub anotherexcelvalues
'open other excel file
Set Excel1 = Sys.OleObject("Excel.Application")
Set ExlFile = Excel1.Workbooks.Open("E:\Exported Reports\MyResults.xlsx")
Set Shtname = Excel1.Sheets("Sheet1")
Delay 500
'call other excel data
ArrayOfValues = checkexcelcells
For i = 0 to UBound(ArrayOfValues)
If ArrayOfValues(i) <>"" Then
Shtname.Cells(i + 1,1).Value = ArrayOfValues(i)
End If
Next
End Sub
Function checkexcelcells
Dim Cellvalue()
'Open Excel Connection
Set Excel = Sys.OleObject("Excel.Application")
Call Excel.Workbooks.Open("E:\Exported Reports\PaymentSchedule_04-04-2012_11-52.xls")
Delay 500
'Check number of rows created in the excel sheet
i = Excel.ActiveSheet.UsedRange.Rows.Count
ReDim Cellvalue(i - 1)
If i > 1 Then
For j = 2 to i
For k = 5 to 5
Cellvalue(j - 2) = VarToString(Excel.Cells(j, k).Value)
Log.Message Cellvalue(j - 2)
Next
Next
checkexcelcells = Cellvalue
End If
End Function