cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Contributor

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 way it looks I may have to install Excel on my lab PC's.  Is there a way around this?  Thanks!!

 

ExcelError.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Hero

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

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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

8 REPLIES 8
Highlighted
Community Hero

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

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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

Highlighted
Contributor

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

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?

Highlighted
Community Hero

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

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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Highlighted
Contributor

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


@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.  Smiley Happy  Thanks.       

Tags (1)
Highlighted
Community Hero

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

As usual, @tristaanogre is correct.   Smiley Happy

 

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.  Smiley Wink

Highlighted
Contributor

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

@Marsha_R wrote:

As usual, @tristaanogre is correct.   Smiley Happy

 

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.  Smiley Wink


The data is being exported from our app from an RDLC report which I have no way to access via TestComplete.  No one will be changing the data in the spreadsheet unless the application export code changes.  The data is exported to an Excel .xlsx file format, read into TestComplete and then the export file is deleted.  The imported data is also run through a series of statements to remove special characters.

 

@tristaanogre 

"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?"  Your answer was yes.

 

I have been thinking about this and if the issue is with the Microsoft DDT driver that reads the Excel file ignoring data (returning null?) for data that is of a different type how will using aqConvert.VarToString to force the conversion on data read from the file correct the problem if the data is not being read in correctly to be converted?

 

Maybe I am a little confused?

Highlighted
Community Hero

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

My suggestion was that it might help.  Best you can do is try.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Highlighted
Contributor

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


@tristaanogre wrote:

My suggestion was that it might help.  Best you can do is try.


Much appreiciated!  I may give it a try.  None the less not only for this reason but others as well, it has been decided that Excel will be required for our lab set up so it is going into the lab machines.    

 

Have a great one!