<< Click here to Goto HomePage

     
     

Monday, September 08, 2008

How to easily Unprotect/Remove Password from a Protected Excel Worksheet

You may already know how easy it is to protect Microsoft Office Excel worksheets and workbooks from unauthorized changes with the help of a password. But, what to do if you can’t remember the password you used for protecting OR you downloaded an excel from the internet and would like to have a look at the formula's or the embeded information - but all that data might be inaccessible due to the password protection.

I have a very simple method by which you can easily recover the password and also un-protect the worksheet or workbook. Please note : this method will only remove the protection from the excel file and not the password used to open the excel file itself.

Open the excel file which is password protected and goto Macro's (I am using Office 2007 so the menu's maybe different View >> Macros). Click on "Record Macro >> OK" and then click on "Stop Recording" from the same menu. Now goto "View Macros", you will find a Macro with a default name E.g. Macro1 - Select the macro name and click on Edit. Now a Visual Basic Editor opens up. Re-place the default code and Paste the below code.


Sub Macro1()
'
' Breaks worksheet and workbook structure passwords. Jason S

' probably originator of base code algorithm modified for coverage

' of workbook structure / windows passwords and for multiple passwords

' Jason S http://jsbi.blogspot.com

' Reveals hashed passwords NOT original passwords

Const DBLSPACE As String = vbNewLine & vbNewLine

Const AUTHORS As String = DBLSPACE & vbNewLine & "Adapted from Bob McCormick base code by" & "Jason S http://jsbi.blogspot.com"

Const HEADER As String = "AllInternalPasswords User Message"

Const VERSION As String = DBLSPACE & "Version 1.0 8 Sep 2008"

Const REPBACK As String = DBLSPACE & "Please report failure to jasonblr@gmail.com "

Const ALLCLEAR As String = DBLSPACE & "The workbook should be cleared"

Const MSGNOPWORDS1 As String = "There were no passwords on " & AUTHORS & VERSION

Const MSGNOPWORDS2 As String = "There was no protection to " & "workbook structure or windows." & DBLSPACE


Const MSGTAKETIME As String = "After pressing OK button this " & "will take some time." & DBLSPACE & "Amount of time " & "depends on how many different passwords, the "



Const MSGPWORDFOUND1 As String = "You had a Worksheet " & "Structure or Windows Password set." & DBLSPACE & "The password found was: " & DBLSPACE & "$$" & DBLSPACE & "Note it down for potential future use in other workbooks by " & "the same person who set this password." & DBLSPACE & "Now to check and clear other passwords." & AUTHORS & VERSION

Const MSGPWORDFOUND2 As String = "You had a Worksheet " & "password set." & DBLSPACE & "The password found was: " & DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & "future use in other workbooks by same person who " & "set this password." & DBLSPACE & "Now to check and clear " & "other passwords." & AUTHORS & VERSION

Const MSGONLYONE As String = "Only structure / windows " & "protected with the password that was just found." & ALLCLEAR & AUTHORS & VERSION & REPBACK

Dim w1 As Worksheet, w2 As Worksheet

Dim i As Integer, j As Integer, k As Integer, l As Integer

Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer

Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer

Dim PWord1 As String

Dim ShTag As Boolean, WinTag As Boolean

Application.ScreenUpdating = False

With ActiveWorkbook

WinTag = .ProtectStructure Or .ProtectWindows

End With

ShTag = False

For Each w1 In Worksheets

ShTag = ShTag Or w1.ProtectContents

Next w1

If Not ShTag And Not WinTag Then

MsgBox MSGNOPWORDS1, vbInformation, HEADER

Exit Sub

End If

MsgBox MSGTAKETIME, vbInformation, HEADER

If Not WinTag Then

MsgBox MSGNOPWORDS2, vbInformation, HEADER

Else

On Error Resume Next

Do 'dummy do loop

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

With ActiveWorkbook

.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

If .ProtectStructure = False And .ProtectWindows = False Then

PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

MsgBox Application.Substitute(MSGPWORDFOUND1, "$$", PWord1), vbInformation, HEADER

Exit Do 'Bypass all for...nexts

End If

End With

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

Loop Until True

On Error GoTo 0

End If

If WinTag And Not ShTag Then

MsgBox MSGONLYONE, vbInformation, HEADER

Exit Sub

End If

On Error Resume Next

For Each w1 In Worksheets

'Attempt clearance with PWord1

w1.Unprotect PWord1

Next w1

On Error GoTo 0

ShTag = False

For Each w1 In Worksheets

'Checks for all clear ShTag triggered to 1 if not.

ShTag = ShTag Or w1.ProtectContents

Next w1

If ShTag Then

For Each w1 In Worksheets

With w1

If .ProtectContents Then

On Error Resume Next

Do 'Dummy do loop

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

If Not .ProtectContents Then

PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

MsgBox Application.Substitute(MSGPWORDFOUND2, "$$", PWord1), vbInformation, HEADER

