How to unprotect the excel sheet if forgot the password

Copper Contributor

Hi team need support to unprotect my sheet which is password protected and forgot the password

 

295 Replies

@vedangbapna  In my case, I lost the password which limited my ability to edit the file but I could open the document.  So I went to file, export, change file type, and selected CSV. This created a copy of my file without all the formats/drop boxes but allowed me to copy the data, recreate or modify the document.

 

@vedangbapna 

Here's my macro

Here's my macro. It changes the extension, unzips file, edits xml files using regular expression, zips back updated files (credits to officetricks.com for zipping macro), changes the extension.

Running the macro is at your own risk. I am not responsible for any consequences of running the macro.

Artur

Sub unlocking()

'Author  @artup720 @techcomunity.microsoft.com

Dim Zrodlo As String, Fname As String, plik As String, folder As String, Temp_ As Variant, Rozszerzenie As String, Zip As String, ZipFolder As String, WorkFolder As String
Dim FSO As Object
Dim SourceFolder As Object
Zrodlo = Application.GetOpenFilename("Excel Files (*.xls*), *xls*", , "Browse for your File & Import Range", False)

If Zrodlo = "False" Then Exit Sub

plik = Dir(Zrodlo)
folder = Replace(Zrodlo, plik, "")
Temp_ = Split(plik, ".")
Rozszerzenie = Temp_(UBound(Temp_))

Select Case "." & UCase(Rozszerzenie)
Case ".XLSX", ".XLSM"
Case Else
MsgBox "Nieprawidłowe rozszerzenie pliku. Koniec Pracy"
GoTo koniec
End Select
Zip = Environ("temp") & "\" & Replace(plik, "." & Rozszerzenie, ".zip")
ZipFolder = Replace(Zip, ".zip", "")

