{"id":70493,"date":"2005-02-07T13:52:00","date_gmt":"2005-02-07T13:52:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/02\/07\/how-can-i-password-protect-an-excel-spreadsheet\/"},"modified":"2005-02-07T13:52:00","modified_gmt":"2005-02-07T13:52:00","slug":"how-can-i-password-protect-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-password-protect-an-excel-spreadsheet\/","title":{"rendered":"How Can I Password-Protect an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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\u2019t show us an example of this. How can I use a script to password-protect a spreadsheet?

— MC<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, MC. Well, as we hinted at in the previous column<\/A> you mentioned, any time you call the SaveAs method you can include password-protection as an optional parameter. To demonstrate, here\u2019s 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<\/B>:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nobjExcel.DisplayAlerts = FALSE<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.Cells(1, 1).Value = Now\nobjWorkbook.SaveAs “C:\\Scripts\\Test.xls”,,”%reTG54w”\nobjExcel.Quit\n<\/PRE>\n
If you\u2019ve done any scripting with Microsoft Excel, this is about as simple a script as you\u2019ll ever write. The only \u201cgotcha\u201d to be aware of occurs in this line of code, where you actually save the file and password-protect it:<\/P>
objWorkbook.SaveAs “C:\\Scripts\\Test.xls”,,”%reTG54w”\n<\/PRE>\n
Admittedly, there\u2019s nothing fancy about this line of code; you just have to make sure the password (%reTG54w<\/B>) is the third<\/I> parameter passed to the SaveAs method. The first parameter is, of course, the file name. The second parameter is the file format. Because we\u2019re using the default format, we don\u2019t need to set a value for the second parameter; however, we do<\/I> need to include a placeholder for that parameter. That\u2019s 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<\/I> a value for the second parameter. By including this placeholder, the password becomes the third parameter, which is exactly what we want.<\/P>\n
After you run this script, try to open the file C:\\Scripts\\Test.xls; you\u2019ll 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\u2019t allow you to bypass the password protection.) But can\u2019t you specify the password when you open the file? Of course you can; that\u2019s what happens with this script:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nobjExcel.DisplayAlerts = FALSE<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”,,,,”%reTG54w”)\n<\/PRE>\n
Note that when opening a spreadsheet the password has to be the fifth<\/I> 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\u2019s 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\u2019d insert ten commas. And so on.<\/P>\n
But what if you decide later on to remove<\/I> the password? No problem: simply open the file and set the value of the Password<\/B> property to an empty string. Here\u2019s 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.<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nobjExcel.DisplayAlerts = FALSE<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”,,,,”%reTG54w”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorkbook.Password = “”<\/p>\n
objWorkbook.SaveAs “C:\\Scripts\\Test.xls”\nobjExcel.Quit\n<\/PRE>\n
What if you didn\u2019t 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\u2019re also aware that \u201cConsistency is the hobgoblin of little minds.\u201d But, hey, what size minds would you expect<\/I> the Scripting Guys to have?!?<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019t show us an example of this. How can I use a script to […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,5],"class_list":["post-70493","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019t show us an example of this. How can I use a script to […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/70493","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=70493"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/70493\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=70493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=70493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=70493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}