Removing / cracking Excel passwords with VBA

There is nothing more frustrating than finding out a previous employee has used passwords to protect an Excel file, or maybe you set the password and have now forgotten it.  Either way… you’re stuffed!  However, before you give in, let me share some of the methods I use to remove passwords.

When messing around with password removal it is easy to make files unreadable, so make sure you back-up before doing anything.  Else the file you want to restore may become completely unreadable.

Setting different types of passwords

Before we even start discussing how to remove passwords, we need to understand the different types of passwords, as the removal approach will vary for each type.

File open passwords

The File Open password will prevent an Excel workbook from opening until a password is entered.  Click File -> Save As.  Then, if using Excel 2016 click More Options… to display the Save As dialog box.

[insert image]

From the Save As dialog box click Tools -> General Options…

The General Options dialog box will appear.  Enter a password in the Password to Open box, then click OK.

In the Confirm Password window reenter the password and click OK.

Finally, click Save in the Save As window.  

The File Open password has now been set.

File Modify passwords

The File Modify password enables a workbook to be opened, but not modified until a password has been entered.  Some of the steps are the same as the file Open password, but they are repeated here for completeness.

Click File -> Save As.  If using Excel 2016 click More Options… to display the Save As dialog box.

Click Tools -> General Options…

The General Options dialog box will appear.  Enter a password in the Password to Modify box, then click OK.

In the Confirm Password window reenter the password and click OK.

Finally, click Save on the Save As window.  

The File Modify password has now been set.

Worksheet protection passwords

The worksheet protection passwords prevent specific cells from being changed.

Start by setting the lock property of a cell or range of cells.   Select some cells then right-click and click Format Cells… from the menu.

The Format Cells window will open.  Select the Protection Tab, then tick or untick the locked option as required.  Click OK to close the Format Cells window.

To apply the protection, click Protect Sheet from the Review Ribbon.

The Protect Sheet window will open.  Enter a password, use the tick boxes to set the protection to be applied, then click OK.

In the Confirm Password window reenter the password and click OK.

Done.  The worksheet is now protected.

Workbook protection passwords

Workbook protection is applied in a similar way to worksheet protection, but with fewer options.  Workbook protection prevents users from changing the structure of a workbook, such as creating or renaming new worksheets.

Click Protect Workbook from the Review Ribbon.

The Protect Structure window will open.  Enter a password, then click OK.  Prior to Excel 2013, both the Structure and Window options were available.  Due to the Single Document Interface introduced in Excel 2013, the Windows this option is no longer relevant.

In the Confirm Password window reenter the password and click OK.

The workbook structure is now protected.

VBA project passwords

VBA Project passwords prevent users from viewing or changing the code of a VBA Project.

In the Visual Basic Editor window click Tools -> VBA Project Properties…

The VBA Project – Project Properties window will open.  Select the Protection tab, enter and confirm a password, then click OK.

The VBA project is now protected.

How Excel handles passwords

Whilst we have considered five different passwords there are only three ways which Excel stores the passwords.

Encryption

From Excel 2007, Microsoft’s level of protection increased significantly. When saving a file with the File Open password, Excel will encrypt the file with 128 bit or 256 bit encryption.  This encryption significantly changes the file and the things which can be done to it.

XML file code

From Excel 2007 the Excel file format changed to be a .xlsx format.  This basically means the file is built using an XML format.  That might sound confusing, but don’t worry.  All you need to know is that if we can edit the XML code we can change the password attributes of an Excel file.

The worksheet, workbook and modify passwords are all featured as part of the XML code.  Anybody with Notepad on their PC can change an XML file (oh wait, that’s everybody!).

VBA Project binary

VBA Projects are stored as binary files within the Excel file format structure.  The passwords, or the encrypted versions of those passwords are stored within the binary file.

Removing each type of password

Since the passwords are stored in different ways the method of removing them is also different.

Removing / cracking the File Open (encrypted) password

This first password method is the bad news.  The only method I know of is a brute force attack (i.e. trying every combination of every letter).  There are third-party software tools and services which claim to crack passwords.  The more complex the password the longer it will take to crack.  It can easily take days, or weeks, or months.

Thankfully, I have never needed to rely on these services.

Removing / cracking the XML file passwords

Excel does not store the password within the XML file.  It uses an algorithm to change the password, it is this which is stored within the file.  The protection is removed when any password put through the same algorithm creates the same result.  Thankfully, we do not have to try brute force, because we can try other means.

Remove the Modify File password

The Modify File password can be removed by saving the file with a new name.  Nice and simple.

Remove the Workbook and Worksheet passwords manually

I keep talking about the XML file and the file structure, let’s have a look at it.  You’ll see it’s not too scary.

Firstly, make sure the file extensions are visible on your computer.

On any Windows folder click.  View -> Options

In the Folder Options window click the View Tab, remove the tick from Hide extensions for known file types, then click OK.

It is now possible to see the .xlsx file extension.  Rename the file to include the .zip extension.

Open the zip file, then navigate to the \xl\workbooks.xml file.  The highlighted section below show where the Modify and Worksheet passwords are coded:

Next, navigate to \xl\worksheets\sheet1.xml. The highlighted section below shows where the password code is:

We could edit these text files to remove this code before changing the file back to a .xlsx file.  It would work.

Or, we could use a macro 🙂

Remove the passwords with a macro

Open a new Excel Workbook and copy the following code into a standard module within 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, Len(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, "<sheetProtection")

    If xmlStartProtectionCode > 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, "<workbookProtection")
If xmlStartProtectionCode > 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, "<fileSharing")
If xmlStartProtectionCode > 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
    Application.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 macro above.  Select the file, then click OK.  The macro will create a new file with the modify, workbook and worksheet passwords removed.

Removing / cracking the VBA Project Binary password

Finally, we get to the VBA Project Bin.  Many tutorials suggest using a HEX editor to remove the password.  But there is a better way.

There is an amazing macro which confuses the Visual Basic Editor into believing a valid password has been entered.  Click the link below to find it.

https://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project

I could not have coded that macro in a million years.  But I have used it several times over the past few years.

Please note, the macro only works with passwords created using Excel’s standard VBA password feature.  It will not necessarily remove passwords where the project has been protected by third-party software.

Conclusion

There you have it how to crack worksheet, workbook, modify and VBA project passwords with VBA code. If you were hoping to get instructions to break the File Open password, then I’m sorry, I don’t have an easy solution.

---

Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Books:

Excel 2016 Power Programming with VBA

Courses:

The Ultimate Excel Programmer Course