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

— JE

Hey, JE. You know, it’s finally happened: after two-and-a-half years of writing this column we’ve finally run out of things to talk about. The “heartbeat sensor” that can tell you whether someone is hiding in the backseat of your car? We talked about that a week or so ago. The exploits of baseball legend Frank “Home Run” Baker? Old news; we covered that a long time ago. The origins of Grandparents Day? You know what they say: been there, done that.

In fact, as near as we can tell we’ve now covered every subject we could possibly cover. (Well, we suppose we could go over that; however, the Scripting Editor doesn’t really like us talking about her broomstick. Besides, we’ve already mentioned that in a column.) The truth is, there’s really nothing left for us to talk about. Try us again next week, or maybe next month; maybe we’ll have something for you by then.

Although, come to think of, there is one thing we haven’t tried: we could simply answer the question without rambling on about, say, Scripting Guys dying while riding an exercise bike. That’s a bit out of character for this column, but we suppose could give it a try. Granted, we wouldn’t 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:

Set objExcel = CreateObject(“Excel.Application”) 

objExcel.Visible = TRUE 
objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”,,,,”L$6tg4HHE”)
objWorkbook.Password = “”
objWorkbook.SaveAs “C:\Scripts\Test.xls”

You know which commercial on TV drives us absolutely crazy? It’s the one where – oh, sorry; force of habit. We said we were going to just talk about scripting, didn’t we? OK. Well, in that case, what we have here is an Excel spreadsheet (C:\Scripts\Test.xls) that has been password-protected. (We’re assuming only the password to open the file has been set; if the password to modify the file has also been set you’ll still need to enter that one.) In order to open the spreadsheet you need to supply the password: L$6tg4HHE. (And yes, that is the Scripting Editor’s 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’s find out.

First things first, however. So what’s the deal with the weather in Cleveland, huh? All that snow, in April? 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–

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 object. We then set the Visible property to True and the DisplayAlerts property to False. Setting the Visible property to True simply ensures that we’ll 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 want to overwrite that existing copy. We don’t want to deal with that message box, and setting DisplayAlerts to False ensures that we don’t have to.

Once Excel is up and running (and properly configured) we next use this line of code to open the file C:\Scripts\Test.xls:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”,,,,”L$6tg4HHE”)

Notice what we’re doing here. Here we’re calling the Workbooks.Open 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:

,,,, “L$6tg4HHE”

What’s the deal with that? Well, the Open method accepts a number of parameters, most of which are optional. We aren’t interested in parameters 2 through 4 (UpdateLinks, ReadOnly, and Format); however, we are interested in parameter 5 (Password). So then why don’t we just list the two parameters we’re interested in, like so:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”,”L$6tg4HHE”)

You’re absolutely right: with that line of code the password shows up in the slot reserved for parameter 2; as you might expect, that’s 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.

Boy, this explaining stuff is hard work. No wonder we don’t do it very often!

Assuming we’ve provided the correct password we’ll have now accomplished step 1 in our two-part process: we’ll have opened Test.xls. All that’s left now is to remove the password protection. To accomplish that feat we first set the value of the Workbook’s Password property to nothing:

objWorkbook.Password = “”

And then, to make sure that the change is applied and the password is removed, we call the SaveAs method, taking care to save the file under its existing file name and file path:

objWorkbook.SaveAs “C:\Scripts\Test.xls”

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’s your faith in the Scripting Guys?

OK, good point. But at least you’ll be able to open the spreadsheet without supplying a password.

So it looks like we’re done, doesn’t it? We have to admit that this was an interesting exercise; nevertheless, it’s probably just a one-time thing. After all, the Scripting Guys all lead such rich and fulfilling lives that something interesting – and worth talking about – is bound to happen to them sooner or later.

OK, another good point. On the other hand, the fact that we don’t have anything worth talking about has never stopped us before, has it? Tune it tomorrow for another thrilling edition of Hey, Scripting Guy!