How Can I Remove the Password When Opening an Excel Spreadsheet?

How Can I Remove the Password When Opening an Excel Spreadsheet?

  • Comments 12
  • Likes
Hey, Scripting Guy! Question

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

-- JE

SpacerHey, Scripting Guy! AnswerScript Center

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!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • thanks for your post, i think remove the password using scripts is a little difficult for freshman, if anyone forgot the excel password can try some excel password recovery tool, which can recover Microsoft Excel passwords, enabling you to easily view the workbooks and sheets for the information.

    Related Source:

    lost-password.net/.../excelpasswordrecovery.html

  • Thanks for this, but I already knew how to 'Open' a password protected excel file using VB. However I am trying to OPEN and the REMOVE the password set on the worksheet permanently and programmatically. Amy ideas how to do this. I already know the password for the sheet, so I'm not trying to hack into it. Just want the thing removed so i can import the worksheet easily afterwards using SSIS.

    Cheers

    Eddy

  • I wrote this excel version for fun and for free – no installation required. Just open this workbook (www.mann-jones.com/ExcelCracker.aspx) , click Quick Run , navigate to your sheet and voila … workbook and worksheet unprotected.

  • damn it won't work if you lost a password to OPEN spreadsheet!! tried everything but could not do it on my own. had to pay $20 to remove it with online service called www.password-find com. it helped in no time fortunately

  • This script is fantastic!  Any suggestions on how to extend it to work with an entire directory of excel files with the same password required to open them?

  • You can perform vba password recovery process via this link http://gallery.technet.microsoft.com/VBA-Password-Recovery-eac07070

  • As you VBA Password Recovery code is strictly bound with syntax so it is not easy to recover VBA password via coding. So i suggest you to use VBA Password Recovery Software to recover VBA Password to get more information you can go for this link http://gallery.technet.microsoft.com/VBA-Password-Recovery-eac07070

  • There are many excel password recovery software are available in online market but I recommend you this tool on my personal experience just use excel password remover software that perfectly recover lost excel password and make it unprotected from such excel versions 97/2000/2002/2003/2007/2010. Read More........... http://www.msexcelpasswordrecovery.com/ http://www.excelpasswordrecovery.net/

  • Firstly you recover lost Excel file password using Excel password recovery tool and after recovering password you can easily remove password form your Excel files. Using this software you can re-open your Excel files.
    Get more here:-
    http://www.excelpasswordrecoverysoftware.org/
    http://www.excelpasswordunlocker.org/

  • I will add two things about Password to Open.

    If the the file is in Excel 97-2003 it is best to use rainbow table-based services. Password complexity is not important to them - they will decode any file. For example http://accessback.com/
    If the file is in Excel 2007-2013, it is best to use programs that use video cards, preferrably - AMD. Then the password scan speed will increase manyfold. For example http://passwordrecoverytools.com/

  • Here is a best way to remove VBA password that i used earlier. Actually i am not familiar with vba programming language. Then i decide to use VBA Password Remover Software which helps me to unlock my VBA password without any coding. For more information you can go for this link http://www.en.vbapasswordremover.org/

  • Download best Excel password recovery program that gives you best and safe solution for cracking Excel file password. Deprived of any harm all users can nicely remove Excel password and open Excel password correctly.

    Read More Here,

    http://www.excelunlocker.com/

    http://www.removeexcelpassword.com/