Forum Discussion

EMC_TX's avatar
EMC_TX
15 years ago

Read and Write into Excel

Hi ALL



I am running into some problems trying to read and write to an excel file. I have data I am reading for a data driven test and I would like to write in the last column the result of the test pass/fail.

I am using this code to cycle through the test data I want to use to run the test

Set xlsDriver = DDT.ExcelDriver("c:\Data.xls","Sheet1")

While Not xlsDriver.EOF()

Project.Variables.Data1 = xlsdriver.Value(0)

Project.Variables.Data2 = xlsdriver.Value(1)

Project.Variables.Data3 = xlsdriver.Value(2)



Call RunTest



xlsDriver.Next() 'Goes to the next record

WEnd

DDT.CloseDriver("c:\Data.xls")





In the function “RunTest” I want to be able to write back to the same file the test results in the last column pass/fail.



I tried to open the file to write to it this way

FName = "c:\Data.xls"



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

ExcelApp.DisplayAlerts = False



If (Utilities.FileExists(FName)) Then

Set Logbook = ExcelApp.Workbooks.Open(FName)

Else

Set Logbook = ExcelApp.Workbooks.Add

Call Logbook.SaveAs(FName)

End If



Set Sheet = Logbook.Sheets(1)

Sheet.Cells(1,4) = "PASSED"

But since Excel is already open, this failed.





Since it is already open, I tried setting

xlsdriver.Value(3) = “PASSED”

but TC did not like this



If I close the driver “ DDT.CloseDriver("c:\Data.xls")”

So I can use the “Set ExcelApp = Sys.OleObject("Excel.Application")

“ command, I then lose my place in the While Not xlsDriver.EOF() and it run the same test in an infinite loop



If I only use the “Set ExcelApp = Sys.OleObject("Excel.Application")

“ commands, I do not have an .EOF option to cycle through the test data



any ideas how I can code this to read and write at the same time?



Any assistance appreciated

Thanks in advance

Eric
  • Dmitry_Nikolaev's avatar
    Dmitry_Nikolaev
    Staff
    15 years ago

    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.
  • ChuckAtIvory's avatar
    ChuckAtIvory
    Contributor
    15 years ago
    Eric,



    Though someone said Excel does not allow concurrent access, I know we set up a file in Office 2007 that allows multiple users to work on it at once. Knowing this is possible might help. Though I don't know how, or what settings were used, I it possible.