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 = FALSESet 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 = FALSESet 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 = FALSESet 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?!?
I’m trying to execute this but I don’t seem to be doin it properly, how can I execute it to pick up an excel file and password protect it?
#Creation of the Excel File:
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$Workbook = $Excel.Workbooks.Add()
$Sheet1 = $Workbook.Worksheets.Item(1)
$Sheet1.Name = “MyData”
#Protecting it with the password and saving it in the same time :
$Workbook.SaveAs(“C:\MyFile”,51,”MyWantedPassword”)
#51 is the value for the default worsheet (xlsx)
#try to open it manually to see the results( you should enter “MyWantedPassword” as a password), the create file is under the C drive
#Cheers