MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Checking to see if a workbook is password-protected

Posted by Jon Gossard on June 02, 2000 4:49 PM

First off, many thanks to Ivan for pointing me in the right direction regarding my last question! And thanks for letting me know about Chip Pearsons' excellent site! Anyway, here is my new problem:

I now have a VBScript program that, when given a folder, will update all .xls files in that folder, as well as all .xls files in every subfolder, with custom footer macro code. The problem is that when the program opens a password-protected file, it stops and waits for the password to be entered. Does anyone know how to check to see if an Excel file is password-protected *before* actually opening it??? That way, I could just log the "failed" update and move on to the next file. I'm aware of the HasPassword property but by the time I have access to that property it's too late!

Any ideas are *greatly* appreciated!

Posted by Sherlock on June 15, 0100 10:00 AM

You cannot know that a file is password protected before opening the file in MSExcel.



Posted by Sherlock on June 09, 0100 12:41 PM

Hello Jon

As far as we know, in excel or in any application before opening the file you cannot know whether it is password protected or not.


Posted by Ivan Moala on June 02, 0100 10:32 PM

I don't know that you can get this ??
However you could try this;

1) before your routine to open the workbooks
have this;

On Error Resume next 'Then your open command
Workbooks.Open FileName:="D:\tt\PW test.xls", password:="ytlasqq"
If Err.Number > 0 Then (indicates error loading)

for password incorrect you get 1004 runtime error
Note: for Password put some outlandish text.

So what will happen is that if your routine tries
to open a password protected file the routine will
generate a runtime error AND normally HALT, but with
the On Error resume next it skips to the next line
and evaluates the error number. If > 0 ie. you
have an error (more then likely a password) then
it will goto what ever you want to do.

Also note that if the file is NOT Password protect
then providing one has no effect.