serbach
New Member
- Joined
- May 22, 2018
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
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)
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)
