Linking to Excel Spreadsheets

[Huck]

Hi, hoping someone can help me with a ridiculous problem I am having in Access.
I have a 2003 database that links to an 2003 Excel Spreadhseet. The spreadsheet is passworded to modify but anyone can open it to read it but not modify it.
Inexplicably, I now get an error message in my database when I try to open the linked table. The message that comes up is "External table is not in the expected format".
The Excel table has been linked into the database for over a year without any problems but all of a sudden this message appears. If I open the spreadsheet, with the password, I am then able to open the table and/or run my queries in the database.
Nothing has changed with the spreadsheet, ie column names have remained unchanged, format has always been Excel 2003 etc so I am at a complete loss as to why this has started to happen.
Any feedback you can give would be much appreciated.
Thanks

 

[PaulO]

I think you've kinda answered your own question ... it would appear that it's the front-end password causing the problem where the spreadsheet is not in memory and Access tries to load/view it.

Why not remove the password from the spreadsheet and, instead, protect the sheet(s) within the workbook thus preventing undesired changes?

That way you get the best of both Worlds?

 

[Huck]

Thanks for the reply Paul but unfortunately removing the password is not an option. There are a number of worksheets & pivots in the spreadsheet and I only link to one of them (that contains the raw data). The spreadsheet has always been password protected in this way and has not caused issues until now.

 

[PaulO]

OK ... well ... I just created a simple spreadsheet in Excel 2003 with a password to modify only. Anyone can open it as read-only.

My Access 2003 database can link to and open that spreadsheet no problem.

So ... are you sure you don't also have a FILE password on the spreadsheet as when I added this additional security my access database couldn't read the file. The answer lies within the spreadsheet, rather than Access, I feel.

 

[Huck]

The password is definitely to modify only, not to open and this has been the case forever - that was why I was thinking Access may be the problem. I completly flummoxed now!

 

[PaulO]

... and there are no additional sheet or range protection passwords within the spreadsheet?

Has the password ever been changed?

 

[Huck]

No, password has remained the same and there are no other protections in the spreadsheet. It's a good one this isn't it..?

 

[PaulO]

It may just be worth setting up an additional link to the same spreadsheet in two different scenarios:-

1. using the same database
2. using a fresh (blank) database

This may then reveal whether Access is playing mind-games with you!?

 

[boblarson]

It is totally possible that corruption has started creeping in to the database. You might try importing everything into a new database and then see if the link works right from there.

 

[Huck]

Tried that and got the following message in the brand new database - "The wizard is unable to access information in the file [filename]. Please check that the file exists and is in the correct format."
Also tried it in the same database and got the original error message back.

Argghhhh!

 

[boblarson]

Tried that and got the following message in the brand new database - "The wizard is unable to access information in the file [filename]. Please check that the file exists and is in the correct format."
Also tried it in the same database and got the original error message back.

Argghhhh!

That does sound like corruption. Is this a split database (frontend / backend) with a copy of the frontend on each user's computer? If not, it should be.

You might try a Decompile and then compact and repair.

 

[Huck]

I am the only one that uses the database and there is no frontend/backend. I do a compact & repair every time I use it but have not come across Decompile before. What does this actually do?

 

[boblarson]

What does this actually do?

If you go to the link I gave it will give the explanation (so I don't have to try to recreate it )

 

[Huck]

Thanks Bob, I'll give that a try and see what happens.
Thanks Paul also for your help.