Forum Discussion

roytberA's avatar
roytberA
Occasional Contributor
6 years ago

Using getActiveXObject("Excel.Application") is taking long time.

Hi there,

I am using getActiveXObject("Excel.Application") to read and write from excel.  It seems slow to me. Is there any other way to read and write from excel that I can use ?  I don't use DDT.ExcelDriver because I get values by row number.  

 

Currently, my script takes 27 seconds to get values from 3 columns 71 rows.  

 

Thanks,

Apple

3 Replies

Sort By
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    6 years ago

    Not built in to TestComplete, no.  

     

    Keep in mind that, by using getActiveXObject, you are actually creating an instance of Excel.Application... meaning, that Excel needs to start up and run... and then load the spreadsheet.... all of which takes overhead.  I'd be willing to bet that most of that 27 seconds is in that overhead.  Meanwhile, using DDT.ExcelDriver you're not running the Excel application, you're simply using the excel spreadsheet as a database which means that you are using built in drivers in the Windows environment and you don't need to start an application.

    Why do you need to get values by row number?  The point of DDT.ExcelDriver is to cycle through all rows and, as you do so, evaluate what you need... row number becomes superfluous in that... if you need to pick specific rows based upon content, you can adjust the ExcelDriver and change the SQL statement that is built into that driver.  We do that ourselves... What's inside the ExcelDriver is just a number of ADO objects... just a matter of finding the CommandText and changing it to whatever SQL statement you want.




    • roytberA's avatar
      roytberA
      Occasional Contributor
      6 years ago

      Thank you Robert for your response.

       

      I am going to try using DDT.ExcelDriver.  The reason why I didn't use it before is because I have more than 48,000 rows in the excel sheet. 

       

      My question is how to use SQL statement in excelDriver?

      Thanks

       

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        6 years ago

        As mentioned, the DDT drivers are simply wrappers around ADO objects... so, the driver has a child object called ADOCommandObject which has a CommandText property. Set the CommandText to whatever you want to select the appropriate data.

        function blah2(){
    let myDriver = DDT.ExcelDriver('C:\\Temp\\test.xls', 'Sheet1', true);
    myDriver.ADOCommandObject.CommandText = 'SELECT * FROM [Sheet1$] where Column1 = "blah"';
}

        See https://querysurge.zendesk.com/hc/en-us/articles/205766136-Writing-SQL-Queries-against-Excel-files for how to write queries against Excel sheets.