{"id":65093,"date":"2007-04-12T21:05:00","date_gmt":"2007-04-12T21:05:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/04\/12\/how-can-i-remove-the-password-when-opening-an-excel-spreadsheet\/"},"modified":"2007-04-12T21:05:00","modified_gmt":"2007-04-12T21:05:00","slug":"how-can-i-remove-the-password-when-opening-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-remove-the-password-when-opening-an-excel-spreadsheet\/","title":{"rendered":"How Can I Remove the Password When Opening an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I remove the password when opening an Excel spreadsheet?

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

Hey, JE. You know, it\u2019s finally happened: after two-and-a-half years of writing this column we\u2019ve finally run out of things to talk about. The \u201cheartbeat sensor\u201d that can tell you whether someone is hiding in the backseat of your car? We talked about that<\/B><\/A> a week or so ago. The exploits of baseball legend Frank \u201cHome Run\u201d Baker? Old news; we covered that<\/B><\/A> a long time ago. The origins of Grandparents Day? You know what they say: been there, done that<\/B><\/A>. <\/P>\n

In fact, as near as we can tell we\u2019ve now covered every subject we could possibly cover. (Well, we suppose we could<\/I> go over that; however, the Scripting Editor doesn\u2019t really like us talking about her broomstick. Besides, we\u2019ve already mentioned that in a column<\/B><\/A>.) The truth is, there\u2019s really nothing left for us to talk about. Try us again next week, or maybe next month; maybe we\u2019ll have something for you by then.<\/P>\n

Although, come to think of, there is<\/I> one thing we haven\u2019t tried: we could simply answer the question without rambling on about, say, Scripting Guys dying<\/B><\/A> while riding an exercise bike. That\u2019s a bit out of character for this column, but we suppose could give it a try. Granted, we wouldn\u2019t want to make a habit of that; after all, why would you read a daily scripting column if all the column ever talked about was scripting-related stuff? But what the heck; the Scripting Guys will try anything once:<\/P>

Set objExcel = CreateObject(“Excel.Application”) <\/p>\n
objExcel.Visible = TRUE \nobjExcel.DisplayAlerts = FALSE<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”,,,,”L$6tg4HHE”)<\/p>\n
objWorkbook.Password = “”\nobjWorkbook.SaveAs “C:\\Scripts\\Test.xls”\n<\/PRE>\n
You know which commercial on TV drives us absolutely crazy? It\u2019s the one where \u2013 oh, sorry; force of habit. We said we were going to just talk about scripting, didn\u2019t we? OK. Well, in that case, what we have here is an Excel spreadsheet (C:\\Scripts\\Test.xls) that has been password-protected. (We\u2019re assuming only the password to open the file has been set; if the password to modify the file has also been set you\u2019ll still need to enter that one.) In order to open the spreadsheet you need to supply the password: L$6tg4HHE. (And yes, that is<\/I> the Scripting Editor\u2019s middle name. Thanks for noticing!) We need a script that can do two things: open the spreadsheet (which will require us to supply the password), and then remove the password protection. Can we do that? Let\u2019s find out.<\/P>\n
First things first, however. So what\u2019s the deal with the weather in Cleveland, huh? All that snow, in April<\/I>? Yuck. On the bright side, however, having 4 games snowed out helped the Seattle Mariners actually go several days without a loss; needless to say, as soon as they had to start playing again they got hammered by\u2013<\/P>\n
Sorry; this is harder than we thought it would be. Time to focus on the task at hand. As you can see, we begin by creating an instance of the Excel.Application<\/B> object. We then set the Visible<\/B> property to True and the DisplayAlerts<\/B> property to False. Setting the Visible property to True simply ensures that we\u2019ll be able to view our instance of Excel on screen. Meanwhile, setting DisplayAlerts to False suppresses the display of Excel message boxes and warnings while the script is running. Do we really need to do that? Well, maybe not. However, when we go to save the spreadsheet Excel will, by default, notify us that a copy of Test.xls already exists and ask us if we really<\/I> want to overwrite that existing copy. We don\u2019t want to deal with that message box, and setting DisplayAlerts to False ensures that we don\u2019t have to.<\/P>\n
Once Excel is up and running (and properly configured) we next use this line of code to open the file C:\\Scripts\\Test.xls:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”,,,,”L$6tg4HHE”)\n<\/PRE>\n
Notice what we\u2019re doing here. Here we\u2019re calling the Workbooks.Open<\/B> method, followed by the complete path to the file we want to open; that should be pretty straightforward. We then have a series of commas with nothing in between them, followed by the spreadsheet password:<\/P>
,,,, “L$6tg4HHE”\n<\/PRE>\n
What\u2019s the deal with that? Well, the Open method accepts a number of parameters, most of which are optional. We aren\u2019t interested in parameters 2 through 4 (UpdateLinks, ReadOnly, and Format); however, we are<\/I> interested in parameter 5 (Password). So then why don\u2019t we just list the two parameters we\u2019re interested in, like so:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”,”L$6tg4HHE”)\n<\/PRE>\n
You\u2019re absolutely right: with that<\/I> line of code the password shows up in the slot reserved for parameter 2; as you might expect, that\u2019s not going to work. Instead, the password has to be the fifth parameter provided to the Open method. And the only way to make sure the password shows up as the fifth parameter is to include blank parameters between the file path and the password. Hence a set of commas with nothing in between.<\/P>\n
Boy, this explaining stuff is hard work. No wonder we don\u2019t do it very often!<\/P>\n
Assuming we\u2019ve provided the correct password we\u2019ll have now accomplished step 1 in our two-part process: we\u2019ll have opened Test.xls. All that\u2019s left now is to remove the password protection. To accomplish that<\/I> feat we first set the value of the Workbook\u2019s Password<\/B> property to nothing:<\/P>
objWorkbook.Password = “”\n<\/PRE>\n
And then, to make sure that the change is applied and the password is removed, we call the SaveAs<\/B> method, taking care to save the file under its existing file name and file path:<\/P>
objWorkbook.SaveAs “C:\\Scripts\\Test.xls”\n<\/PRE>\n
What will that do for us? Well, suppose we go ahead and close Test.xls, then try to reopen it. Will we need to supply the password in order to open the spreadsheet? Come on; where\u2019s your faith in the Scripting Guys?<\/P>\n
OK, good point. But at least you\u2019ll be able to open the spreadsheet without supplying a password.<\/P>\n
So it looks like we\u2019re done, doesn\u2019t it? We have to admit that this was<\/I> an interesting exercise; nevertheless, it\u2019s probably just a one-time thing. After all, the Scripting Guys all lead such rich and fulfilling lives that something interesting \u2013 and worth talking about \u2013 is bound<\/I> to happen to them sooner or later. <\/P>\n
OK, another good point. On the other hand, the fact that we don\u2019t have anything worth talking about has never stopped us before, has it? Tune it tomorrow for another thrilling edition of Hey, Scripting Guy!<\/I><\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I remove the password when opening an Excel spreadsheet?— JE Hey, JE. You know, it\u2019s finally happened: after two-and-a-half years of writing this column we\u2019ve finally run out of things to talk about. The \u201cheartbeat sensor\u201d that can tell you whether someone is hiding in the backseat of your car? […]<\/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-65093","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! How can I remove the password when opening an Excel spreadsheet?— JE Hey, JE. You know, it\u2019s finally happened: after two-and-a-half years of writing this column we\u2019ve finally run out of things to talk about. The \u201cheartbeat sensor\u201d that can tell you whether someone is hiding in the backseat of your car? […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65093","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=65093"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65093\/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=65093"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65093"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65093"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}