Wednesday, January 28, 2009

Possible Error in Excel 2007 Workbooks.Open method

I have discovered a disturbing "feature" in Excel 2007 VBA in the way that the Workbooks.Open method handles xlsx format files versus the legacy xls format file.

If a file has a "workbook protection" password set, then the following statement will succeed for an xls file but will fail for an xlsx file with run time error "1004: The password you supplied is not correct."

set wkb = Application.Workbooks.Open("file.???", , , , "password")

Why supply a password in this call? I am running Excel as an unattended automation server to load data captured in workbooks submitted from a website into an Access database. Sometimes the users apply a "file open" password which will cause Excel to display a dialog box and wait for someone to supply the password if no password is supplied as an argument to the Open method - very bad! On the other hand, if the password argument is present and is incorrect then the above 1004 error is generated and I can then deal with it in code. If there isn't a "file open" password on the file, the Open method ignores the password argument.

However, all my files have a "workbook protection" password set and some users submit the files in the new xlsx format. This causes me to discard the file when it should be able to be opened. By the way, even if the "workbook protection" password is "password" it generates this error. And if there is a "workbook protection" password on an xlsx file and no password argument is provided to the Open method, it opens the file without any trouble.

So looks like Microsoft have messed up the Workbooks Open method in the case where:
  1. The file has a "workbook protection" password
  2. The file is in the new xlsx format
  3. A password argument is supplied in the call to the Workbooks.Open method
If you know someone who can alert Microsoft to this, please let them or me know!

*** UPDATE ***

Prominent Excel MVP Ron de Bruin has confirmed this is an error and has notified the Microsoft Excel team. Stay tuned for additional developments.

*** UPDATE ***

5 comments:

JP said...

I was not able to duplicate your problem. I took an Excel 2007 workbook (xlsx) and saved it with the open password "password". I used the following macro code:

Dim wkb As Excel.Workbook
Set wkb = Application.Workbooks.Open("mywkb.xlsx", , , , "password")

The workbook opened successfully. Do you have any more information that could help me duplicate your issue?

Wazza said...

JP,

Reread my post more carefully.

The problem occurs when the file has a "workbook protection" password (and no "file open" password).

Try that with an xls and an xlsx and you'll see that the xlsx fails with 1004.

You might say "well, don't include the password argument" but the reason I have to do that is in case the user has saved the file with one. If I don't include that argument, Excel will halt waiting for a password to be entered into a dialog box (and this runs unattended, remember). With the password argument supplied, I get a 1004 error and can put the file aside to be dealt with later. I'd rather not have this happen for every xlsx file however.

Cheers,
Wazza

Wazza said...

Prominent Excel MVP Ron de Bruin has confirmed this is an error and has notified the Microsoft Excel team. Stay tuned for additional developments.

Joe Pence said...

I'm getting similar issues when trying to call Workbook.Open on a password-protected file on a network. It works fine on a local file, but won't accept the password on a shared drive.

K_Dog said...

Have you been able to figure this out? It has been driving me nuts. Thanks.