2 posts tagged “vba”

Check out this VBA enabled Microsoft Project Plan document that I recently developed.  It neatly solves a need of every project manager who uses Microsoft Project to track their project:  marking public holidays as non-working time in the project schedule.

My approach is to use the Outlook Holiday File that is installed on your computer when you install Microsoft Office Outlook.  It contains hundreds of religious and national holidays from countries around the world.  If you don't have Outlook installed, the wizard will take you to a page where you can learn about this file and download a copy to use as a source of holiday dates.

Best of all: its free!

Click here to visit my company website and check it out.

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

*** UPDATE ***
Microsoft Excel MVP Ron de Bruin has confirmed that this is an error in Excel 2007 and he has notified the Microsoft Excel development group.  Hopefully that will get the issue fixed!