Editing Excel files

Brad,



I use the ADO function to read data from excel.

So every excel tabsheet i handle as a database table and i can query on it.

perhaps this is a solution for your problem.



 Below i have a example how you can do it.



Regards



R. Wiertz

Quality Assurance



Furore.com









  Procedure ReadFromExcel(aExcelFile : String);

  var

    DBConnection : OleVariant;

    Command : OleVariant;

    RecordSet : OleVariant;

    ConnectionString : String;

    strQuery : String;

    SheetName : String;

    iFieldCount  : Integer;



  begin

    ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source= ' + aExcelFile + ';Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"';

    

    if Length(aConnectionString) > 0 then

    begin

      DBConnection := ADO.CreateConnection();

      DBConnection.ConnectionString := aConnectionString;

      try

        DBConnection.LoginPrompt := False;

      except

        //do nothing, there is no login prompt

      end;

      

      try

        DBConnection.Open;

   

      SheetName := 'Your excel sheet name';



       strQuery := 'Select * from ['+SheetName+'$]';



      Command := ADO.CreateCommand();

      Command.CommandText := Query;

      Command.ActiveConnection := DBConnection;

      RecordSet  := Command.Execute();



      while not aqConvert.VarToBool(RecordSet.EOF) do

      begin

        for iFieldCount := 0 to RecordSet.Fields.count -1 do

        begin

          Log.message(aqConvert.VarToStr(RecordSet.Fields(iFieldCount).name));

          Log.Message(aqConvert.VarToStr(RecordSet.Fields(iFieldCount).Value));

       end;       

    end;

    RecordSet.Close;

   DBConnection.Close;



  end;

 

Hi Brad,



You can work with Excel files via COM rather than via Excel's GUI. There's an example here:

http://www.sqaforums.com/showflat.php?Cat=0&Number=602068&an=0&page=0&gonew=1



>

import this excel file into test complete as a table or DB rather than using the DDT.ExcelDriver method?

<

You can use ADO to read Excel files. However, it is similar to DDT, so there's no reason to do this. Also, you can read them via COM (via the properties and methods used in the example I referred to above).

Jared,

Hi Raymond,



Yes, that's correct. However, DDT already uses ADO, and there's actually no reason to use ADO directly. If you'd like to save data in an Excel sheet, COM would be the most convenient way.

I find DDT is short supplied for methods. Like to browse across the cells I have to keep looping around with the "Next" function because cannot access the cells directly and for a "Next" I don't find  a "Previous"  that can help me browse backward. Also I could not find any method that could help me write data into the spreadsheet. Say I want to use an order number generated in a flow as input in the flow later and therefore want to store in my data store. I found COM as the coolest approach. Wrote a custom script extn with an object called DataSheet.



In the script I have would use



Result = DataSheet.GetAccess(FileName)

Result = DataSheet.LooseAccess(FileName)

Result = DataSheet.GetData(Sheetname, FieldName, Iteration)

Result = DataSheet.SetData(Sheetname, FieldName, Iteration, Value)

Result = DataSheet.FindValue(Sheetname, Value, SearchAllBool)

Result = DataSheet.SortValues(SheetName, FieldName, AscDesBool)

Result = DataSheet.FieldResultsCompare(Sheet1, Field1, Sheet2, Field2, ResSheet, ResField)

etc



Implementations would be like



Dim objExcel

Dim objWorkbook

Function DataSheet_GetAccess(WorkBookName)   

  Dim FilePath

  FilePath = ProjectSuite.Path & "TestData\" & WorkBookName

  Set objFSO = CreateObject("Scripting.FileSystemObject")

  If objFSO.FileExists (FilePath) then

    Set objExcel = CreateObject("Excel.Application")  

    Set objWorkbook = objExcel.Workbooks.Open(FilePath)

    DataSheet_GetAccess =  1

  Else

    DataSheet_GetAccess = 0

  End if

End Function



Function DataSheet_GetData(WorkSheetName, IterationNumber, FieldName)

    Set objWorksheet = objWorkbook.Worksheets(WorkSheetName)

      i = 1

      Do While objWorksheet.Cells(1, i).Value <> ""

        If objWorksheet.Cells(1, i).Value = FieldName Then

             DataSheet_GetData = objWorksheet.Cells(IterationNumber, i).Value

             Exit Do

        End If

        i = i + 1

      Loop

    Set objWorksheet = Nothing

End Function

...

...