I have an application that opens Excel files and loads data into an Access
database. This app runs unattended and so I have to take various precautions
to ensure that Excel doesn't produce any prompts that require human input.
One such situation is where a user decides to password protect the file. If I
try to open this in code without the password, Excel will pop up a dialog box
prompting for the password and everything will stop until somebody goes to
the server and answers it. To avoid this, I supply a password like so: set
wkb = Excel.Workbooks.Open(FileName:= " test.xls ", Password:="password") If
the file isn't password protected, Excel just ignores that parameter. If it
is password protected (and the password isn't "password") then I get a 1004
error and can email somebody to fix it and then skip to the next file. This
works fine for .xls files but there is a difference with .xlsx files. If the
file has a workbook protection password set (which it does), then the .xlsx
format file returns the 1004 error (no matter the value of the password,
including "password") whereas the equivalent file saved in legacy .xls format
does not fail. So to handle .xlsx files I will need to remove the password
parameter and risk the task hanging... Anyone have an idea about this or have
I discovered an Excel bug?