Check whether Excel file is Password protected

Question

I am trying to open an Excel (xlsm) file via VBA. It may or may not be protected with a (known) password. I am using this code:

On Error Resume Next
Workbooks.Open filename, Password:=user_entered_pw
opened = (Err.Number=0)
On Error Goto 0

Now, this works fine if the workbook has a password. But if it is unprotected, it can NOT be opened. Apparently this is a bug in XL2007 if there is also workbook structure protection active. (http://vbaadventures.blogspot.com/2009/01/possible-error-in-excel-2007.html). On old XL2003, supplying a password would open both unprotected and password protected file.

I tried:

Workbooks.Open filename, Password:=user_entered_pw
If (Err.Number <> 0) Then workbooks.open filename

This works for unprotected and protected file. However if the user enters a wrong password it runs into the second line and pops up the "enter password" prompt, which I do not want.

How to get around this?

Answer 1

1. Use 1004 error handling exclusively for this particular problem, and On Error Resume Next for everything else in the Sub.

2. Above the statement Workbooks.Open filename, Password:=user_entered_pw also add another line statement without a password argument.

3. If those (or their combination) don't work, try scouring the .xlsm flat code in Notepad for any hint that a PW protected file differs at that level from one without. Use that info in a pre-opening function.

Answer 2

For the record - the reason was indeed the structure protection of the workbook I was opening. I could circumvent the problem by disabling structure protection, and re-protecting in Workbook_Open() (in the protected workbook).

With structure protection inactive, Workbooks.Open with password does not fail even when there is no password.

Since I am opening via a VBA method, the VBA code is already trusted, meaning the Workbook_Open method will surely be called.