cancel
Showing results for 
Search instead for 
Did you mean: 

DDT.ExcelDriver for .xlsx not .xls

SOLVED
BMD
Contributor

DDT.ExcelDriver for .xlsx not .xls

Team,

The function below worked until I changed the format from .xls to .xlsx.  Now the log returns "

External table is not in the expected format".  Is there are way to correct this.  I did not think the two Excel formats would cause this?

 

This is how the function is called:

ReadExcelHeaderFile((PathFileName), "Project", 0);

This is the function

function ReadExcelHeaderFile(PathFileName, TargetText, TargetCell)
//Search for column header text in an Excel at a known column number 
//Argument (path and filename, text to search for, column number) { DDT.ExcelDriver((PathFileName), "Sheet1"); if ( (TargetText) == (DDT.CurrentDriver.ColumnName(TargetCell)) ) { Log.Message("Found "+(DDT.CurrentDriver.ColumnName(TargetCell))+" in header in column "+(TargetCell)); } else { Log.Warning("DID NOT Find "+(DDT.CurrentDriver.ColumnName(TargetCell))+" in header in column "+(TargetCell)); Project.Variables.FailFlag ++; } // Closes the driver DDT.CloseDriver(DDT.CurrentDriver.Name); }

 Thanks for any pointers.

BMD

2 ACCEPTED SOLUTIONS

Accepted Solutions
SmartBearDan
Staff

Re: DDT.ExcelDriver for .xlsx not .xls

Hello BMD,

 

You may need to download the Microsoft Office data connectivity driver to access the newer XLSX format.

 

2007 Office System Driver: Data Connectivity Components

 

More details on this in the TestComplete help here:

 

Using Excel Files as Data Storages

 

-Dan

View solution in original post

HKosova
Moderator

Re: DDT.ExcelDriver for .xlsx not .xls

You probably need to change

DDT.ExcelDriver((PathFileName), "Sheet1");

to

DDT.ExcelDriver((PathFileName), "Sheet1", true);

in order to use .xlsx instead of .xls.


Helen Kosova
SmartBear Documentation Team Lead
________________________
Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️

View solution in original post

7 REPLIES 7
SmartBearDan
Staff

Re: DDT.ExcelDriver for .xlsx not .xls

Hello BMD,

 

You may need to download the Microsoft Office data connectivity driver to access the newer XLSX format.

 

2007 Office System Driver: Data Connectivity Components

 

More details on this in the TestComplete help here:

 

Using Excel Files as Data Storages

 

-Dan

View solution in original post

BMD
Contributor

Re: DDT.ExcelDriver for .xlsx not .xls

Thanks Dan,

I have requested the newer driver, be downloaded.  Is this included in TC10.6 we are upgrading to from 10.3 very soon.

SmartBearDan
Staff

Re: DDT.ExcelDriver for .xlsx not .xls

Hi BMD,

 

That driver isn't part of TestComplete, it's a Windows driver that lets any program treat newer Excel files as a database.  So it's not included as part of the installation of TestComplete, but it is available free from Microsoft and we have a link to it from the support article.

 

-Dan

HKosova
Moderator

Re: DDT.ExcelDriver for .xlsx not .xls

You probably need to change

DDT.ExcelDriver((PathFileName), "Sheet1");

to

DDT.ExcelDriver((PathFileName), "Sheet1", true);

in order to use .xlsx instead of .xls.


Helen Kosova
SmartBear Documentation Team Lead
________________________
Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️

View solution in original post

sbeach
Contributor

Re: DDT.ExcelDriver for .xlsx not .xls

WOW this is tough.

 

It's simply not working.

It stops at the first line.

I downloaded the MS driver and have it setup like this:

 

DDT.ExcelDriver("C:\\BillableAPIUsageReport.xlsx","BillableAPIUsageReport",true)
while (! DDT.CurrentDriver.EOF())
{
//Gets a value from the storage and posts it to the log
Log.Message(DDT.CurrentDriver.Value(0));
DDT.CurrentDriver.Next();
}

// Closes the driver
DDT.CloseDriver(DDT.CurrentDriver.Name);

 

The error i get is:

There was an error on this page.Error description: External table is not in the expected format 14:38:03 Normal

 

This works: DDT.ExcelDriver("C:\\BillableAPIUsageReport.xls","BillableAPIUsageReport",true)

 

I've downloaded the driver, ran as admin.

Restarted pc

Had TC closed when i installed.

Version 11.20.1491.7

 

Is anyone having any luck with xlsx and can 'show me the way' please?


Thanks

Steph Beach

Certica Solutions.

djadhav
Regular Contributor

Re: DDT.ExcelDriver for .xlsx not .xls

This is one workaround. If you keep the .xlsx file open while running the tests it should run fine.

Worked for me. Check if it works for you as well.

sbeach
Contributor

Re: DDT.ExcelDriver for .xlsx not .xls

Thank you for your help.

That did work.

Then I decided to see if I could get it to fail again so i closed the xlsx file.

It still worked.

 

The only thing I did differently was to allow a new windows update this morning and that did a full restart.

So for anyone else, if you are in my situation, try a full power off and on.

Who knows with Microsoft.

 

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