MS query on a password protected excel 2007 file

[dskjkk]

I am using excel 2007. I have an excel file that is the source for multiple Microsoft queries. This file is saved in .xlsx format.

Using Excel's feature (Data>From Other Sources>From Microsoft Query) I set up a DataSource connection (i.e. the dsn file) pointing to this excel source and specified the Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb). I then set up my query (i.e. the dqy file) using this data source.

My problem is this: the source file needs to be password protected. I set up a password on the source excel file (after the dsn and query files were created) BUT the query failed with this message: "ODBC Excel Driver Logon Failed>External table is not in the expected format." I had hoped the query would have prompted me for the password (to open the source file) before executing but it did not.

I then tried to set up the DataSource connection with the password already established on the source file. Excel would not let me create the dsn file on a password protected file (I got an error message of: "ODBC Excel Driver Logon Failed>External table is not in the expected format.")

Is there any way to run Microsoft Queries on a password protected excel 2007 source file? Please advise. Thanks.

 

[SkipVought]

I then tried to set up the DataSource connection with the password already established on the source file.

What connection string are you using. Use fake ID & PW.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Hi,

Use VBA to refresh your query. In the VBA code you must...

1) remove the password protection
2) refresh/execute the query
3) add the password protection

For VBA help post in forum707.

http://support.microsoft.com/kb/211378

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dskjkk]

Thanks Skip...
I was hoping that the ability to edit / disable the password in the Datasource connection could be done through the existing interface's check boxes. Guess I'll give the VBA approach a try. Thanks.