Remove password protection from Excel file
Excel files have the option to provide them with password protection. I use it once in a while. For example, to prevent formulas in cells from being deleted or modified. An important part of using a password is not to forget it. What can you do if you have an Excel file whose password you don't know?
[TOC]
Different password protections in Excel
You can protect Excel with a password in five different ways:
- Protect file from opening.
- Protect file from modification.
- Protect worksheet.
- Protect workbook.
- VBAProject security.
Each security has a different purpose and should be approached in a different way.
How Excel Files Protected With Passwords
Above we read that an Excel document has five different password protections. These protections can be performed in three different ways. Through:
- An XML file.
- encryption.
- Binary VBA Project.
XML file
As of version Excel 2007, the standard file format of Excel has been changed from .xls to .xlsx. This has to do with the technical change of the file structure. The .xlsx files are, as it were, .zip files which consist of XML files. The advantage of this is that the size of your files is considerably smaller and it gives developers more options by giving them access to the underlying structure of the XML files.
encryption
From version Excel 2007, the security level has increased enormously. When we protect a file against opening, the Excel file is provided with encryption.
Binary VBA Project
The passwords or the encrypted versions of those passwords are stored in the binary itself.
Removing different types of password protections
Crack the password of an Excel file that is protected from opening
This type of password protection can only be cracked by brute force attack. In other words, software tries to 'guess' the password using different letter and/or number combinations. The simpler the password, the easier it is to crack.
Crack the password of an Excel file that is protected from modification
The password protection (as far as we can talk about that) is easy to remove by saving the file under a different name. Easy is not it?
Crack the password of a protected worksheet or workbook
We have two methods at our disposal for this. As up here described above, an Excel file consists of XML files from 2007 onwards. Excel does not store the password in these files, but uses an algorithm that modifies the code. Then the modified code is saved in the xlsx file. Security is removed when a password through the algorithm produces the same result.
Crack a protected worksheet or workbook manually
Download the sample file and save it to a folder on your computer
Make sure the file extensions are visible.
- Make a copy of the sample file and change the extension to .zip
- Open the file “file with password.zip”.
- Open the \xl\workbooks.xml file to unlock a protected workbook.
- Open the file \xl\worksheets\sheet1.xml to unlock a protected worksheet.
- The boxed area shows the code that encodes the password for the workbook.
- Delete this section from the xml file and save it.
- Put the modified files back in the zip file and change the extension to .xlsx.
Cracking a protected worksheet or workbook with a macro
The above method can also be performed faster and easier by running a macro. Create a new Excel file and copy the code below into a standard module of the Visual Basic Editor.
Sub RemoveProtection() Dim dialogBox As FileDialog Dim sourceFullName As String Dim sourceFilePath As String Dim sourceFileName As String Dim sourceFileType As String Dim newFileName As Variant Dim tempFileName As String Dim zipFilePath As Variant Dim oApp As Object Dim FSO As Object Dim xmlSheetFile As String Dim xmlFile As Integer Dim xmlFileContent As String Dim xmlStartProtectionCode As Double Dim xmlEndProtectionCode As Double Dim xmlProtectionString As String 'Open dialog box to select a file Set dialogBox = Application.FileDialog(msoFileDialogFilePicker) dialogBox.AllowMultiSelect = False dialogBox.Title = "Select file to remove protection from" If dialogBox.Show = -1 Then sourceFullName = dialogBox.SelectedItems(1) Else Exit Sub End If 'Get folder path, file type and file name from the sourceFullName sourceFilePath = Left(sourceFullName, InStrRev(sourceFullName, "\") ) sourceFileType = Mid(sourceFullName, InStrRev(sourceFullName, ".") + 1) sourceFileName = Mid(sourceFullName, Le n(sourceFilePath) + 1) sourceFileName = Left(sourceFileName, InStrRev(sourceFileName, ".") - 1) 'Use the date and time to create a unique file name tempFileName = "Temp" & Format(Now, " dd-mmm -yy h-mm-ss") 'Copy and rename original file to a zip file with a unique name newFileName = sourceFilePath & tempFileName & ".zip" On Error Resume Next FileCopy sourceFullName, newFileName If Err.Number <> 0 Then MsgBox "Unable to copy " & sourceFullName & vbNewLine _ & "Check the file is closed and try again" Exit Sub End If On Error GoTo 0 'Create folder to unzip to zipFilePath = sourceFilePath & tempFileName & "\" MkDir zipFilePath 'Extract the files into the newly created folder Set oApp = CreateObject("Shell.Application") oApp.Namespace(zipFilePath).CopyHere oApp.Namespace(newFileName).items 'loop through each file in the \xl\worksheets folder of the unzipped file xmlSheetFile = Dir(zipFilePath & "\xl\worksheets\*.xml*") Do While xmlSheetFile <> "" 'Read text of the file to a variable xmlFile = FreeFile Open zipFilePath & "xl\worksheets\" & xmlSheetFile For Input As xmlFile xmlFileContent = Input(LOF(xmlFile), xmlFile) Close xmlFile 'Manipulate the text in the file xmlStartProtectionCode = 0 xmlStartProtectionCode = InStr(1, xmlFileContent, " 0 Then xmlEndProtectionCode = InStr(xmlStartProtectionCode, _ xmlFileContent, "/>") + 2 '"/>" is 2 characters long xmlProtectionString = Mid(xmlFileContent, xmlStartProtectionCode, _ xmlEndProtectionCode - xmlStartProtectionCode) xmlFileContent = Replace(xmlFileContent, xmlProtectionString, "" ) End If 'Output the text of the variable to the file xmlFile = FreeFile Open zipFilePath & "xl\worksheets\" & xmlSheetFile For Output As xmlFile Print #xmlFile, xmlFileContent Close xmlFile 'Loop to next xmlFile in directory xmlSheetFile = Dir Loop 'Read text of the xl\workbook.xml file to a variable xmlFile = FreeFile Open zipFilePath & "xl\workbook.xml" For Input As xmlFile xmlFileContent = Input(LOF(xmlFile), xmlFile) Close xmlFile 'Manipulate the text in the file to remove the workbook protection xmlStartProtectionCode = 0 xmlStartProtectionCode = InStr(1, xmlFileContent, " 0 Then xmlEndProtectionCode = InStr(xmlStartProtectionCode, _ xmlFileContent, "/>") + 2 ''"/>" is 2 characters long xmlProtectionString = Mid(xmlFileContent, xmlStartProtectionCode, _ xmlEndProtectionCode - xmlStartProtectionCode) xmlFileContent = Replace(xmlFileContent, xmlProtectionString, " ") End If 'Manipulate the text in the file to remove the modify password xmlStartProtectionCode = 0 xmlStartProtectionCode = InStr(1, xmlFileContent, " 0 Then xmlEndProtectionCode = InStr(xmlStartProtectionCode, xmlFileContent, _ "/>") + 2 ''"/>" is 2 characters long xmlProtectionString = Mid(xmlFileContent, xmlStartProtectionCode, _ xmlEndProtectionCode - xmlStartProtectionCode) xmlFileContent = Replace(xmlFileContent, xmlProtectionString, " ") End If 'Output the text of the variable to the file xmlFile = FreeFile Open zipFilePath & "xl\workbook.xml" & xmlSheetFile For Output As xmlFile Print #xmlFile, xmlFileContent Close xmlFile 'Create empty Zip File Open sourceFilePath & tempFileName & " .zip" For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 'Move files into the zip file oApp.Namespace(sourceFilePath & tempFileName & ".zip").CopyHere _ oApp.Namespace(zipFilePath).items 'Keep script waiting until Compressing is done On Error Resume Next Do Until oApp.Namespace(sourceFilePath & tempFileName & ".zip").items.Count = _ oApp.Namespace(zipFilePath).items.Count Appl ication.Wait (Now + TimeValue("0:00:01")) Loop On Error GoTo 0 'Delete the files & folders created during the sub Set FSO = CreateObject("scripting.filesystemobject") FSO.deletefolder sourceFilePath & tempFileName ' Rename the final file back to an xlsx file Name sourceFilePath & tempFileName & ".zip" As sourceFilePath & sourceFileName _ & "_" & Format(Now, "dd-mmm-yy h-mm-ss") & "." & sourceFileType 'Show message box MsgBox "The workbook and worksheet protection passwords have been removed.", _ vbInformation + vbOKOnly, Title:="Password protection" End Sub
Run the above macro and select the file whose worksheet or workbook is protected. The macro then makes a copy of the file and removes the password protection(s).
Common Error Messages and Solutions
Error 53 during execution: Cannot find the file.
- The Excel file is an .xlsb file type (Excel binary file). The macro only works on .xlsx files.
Error 76 at runtime: Could not find the file.
- The Excel file is an .xls file type. The macro only works on .xlsx files.
- The file is protected against opening.
Error 91 during execution: Object variable or block variable with is not set.
- You are trying to run the macro on a Mac. The macro only works on a Windows computer.
Cracking a binary VBA project
With the macro below it is possible to unlock a protected VBA project. The macro was created by a Vietnamese software developer. The original code can be found at stackoverflow.
- Open the file whose VBA project is password protected.
- Create a new .xlsm file and copy the code below into a standard module of the Visual Basic Editor.
- Enter the lower part of the code and the password will be removed.
Pay attention! The macro below only works on VBA projects that are provided with a password and which are created with the default Excel password option. A VBA project that has been password-protected by Unviewable+ cannot be cracked.
Private Const PAGE_EXECUTE_READWRITE = &H40 Private Declare PtrSafe Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _ (Destination As LongPtr, Source As LongPtr, ByVal Length As LongPtr) Private Declare PtrSafe Function VirtualProtect Lib "kernel32" (lpAddress As LongPtr, _ ByVal dwSize As LongPtr, ByVal flNewProtect As LongPtr, lpflOldProtect As LongPtr) As LongPtr Private Declare PtrSafe Function GetModuleHandleA Lib "kernel32" (ByVal lpModuleName As String) As LongPtr Private Declare PtrSafe Function GetProcAddress Lib "kernel32" (ByVal hModule As LongPtr, _ ByVal lpProcName As String) As LongPtr Private Declare PtrSafe Function DialogBoxParam Lib "user32" Alias "DialogBoxParamA" (ByVal hInstance As LongPtr, _ ByVal pTemplateName As LongPtr, ByVal hWndParent As LongPtr, _ ByVal lpDialogFunc As LongPtr, ByVal dwInitParam As LongPtr) As Integer Dim HookBytes( 0 To 11) As Byte Dim OriginBytes(0 To 11) As Byte Dim pFunc As LongPtr Dim Flag As Boolean Private Function GetPtr(ByVal Val ue As LongPtr) As LongPtr GetPtr = Value End Function Public Sub RecoverBytes() If Flag Then MoveMemory ByVal pFunc, ByVal VarPtr(OriginBytes(0)), 12 End Sub Public Function Hook() As Boolean Dim TmpBytes(0 To 11) As Byte Dim p As LongPtr, osi As Byte Dim OriginProtect As LongPtr Hook = False #If Win64 Then osi = 1 #Else osi = 0 #End If pFunc = GetProcAddress(GetModuleHandleA("user32.dll"), "DialogBoxParamA") If VirtualProtect(ByVal pFunc , 12, PAGE_EXECUTE_READWRITE, OriginProtect) <> 0 Then MoveMemory ByVal VarPtr(TmpBytes(0)), ByVal pFunc, osi+1 If TmpBytes(osi) <> &HB8 Then MoveMemory ByVal VarPtr(OriginBytes(0)), ByVal pFunc, 12 p = GetPtr(AddressOf MyDialogBoxParam) If osi Then HookBytes(0) = &H48 HookBytes(osi) = &HB8 osi = osi + 1 MoveMemory ByVal VarPtr(HookBytes(osi)), ByVal VarPtr(p), 4 * osi HookBytes(osi + 4 * osi) = &HFF HookBytes(osi + 4 * osi + 1) = &HE0 MoveMemory ByVal pFunc, ByVal VarPtr(HookBytes(0)), 12 Flag = True Hook = True End If End If End Function Private Function MyDialogBoxParam(ByVal hInstance As LongPtr, _ ByVal pTemplateName As LongPtr, ByVal hWndParent As LongPtr, _ ByVal lpDialogFunc As LongPtr, ByVal dwInitParam As LongPtr) As Integer If pTemplateName = 4070 Then MyDialogBoxParam = 1 Else RecoverBytes MyDialogBoxParam = DialogBoxParam, pTemplateTemName , _ hWndParent, lpDialogFunc, dwInitParam) Hook End If End Function ''''RUN THE CODE BELOW'''' Sub VBAUnprotected() If Hook Then MsgBox "VBA Project is unprotected!", vbInformation, "*****" End If End Sub