How can I tell if a workbook is password-protected using VBA without actually opening it?

[serbach]

Our company has > 65,000 workbooks on our shared file server. I'm working on an Excel VBA routine that documents ODBC and Workbook Connections, external Excel Links, Power Queries, etc., for each workbook and write it all out to a text file. It actually works pretty darned well... except when it comes to workbooks with passwords.

As I loop through the workbooks in a given folder, I hope to be able to record that a particular workbook is password-protected and skip to the next file. However, the VBA routine has to pause while the Password dialog is displayed. There does not seem to be any way in VBA to tell when that dialog is on-screen and, more importantly, to Cancel it and move on to the next file.

I use PowerShell quite a bit. That's how I got the long list of all the Excel files on our shared drive in the first place. I found from my research that, apparently, if you examine the file metadata in Windows File Explorer (Properties / Details), a password-protected Excel file does not have the "Program name" property set; whereas un-protected Excel files have "Microsoft Excel" as the Program name.

OK, so I wondered if there's a cmdlet in PowerShell that would read that particular property of a file. Unfortunately, no, there isn't. So I found a couple guys who wrote custom PowerShell functions that use Windows System.IO calls to extract all of the metadata from a file, which includes all the usual properties you see in File / Info / Properties, plus all of the items that PowerShell cmdlets ignore.

Well, the custom function enumerates the metadata items for a file one by one in a loop. Then one has to search through the values returned to see if a "ProgramName" property exists. If it doesn't then that means the file is password-protected. [Whew!] This will take a while to execute!

My question: does anyone know of a VBA way to tell if an Excel workbook is password-protected before it opens? Or at least Cancel opening the file if the password prompt appears?

Thank you,

Steve Erbach
Green Bay, WI
(crying in my beer that the Pack fell to the Niners)

 

[ranman256]

you must open to tell.

 

[Siddharth Rout]

Yes it is possible but takes much more code than a simple VBA code to open and check

 

[serbach]

Yes it is possible but takes much more code than a simple VBA code to open and check

Right, I've experienced it!

But your reply suggests that one could "open and check"... or am I not understanding you? When I open a password-protected workbook in a VBA For Next loop on a folder full of workbooks, I get the Password dialog box. If I could detect that Excel was waiting for me to enter a password and somehow Cancel the dialog, I might be able to continue opening other workbooks. Do you know of a way to detect the Password prompt?

Thank you,

Steve Erbach
Green Bay, WI

 

[Siddharth Rout]

So I see you have couple of questions

1. Can we tell if an Excel workbook is password-protected before it opens?
2. Can we cancel the dialog box after it appears?

The answer to both the questions is YES.

Let me test few things before I can confirm which would be the best way to go forward in your case.

 

[RoryA]

Try supplying password:="". You'll get an error 1004 if the wb is password protected, which you can trap and move on.

 

[Siddharth Rout]

Try supplying password:="". You'll get an error 1004 if the wb is password protected, which you can trap and move on.

That is an excellent suggestion Rory

 

[RoryA]

Thank you Sid

 

[Siddharth Rout]

Thank you Sid

I had a different way to approach the problem but yours is definitely more elegant than mine!

 

[serbach]

Try supplying password:="". You'll get an error 1004 if the wb is password protected, which you can trap and move on.

Oooo! That's a good idea! Thank you for that!

Sincerely,

Steve Erbach
Green Bay, WI