'leverage finding Pword by trying on other sheets

For Each w2 In Worksheets

w2.Unprotect PWord1

Next w2

Exit Do 'Bypass all for...nexts

End If

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

Loop Until True

On Error GoTo 0

End If

End With

Next w1

End If

MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
'
End Sub



Finally, run the Macro(View >> Macros >> View Macros >> Run). You will get the password of the protected workbook and worksheet in Excel. I have tested the above in Microsoft Office Excel XP / 2003 / 2007

61 comments:

  1. Joeri Van de Gender said,

    THX, removing the protection was really easy and the code works fine.

    on 9/16/2008 5:29 PM


  2. bluecon said,

    ok, i think this is a easy way to know the password then i have the password and opened it bevor, or?

    how can i recover the password of the excel worksheet or excel file?

    when i open the password protected excel file, he asks me "please enter the password" but i can't open the macro area.

    can you help me?

    on 9/27/2008 9:14 PM


  3. nycnyc said,

    Perfect! Thanks. - nyc

    on 11/09/2008 7:13 PM


  4. LuisP said,

    I just unprotected a sheet in Excel 2007, and it worked very well.
    Thanks.
    Luis P.

    on 11/14/2008 4:39 PM


  5. Anonymous said,

    Awesome.

    Worked perfectly.

    Thanks very much.

    on 4/13/2009 8:53 PM


  6. Tanya said,

    FANTASTIC! Great work

    on 5/25/2009 11:15 PM


  7. Anonymous said,

    Fab thank you worked a treat I've bookmarked the page...

    on 6/05/2009 1:51 PM


  8. JazzyD said,

    I ran the macro, but it's still running. How long does it usually take?

    on 6/06/2009 11:41 AM


  9. JASON S said,

    it should take a few secs only. Maybe a minute or two.

    Cheers!!
    Jason

    on 6/06/2009 3:26 PM


  10. Anonymous said,

    Absolutely smashing stuff!

    You're a life saver - thanks for this!

    on 6/11/2009 5:20 PM


  11. Anonymous said,

    You are the bomb diggity dude! You Rock! Thank you so much for putting this type of assistance online. This has made my day today and everyday from now on...so no matter what the password may be - I'll be able to change or remove it. Thanks again, Dorothy

    on 6/12/2009 1:32 AM


  12. Anonymous said,

    thank you so much!!! Worked like a charm! Brilliant! :)

    on 7/20/2009 10:19 PM


  13. Anonymous said,

    Works like a charm in Excel 2000! Thanks!!!

    on 7/28/2009 12:25 AM


  14. Aj said,

    Wow!! It worked in Excel 2007. You did a great job -- Thanx.

    on 8/01/2009 2:58 AM


  15. Mindy said,

    wow, this was ridiculously easy. worked like a charm on my first try! THANK YOU!

    on 10/07/2009 8:12 PM


  16. Anonymous said,

    Great instructions. Thank you very much!

    on 10/28/2009 3:11 PM


  17. Anonymous said,

    Wow. After searching for something like this and not having much luck, I was skeptical that this would work for Excel 2007. Worked perfectly.

    on 11/14/2009 7:59 PM


  18. Anonymous said,

    awesome job Jason, this is exactly what i was searching for. I dint want some programs or add-ons.

    on 11/16/2009 5:02 PM


  19. Anonymous said,

    How can i recover the password of the excel worksheet or excel file?

    When I open the password protected excel file, it asks me "please enter the password" but i can't open the macro area.

    can you help me?

    on 11/16/2009 9:27 PM


  20. kiran said,

    worked perfectly. awsome.......

    thanxxxxx alot

    on 11/19/2009 12:10 PM


  21. Anonymous said,

    It worked nicely thanks! One thing I noticed for me it created a file in the XLSTART folder named PERSONAL.XLSB which ended up opening everytime after I ran the macro.
    I deleted the file from that startup folder and it seems to be resolved.

    on 11/22/2009 6:46 AM


  22. Anonymous said,

    It worked very well Jason, thanks a lot.

    on 11/22/2009 4:10 PM


  23. Anonymous said,

    Thanks a lot Jason. It worked very well in Excel 2007. Keep up the good work.

    on 11/22/2009 4:17 PM


  24. Janette said,

    Worked for me, too. Thanks so much for sharing this!

    on 12/01/2009 12:37 AM


  25. Anonymous said,

    fantastic, thank you so much for taking the time!

    on 12/18/2009 11:03 AM


  26. Anonymous said,

    You just saved my ass! Thank you Thank you Thank you.

    on 1/14/2010 11:29 PM


  27. Anonymous said,

    Hei Jason, could please tell us just for curiosity what does this Macro does practically?

    on 1/20/2010 1:59 AM


  28. Anonymous said,

    I'm also interested in understanding how does this Macro works internally, because of all the code I don't understand excatly, I'm just a beginner in programming.
    Thanks.

    on 1/20/2010 1:41 PM


  29. Jason H. said,

    Thank you for the code!!

    on 1/22/2010 4:15 AM


  30. Anonymous said,

    Bought software to do this did not work - used your code did work!

    Thanks,

    Hans

    on 2/16/2010 3:28 AM


  31. Deepak said,

    This is just excellent. Thaks for the wonderful stuff!!

    on 3/02/2010 7:09 PM


  32. Anonymous said,

    thank you. worked well.

    on 4/21/2010 7:04 PM


  33. Anonymous said,

    hey, just wanted to post to say thankyou

    no other google search actually worked and had me download stuff instead of write a macro.

    on 5/17/2010 6:43 PM


  34. Yashvant said,

    hey thx man,ur code works fine.

    on 5/19/2010 11:17 AM


  35. Anonymous said,

    Works really well! Thanks!

    on 5/27/2010 5:11 PM


  36. Anonymous said,

    Thanks a lot man really its great..

    on 6/01/2010 5:00 PM


  37. Fabian said,

    great stuff, worked like a cham

    on 6/03/2010 8:21 AM


  38. Anonymous said,

    Great; many thanks

    on 6/14/2010 7:49 PM


  39. Anonymous said,

    This worked great! Thanks!

    on 7/23/2010 11:16 PM


  40. Anonymous said,

    Jason, you are a gifted fellow! Worked perfectly and you have saved me re-doing my own work. Many Thanks!

    on 8/08/2010 10:29 PM


  41. Anonymous said,

    You are the best!!! Thanks... Ed

    on 8/20/2010 10:32 AM


  42. Anonymous said,

    You are the best!!! thanks for creating this very clever macro...
    Ed

    on 8/20/2010 10:33 AM


  43. ubeauty said,

    Great stuff, many thanks for circulating and sharing this information

    on 8/20/2010 1:34 PM


  44. Anonymous said,

    I'd forgotten my password for an Excel 2000 worksheet. Found this page through Google and decided to give it a try. Instructions were easy to follow even though I'd never used macros before. Worked perfectly the first time. You've saved a lot of frustration. Thank you very much!!

    on 9/01/2010 11:46 AM


  45. prnagaraj said,

    Hi
    It is really working. Thanks for providing such a wonderfull solution

    on 9/13/2010 10:20 PM


  46. prnagaraj said,

    Hi
    Thanks for providing such wonderfull solution

    on 9/13/2010 10:22 PM


  47. Anonymous said,

    Worked a treat on some old files

    on 9/22/2010 5:06 PM


  48. sridhar said,

    It worked wonderful. Thank you very much. However, I would also like to inform you that I could not unhide the first three coloumns(A,B,C) (AND UNHIDE IS NOT WORKING) in the sheet. Could you pl. help in this regard

    on 10/28/2010 9:56 PM


  49. Anonymous said,

    You are a lifesaver!!! Thanks!

    on 12/02/2010 6:45 AM


  50. Anonymous said,

    Nice one saved me a lot of retyping

    on 12/17/2010 10:06 PM


  51. Sweet gal said,

    Thanx!!
    I successfully unprotected the sheet, but the password provided to me was wrong..!!
    Can u please provide correct coding!

    on 12/18/2010 6:44 PM


  52. Conference said,

    Worked like a charm. Thanks!

    on 2/22/2011 7:47 PM


  53. Anonymous said,

    THX, removing the protection was really easy and the code works fine.

    on 3/07/2011 11:17 PM


  54. shyam said,

    when i open the protected excel work book it is asking for a password, how should i remove the password

    on 4/22/2011 10:32 AM


  55. shyam said,

    ok, i think this is a easy way to know the password then i have the password and opened it bevor, or?

    how can i recover the password of the excel worksheet or excel file?

    when i open the password protected excel file, he asks me "please enter the password" but i can't open the macro area.

    can you help me?

    on 4/22/2011 10:33 AM


  56. It worked! Thank you a lot!

    on 1/26/2012 4:22 AM


  57. Ben said,

    Wow, this was amazing... My friends think I'm a magician! Thanks for this!

    on 7/03/2012 9:58 PM


  58. This comment has been removed by the author.

    on 7/18/2012 8:19 PM


  59. Hi Jason,
    I have a protected macro in Excel 2007 and wanted to unlock it. As per your direction, I tried to record the macro, but after giving the name to the macro (eg: Macro1) it is asking for password. So I could not able to record anything. Can you suggest any workaround for this?
    One more thing, I could not able to copy anything from you web Page, do I need to have any special permission to copy your code? Please let me know. If possible please send me the code to raghunadhamurthy@gmail.com

    on 7/18/2012 9:55 PM


  60. Anonymous said,

    PEFRECT!!

    on 10/13/2012 3:01 AM


  61. Klorvalex said,

    Thanks. I tried the code and found it works fine.

    on 2/05/2013 10:19 PM