Aug 08 2020 04:10 AM - last edited on Nov 09 2023 11:08 AM by
Hi team need support to unprotect my sheet which is password protected and forgot the password
Aug 24 2021 07:53 AM
@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.
Aug 24 2021 08:15 AM
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:
Aug 30 2021 01:53 AM
Sep 01 2021 06:41 PM
(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 !
Sep 03 2021 12:31 AM
Hello Patrick,
Please suggest how to go about it.
Thank you.
Best Regards,
Meenakshi Sharma
Sep 13 2021 10:59 AM
@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!
Sep 20 2021 10:57 AM
Sep 21 2021 01:30 PM
@NikolinoDE Hi! can you help me to unlock this file? Many
Sep 21 2021 01:32 PM
@NikolinoDE Hi! can you please remove the password from this excel file? Thanks a lot..
Sep 23 2021 01:42 PM
@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.
Sep 28 2021 03:14 AM
@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 :(
Sep 28 2021 07:51 AM
Sep 29 2021 09:51 AM
Please can I send my encrypted script m having challenge with it@NikolinoDE
Sep 29 2021 02:20 PM
Oct 07 2021 05:22 AM
@NikolinoDE I am unable to run that code as it shows error. Attached the file.
Oct 07 2021 02:18 PM
@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
Oct 07 2021 08:25 PM
Oct 08 2021 12:04 AM
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)
Oct 08 2021 12:12 AM
Hi @rohitsharma2040 ,
Using the macro I posted couple weeks before i unprotected your spreedsheet in 40 seconds.
Regards,
Artur
Oct 18 2021 07:06 AM
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?