DDT.ExcelDriver for .xlsx not .xls

[BMD]

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

[SmartBearDan · Accepted solution]

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

[BMD]

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]

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 · Accepted solution]

You probably need to change

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

to

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

in order to use .xlsx instead of .xls.

[sbeach]

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]

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]

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.

Dhadhave iss excellent for helping me so fast: Thank you again