cancel
Showing results for 
Search instead for 
Did you mean: 

Editing Excel files

Occasional Contributor

Editing Excel files

Hi there,

Was wondering if there was an easier way to edit the content of an excel file? More specifically, is there a way i can reference specific cells?

I have a file that i use to validate test results. This file has variable content dependant on what the value of a certain cells is. I would like to make the updating of this file an automated process instead of having to manually open the file edit the value and close it, due to the size of the file this is a very time consuming process.

At the moment i have done it through a very ugly hack, that is easily broken.

function ChangeCommStructure(Sender)

{

var CommStruct = UserForms.Main.CommStructure.ItemIndex+1;

var file = UserForms.Main.FilePath.Lines.Text+"[Enter]";


var explorer;

var excel;

var xldesk;

var wndAU_Comm;

var toolbarWindow32;

Sys.Keys("[Hold][Win]r[Release]"+file);

Sys.WaitProcess("EXCEL", 15000, 1);

excel = Sys.Process("EXCEL");

xldesk = excel.Window("XLMAIN", "*").Window("XLDESK");

wndAU_Comm = xldesk.Window("EXCEL7", "AU_Comm");

wndAU_Comm.Keys("^[Home]");

wndAU_Comm.Keys("[Right][Right][Right][Right][Right][Right]");

wndAU_Comm.Keys("[Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down][Down]");

wndAU_Comm.Keys(CommStruct)

xldesk.Window("EXCEL6").Keys("[Enter]");

Delay(1500);

wndAU_Comm.Keys("~[F4]");

Delay(1500);

excel.Window("#32770", "Microsoft Office Excel").Window("Button", "&Yes").Keys("[Enter]");

}


Additionally is there a way i can import this excel file into test complete as a table or DB rather than using the DDT.ExcelDriver method? Or is this even a worth while change?


Thanks,

Brad
5 REPLIES 5
Occasional Contributor

RE: 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;

 

Staff

RE: Editing Excel files

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).



------
Yuri
TestComplete Customer Care Engineer

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Occasional Contributor

RE: Editing Excel files

Jared,



For so far as i know, the way you descript you need excel installed on your pc.

When using ADO you only need the excel odbc drivers installed.







Regards




R. Wiertz

Furore.com




Staff

RE: Editing Excel files

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.



------
Yuri
TestComplete Customer Care Engineer

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Contributor

RE: Editing Excel files

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

...

...



Syed Ahmed Safvan

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors