Help On Array Loop

Can someone please look into this :(

The reason for the problem is that your loop is wholly contained in your checkexcelcells function.  So, you call that function once, it loops through everything, posting the value each time to a variable and then returns that variable.  So, your function will always only return the last value.



You need to run your loop through your spreadsheet outside of the function or move the writing to the result file inside the function.

Hi Robert,



Thank you for replying.



It will be great help if you could help me in code... I'm unable to get your point...

Hello 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

Hi Allen,



Thank you very much for the code... it is working as expected.

One more thing I want to ask is... the excel file is not closing... I wrote below line to save and close th excel, but still I see excel pop-up saying

"Do you want to save the changes you made to 'MyResults.xlsx'"



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



'Close excel connection                                    'added these lines to save and close the excel

Call Excel1.ActiveWorkbook.Save

Call Excel1.Workbooks.Close



End Sub



Please suggest!!

Try the following instead:

Call Excel1.Keys("^s")

Call Excel1.Workbooks.Close

Hi Ncoes,



Thanks for replying...

As you said I tried, I'm getting following error...

"Object doesn't support this property or method: 'Excel1.Keys'"

Hello Anil,



Replace saving an active workbook with saving the exact workbook you need. Also, closing workbooks does not close the application. You need to use the Application.Quit method:

  Call ExlFile.Save

  Call Excel1.Quit

Hi David,



Understood your point...This is working... :)

Thanks a lot...!!