If Not Dir(ZipFolder & "\") = "" Then
On Error Resume Next
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.deletefolder ZipFolder, True
MkDir (ZipFolder)

Else
On Error Resume Next
MkDir (ZipFolder)
On Error GoTo 0
End If


FileCopy Zrodlo, Zip
Dim wShApp As Shell
Dim objZipItems As FolderItems
Dim objZipItem As FolderItem
    
Set wShApp = CreateObject("Shell.Application")
Set objZipItems = wShApp.Namespace(Zip).Items
wShApp.Namespace(ZipFolder).CopyHere objZipItems


WorkFolder = ZipFolder & "\xl\worksheets\"


Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(WorkFolder)
Dim text
For Each FileItem In SourceFolder.Files

text = ""


text = CreateObject("Scripting.FileSystemObject").OpenTextFile(FileItem.Path, 1).ReadAll
'text = Replace(Split(text(i), "[")(1), "]", "")
Set objRegEx = CreateObject("VBScript.RegExp")
    With objRegEx
        .Global = True
        .MultiLine = True
        .Pattern = "(<sheetProtection algorithmName=).*?(\/>)"
        text = .Replace(text, "")
    End With
    Set objRegEx = Nothing


CreateObject("Scripting.FileSystemObject").OpenTextFile(FileItem.Path, 2).Write (text)
Next FileItem

'save Zip
ZipVBA Replace(Zrodlo, "." & Rozszerzenie, "-odbezpieczony.zip"), ZipFolder
FSO.movefile Replace(Zrodlo, "." & Rozszerzenie, "-odbezpieczony.zip"), Replace(Replace(Zrodlo, "." & Rozszerzenie, "-odbezpieczony.zip"), ".zip", "." & Rozszerzenie)

koniec:

Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
Set PathFilename = Nothing
FSO.deletefolder ZipFolder, True

MsgBox "Finish"
Exit Sub


End Sub

Sub ZipVBA(plik As String, folder As String)
'Source=https://officetricks.com/how-to-zip-file-or-folder-using-vba-code-excel-macro/
    'Define Variable Data Types
    'Early Binding Reference
    'Add Tools -> Reference -> "Microsoft Shell Controls & Automation"
    Dim zipFileName As String
    Dim unZipFolderName As String
    Dim objZipItems As FolderItems
    Dim objZipItem As FolderItem
    
    'Set Zip File Name & Folder path to Unzip
    zipFileName = plik
    unZipFolderName = folder
    
    'Create Empty Zip file
    Open zipFileName For Output As #1
        Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
    
    'Initialize Shell Object & File to be Zipped
    Dim wShApp As Shell
    Set wShApp =a CreateObject("Shell.Application")
    Set objZipItems = wShApp.Namespace(unZipFolderName).Items
    
    'Method1: Compress All Files at once
    wShApp.Namespace(zipFileName).CopyHere objZipItems
    Do Until wShApp.Namespace(zipFileName).Items.Count = objZipItems.Count
        DoEvents
        Debug.Print "Processing " & wShApp.Namespace(zipFileName).Items.Count & " of" & objZipItems.Count
        Application.Wait DateAdd("s", 1, Now)
    Loop
    Debug.Print "Processing " & wShApp.Namespace(zipFileName).Items.Count & " of" & objZipItems.Count
    

End Sub

 References neede:

artup720_0-1629817845171.png

 

artup720_1-1629817851593.png

 

 

(Method working with Excel 2007 xlsx files but not with old Excel 97-2003 xls file format)

1. Change your xlsx file extension to zip (xslx files are pure zip files)

2. Extract it to a folder called tmp (actually the folder name doesn't matter)

3. Worksheets can be found in tmp/xl/worksheets and are named sheet1.xml, sheet2.xml, ...

4. Open your password protected worksheet with a text editor (it's just xml data)

5. Find and delete the <sheetProtection> xml element

6. Save

7. zip the content of folder tmp (not the folder tmp itself, just its content !)

8. Rename the zip file extension to xlsx

9. Run it with Excel : your worksheet protection is gone !

@Patrick2788 

Hello Patrick,

 

Please suggest how to go about it.

Thank you.

 

Best Regards,

Meenakshi Sharma

@NikolinoDE : Hello. I saw your offers for assistance to unprotect an Excel file. I've followed the VBA steps and get a "syntax" error. I need to unprotect an entire workbook (hidden tabs). Could I send you the file to assist me in this? Thank you very much!

Hey can i send you the excel sheet?

@NikolinoDE Hi! can you help me to unlock this file? Many 

@NikolinoDE Hi! can you please remove the password from this excel file? Thanks a lot..

@Tarun3338 Perhaps, @NikolinoDE will not be able to help you in your case.

 

All the methods described above are about action restriction passwords. They are instantly deleted in all Excel versions one way or another. Such passwords are not for data protection, but for foolproof — they protect against accidental changes.

 

Your file is different.

 

Encryption with a password is used in it and the only way to open it is to find the password by searching. And since it is Excel 2013, then in the general case it is impossible to crack another person’s password (if it is not a popular password from a dictionary, of course ;)). Therefore, Microsoft does not provide tools for such actions. It is considered that it is impossible to crack protection.

 

But you can try to RECOVER YOUR password. You should use a position mask, dictionary mutation, GPU acceleration and speed-optimized programs (I would recommend AccentOPR).

 

But still there are no success guarantees for xlsx files. Encryption with a password in MS gets stronger with every version.

@NikolinoDE Hi Nikolina, I tried my best doing this but didnt work - I have a few to remove but cant manage it - can you pls help :(

I don't get where "the code on the right" is supposed to be? I see nothing.

Please can I send my encrypted script m having challenge with it@NikolinoDE 

@NikolinoDE 

 

don't see code on the right??

@NikolinoDE I am unable to run that code as it shows error. Attached the file.

@NikolinoDE  apologies I am not tech savvy at all so stumbling through this.  I somehow have forgotten my password for an excel workbook.  I am so used to typing it with muscle memory and use this file a lot but have tried everything.  I read your advice and I cannot even get to the alt-F11 area you refer to, as it just takes me to an image/guide of my keyboard. I'm on a Lenovo Thinkpad so perhaps it is different.  The other issue is I cannot even get to the File Info section because it won't let me open the workbook period.   Is there a way to use right click in My Documents folder on the file and somehow apply the code you refer to?  It's on my laptop that I've had for years and I created the file and have never had it modified by anyone.    Thoughts?  Thank you,  Tracy

You should only remove protection on a copy of the file not the original and only if you are the author.
Try this site :exceloffthegrid.com and search removing-cracking-excel-passwords-with-vba
Don't forget to leave a thank you...

Cheers
Jim

@Eviee1265  

A bit late, and maybe you found out . When I paste the code it give me an empty line on the .Unprotect line.
So after you pasted the vba code in, you need to remove the empty line:

.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & _

            Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t)

 

So it looks like this instead:

 

.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & _
Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t)

 

Hi @rohitsharma2040 ,

Using the macro I posted couple weeks before i unprotected your spreedsheet in 40 seconds.

 

Regards,

 

Artur

@BrockT 

 

I have the same error. I have reviewed the responses to your original issue in regards to the syntax compile error and i still cant seem to fix the issue. I added the code to each of the worksheets but i still get that error. Do you have any suggestions?