Read and Write into Excel

Hi Eric,





The script below demonstrates how to read values from and write them to an Excel sheet. The script:

- opens an .xls file;

- counts the number of used rows and columns (the Dimensions routine);

- iterates through the file row by row and retrieves data for the test;

- logs the data from the file;

- processes the results of the test and writes the result to the last column: PASSED if a row number is even, FAILED otherwise.





Whether the end of the file is reached is checked via the number of used rows and columns.

Sub Main

  FileName = "c:\Test\Data.xls"

  Set MsExcel = Sys.OleObject("Excel.Application")

  MsExcel.Workbooks.Open FileName

  MsExcel.Visible = True

  Call RunTestAndInsertResults(MsExcel)

End Sub





Sub RunTestAndInsertResults (Excel)

  Dim NumberOfUsedCells

  NumberOfUsedCells = GetDimensions(Excel)

  For RowsIndex = 1 to NumberOfUsedCells(0)

    For ColumnsIndex = 1 to NumberOfUsedCells(1)

      Log.Message(Excel.Cells(RowsIndex,ColumnsIndex))

    Next

    If RowsIndex mod 2 = 0 Then

      Excel.Cells(RowsIndex,NumberOfUsedCells(1) + 1).Value = "PASSED"

    Else

      Excel.Cells(RowsIndex,NumberOfUsedCells(1) + 1).Value = "FAILED"

    End If

  Next

End Sub





Function GetDimensions(Excel)

  Dim Count(1)

  xlCellTypeLastCell = 11

  Count(0) = Excel.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

  Count(1) = Excel.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

  GetDimensions = Count

End Function

Does it help you?





Please refer to the 'Working With COM Objects' help topic for more information.