Forum Discussion

scot1967's avatar
scot1967
Frequent Contributor
5 years ago

COM to access .xls and .xlsx files without Excel on the PC?

I have run into a problem running tests I have devloped on my PC with TestComplete to read Excel files via COM in TestExecute on another PC.  My PC has Excel installed and the lab PC does not.  The w...
Data-Driven Testing
  • tristaanogre's avatar
    tristaanogre
    5 years ago

    Unfortunately, if you're creating a COM object access to "Excel.Application", you need excel installed.

     

    Question:  What are you using Excel for?  There are ways of reading data from excel (or writing) that use data drivers rather than Excel application (that's what the DDT.ExcelDriver object does).  

     

    Side note: This is why, for automated testing, I don't use Excel for sourcing external data. You are dependant upon an application that is not universally available.  CSV or text or INI or XML are all much more portable and accessible.

tristaanogre's avatar
tristaanogre
Esteemed Contributor

Unfortunately, if you're creating a COM object access to "Excel.Application", you need excel installed.

 

Question:  What are you using Excel for?  There are ways of reading data from excel (or writing) that use data drivers rather than Excel application (that's what the DDT.ExcelDriver object does).  

 

Side note: This is why, for automated testing, I don't use Excel for sourcing external data. You are dependant upon an application that is not universally available.  CSV or text or INI or XML are all much more portable and accessible.

scot1967's avatar
scot1967
Frequent Contributor
5 years ago

We are working with reports in RDLC format and can't access the data throught the gui without exporting it or possibly using an AI plug in.  The microsoft component only supports Excel, PDF and Word.  

 

Using the Dataloop in a KWT I ran into a problem with mixed data type formats within the same column.  Does DDT have this same limitation?

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    5 years ago

    Yes, it will, but that's not a limtiation neceesarily of the object your using but simply with data in Excel or even CSV.  The first data field in a column is what Excel and other engines use to determine what to do with the data fields in the rest of the column.  

     

    The best work around that I have for you is to explcitly convert the data in the column to what you need it to be in your code.  So, when you read column 1 and you want them all to be string, everytime you read a value, call aqConvert.VarToString to force the conversion to a string data type and so on.

    • scot1967's avatar
      scot1967
      Frequent Contributor
      5 years ago
      tristaanogre wrote:

      Yes, it will, but that's not a limtiation neceesarily of the object your using but simply with data in Excel or even CSV.  The first data field in a column is what Excel and other engines use to determine what to do with the data fields in the rest of the column.  

       

      The best work around that I have for you is to explcitly convert the data in the column to what you need it to be in your code.  So, when you read column 1 and you want them all to be string, everytime you read a value, call aqConvert.VarToString to force the conversion to a string data type and so on.

       

       This is interesting.  I have not tried this before.  The data loop just leaves out types that don't match if I view it while setting the loop up.  This could have potential.  :)  Thanks.       

      • Marsha_R's avatar
        Marsha_R
        Moderator
        5 years ago

        As usual, tristaanogre is correct.   :)

         

        If there's any way you can get out of using Excel, do so.  The explicit conversion will save you most of the time, but I have seen Excel sheets get edited and then end up with control characters in them so that they can't be read any more and the data has to be entered in a new sheet manually.   After the third or fourth time that you have to recreate your data, this will become most annoying.  ;)