Remove password from an Excel Document

I'm providing internal support and one of our users has managed to put a password on an excel file by accident, I've done the proper checks to make sure that the user should have access to the document and now want to know what the recommendation for removing a password from an Excel document.

For what its worth, the password appears after Excel opens but before you can see any data in excel.

Solution 1:

Elcomsoft make a pretty useful program called Advanced Office Password Recovery which can do the job better than anything else I've used.

It's probably worth putting a value on the data you're trying to recover before attempting this, sometimes it's less expensive to let the user re-create the document from scratch (and teaches them a valuable lesson ;-) ). AOPR isn't free, and the passwords can sometimes only be worked out by brute force (trying every possible combination of letters) which can take a very long time.

Solution 2:

If you know what the password is, go ahead and open the Excel document. Then click on File > Save As. To the left of the Save button is a little drop down labeled Tools. Click on that, and then click on General Options. Delete the password entries there, and click OK. Save the document.

If you do not know what the password is, you can use VBA to find it. If I had to take a guess, your user probably didn't use a super strong password, so we could use a brute force type method to find it. The code below is rough, but it has helped me find a weak, lost password on several of my users' documents. It checks for passwords of any length with the ASCII characters from 1 to z. You would call it from the Immediate Window and wait several minutes like so:

? GetPassword("D:\mywkbk.xlsx")

-

Public Function GetPassword(ByRef sFileName As String) As String
On Error Resume Next
    Dim pw As String
    pw = ""
    Do
        VBA.Err.Clear
        pw = GenerateNextPassword(pw)            
        Application.Workbooks.Open sFileName, False, True, , pw, pw
        VBA.DoEvents
    Loop While VBA.Err.Number = 5408
    GetPassword = pw
End Function

Public Function GenerateNextPassword(ByRef sCurrentPassword As String) As String
    Const MAX_CHAR = 122
    Const MIN_CHAR = 49

    Dim sCurrentPasswordMax As String
    Dim sNewPassword As String
    Dim i As Long

    sCurrentPasswordMax = String(Len(sCurrentPassword), Chr(MAX_CHAR))
    If sCurrentPassword = sCurrentPasswordMax Then
        'do an increment that changes the length
        sNewPassword = String(Len(sCurrentPassword) + 1, Chr(MIN_CHAR))
        Debug.Print Now(); ": "; sNewPassword
    ElseIf Asc(Right(sCurrentPassword, 1)) = MAX_CHAR Then
        'do an increment that changes multiple characters
        sNewPassword = Left(sCurrentPassword, Len(sCurrentPassword) - 1) & Chr(MIN_CHAR)
        For i = Len(sCurrentPassword) - 1 To 1 Step -1
            sNewPassword = Left(sNewPassword, i - 1) & Chr(Asc(Mid(sNewPassword, i, 1)) + 1) & Mid(sNewPassword, i + 1)
            If Asc(Mid(sCurrentPassword, i, 1)) <> MAX_CHAR Then
                Exit For
            End If
        Next i
    Else
        'do an increment on the rightmost character
        sNewPassword = Left(sCurrentPassword, Len(sCurrentPassword) - 1) & Chr(Asc(Right(sCurrentPassword, 1)) + 1)
    End If

    GenerateNextPassword = sNewPassword
End Function

Solution 3:

Is something like this, the Excel Password Remover XLA add-in, what you are after?

EDIT: Thinking about it, maybe not - this is used for removing the password from protected worksheets/workbooks.