asked on

How can I connect to protected Excel workbook using Crystal Reports

I want to connect to my excel workbook and use it as the datasource from my Crystal Report but I also want the Excel document to be protected on Open. I tried entering the password in the "Database Password", and "Session Password" in the connection dialogue box in Crystal when trying to connect but I get an error that says

"Logon failed, Details: DAO Error Code: 0xcca, Source: DAO.Workspace, Description: External table is not in the expected format."

The workbook is Excel 2003 and I have selected Excel 3.0 as the database type..

Please let me know if you guys have any ideas on this!

Thanks in advance!

Will

I know this rudimentary, but have you set up the spreadsheet as a flat data table and named the range?

Additionally, you can try setting it up as an ODBC with the username and password through the control panel and administration in windows.

Mike McCracken

Try using Excel 8 as the format which corresponds to Excell 2007

According to this
Excel 2003 is version 11
http://en.wikipedia.org/wiki/Microsoft_Excel#History

mlmcc

James0628

Basically, what mlmcc said. Excel 3.0 is _not_ Excel 2003. I don't know what it is exactly (The Wiki page that mlmcc mentioned might say. I haven't looked), but I'm sure that it's a very old version. Excel 8.0 is at least from around the same era as Excel 2003 and it should work with 2003 files.

James

Jeremy Campbell

ASKER

Well I have tried a variety of things you guys have proposed but still no luck.

wkrasner, I do have the table setup and named it.

(I am able to connect to the excel document just fine if I remove the password on open.)

mlmcc, I tried connecting to the document using excel 8.0.

I'm attaching the different screenshots of what I have tried and the corresponding errors.

Jeremy Campbell

ASKER

Also, tried to connect using a File DSN in windows.. I can't a similar error as one of the errors above.

Will

So you created an ODBC in windows admin and the connection continues to Fail?

Will

can you post a sample xls configured the sameway

Jeremy Campbell

ASKER

Here is the dummied down version of it..

Note: I tried to create a fresh workbook and simply add a table to it and add the password and I was able to connect to it just fine..

Almost seems to be something specific with this workbook..

The password for the worksheet is test
Lead-Time-Tracking-Sheet-Revampe.xls

Will

you need to make the filed names the first row and it works

Will

sorry that was 'field names'

Jeremy Campbell

ASKER

Well I tried to delete the top rows so that the field names were first and I'm still getting the same error messages...

Jeremy Campbell

ASKER

Well I've given up on this. I removed the password when open from the file and added folder permissions so that only specified users can get into the folder containing the document.

Jeremy Campbell

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for SeyerIT's comment http:/Q_27263901.html#36437794

for the following reason:

Not exactly the solution I was looking for but it will work.

ASKER CERTIFIED SOLUTION

Will

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeremy Campbell

ASKER

At one point I was able to connect to this spreadsheet and it worked fine, password and all. Then it just quit working. I assumed someone did something to the settings somehow and that I should've still been able to connect to it.. At this point I'm assuming it was a glitch since I was able to copy it to a new spreadsheet, apply the password protection and then have it connect without a problem.

Jeremy Campbell

ASKER

Need to accept wkrasner's last solution:

"Have you tried recopying the data to a new spread sheet and rename the range and connect to the new ss."

This is a functional solution