How Can I Password-Protect an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! In a previous column, you told us a couple different ways to save Excel spreadsheets. In that column, you said that one of the things you could do with the SaveAs method was password-protect a spreadsheet. However, you didn’t show us an example of this. How can I use a script to password-protect a spreadsheet?

— MC

Hey, MC. Well, as we hinted at in the previous column you mentioned, any time you call the SaveAs method you can include password-protection as an optional parameter. To demonstrate, here’s a script that creates a new worksheet, writes the current date and time in cell A1, and then saves the worksheet as C:\Scripts\Test.xls. On top of that, it password-protects the spreadsheet, giving it the password %reTG54w:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1, 1).Value = Now
objWorkbook.SaveAs “C:\Scripts\Test.xls”,,”%reTG54w”
objExcel.Quit

If you’ve done any scripting with Microsoft Excel, this is about as simple a script as you’ll ever write. The only “gotcha” to be aware of occurs in this line of code, where you actually save the file and password-protect it:

objWorkbook.SaveAs “C:\Scripts\Test.xls”,,”%reTG54w”

Admittedly, there’s nothing fancy about this line of code; you just have to make sure the password (%reTG54w) is the third parameter passed to the SaveAs method. The first parameter is, of course, the file name. The second parameter is the file format. Because we’re using the default format, we don’t need to set a value for the second parameter; however, we do need to include a placeholder for that parameter. That’s what the back-to-back commas (,,) are for: they simply indicate that the value for the second parameter would go here if we actually had a value for the second parameter. By including this placeholder, the password becomes the third parameter, which is exactly what we want.

After you run this script, try to open the file C:\Scripts\Test.xls; you’ll be prompted to enter the password before the file will actually be opened. Incidentally, this will happen even if you try opening the file using a script. (Sorry, but using a script won’t allow you to bypass the password protection.) But can’t you specify the password when you open the file? Of course you can; that’s what happens with this script:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”,,,,”%reTG54w”)

Note that when opening a spreadsheet the password has to be the fifth parameter; thus we have the file name, three placeholders, and then the password. This can be a little confusing, to say the least, but here’s a rule of thumb: just put in one more comma than you have placeholders. In this example, we have three placeholders, so we insert four commas. If we had nine placeholders, we’d insert ten commas. And so on.

But what if you decide later on to remove the password? No problem: simply open the file and set the value of the Password property to an empty string. Here’s a script that does just that: it opens the spreadsheet, removes the password, and then uses the SaveAs method to re-save the file. After running this script, try to open this spreadsheet from within Windows Explorer; you should be able to do so without being prompted for a password.

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”,,,,”%reTG54w”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorkbook.Password = “”
objWorkbook.SaveAs “C:\Scripts\Test.xls”
objExcel.Quit

What if you didn’t want to remove the password, but merely wanted to change it? In that case, just set the Password property to the new password rather than to an empty string. And before you ask, yes, we could have used the Save method here rather than SaveAs. We stuck with SaveAs simply to be consistent with the previous script. We’re also aware that “Consistency is the hobgoblin of little minds.” But, hey, what size minds would you expect the Scripting Guys to have?!?

ScriptingGuy1

Follow