How to Get EPM Protection Password

In some cases it’s required to find the password used to protect sheets in EPM workbook. Not to remove, but to find the password string. This can be done with a simple VBA code.

Create a new workbook, open VBA editor and insert a new module (Module1).
Open Menu: Tools -> References and add the reference:

Code to Get Sheet Protection Password

In the code window paste the following code:

Option Explicit

Public Sub ExportSheetOptions()
'Tools -> References: Required "Microsoft XML, v6.0"

    Dim wshCur As Worksheet
    Dim strPath As String
    Dim shpCur As Shape
    Dim strBase64 As String
    Dim objXML As MSXML2.DOMDocument60
    Dim objNode As MSXML2.IXMLDOMElement
    Dim bytArr() As Byte
    Dim bytShortArr() As Byte
    Dim lngSize As Long
    Dim lngTemp As Long
    

    Set wshCur = ActiveWorkbook.ActiveSheet
    strPath = ActiveWorkbook.FullName
    strPath = StrReverse(Replace(StrReverse(strPath), ".", "_", 1, 1))

    Set shpCur = wshCur.Shapes("FPMExcelClientSheetOptionstb1")
    strBase64 = shpCur.OLEFormat.Object.Object.Text

    Set objXML = New MSXML2.DOMDocument60
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.Text = strBase64
    bytArr = objNode.nodeTypedValue
   
    Set objNode = Nothing
    Set objXML = Nothing
    
    lngSize = UBound(bytArr)
    ReDim bytShortArr(0 To lngSize - 4)
    
    For lngTemp = 4 To lngSize
        bytShortArr(lngTemp - 4) = bytArr(lngTemp)
    Next lngTemp
    
    Open strPath & "_" & wshCur.Name & "_SheetOptions.gz" For Binary Access Write As #1
    lngTemp = 1
    Put #1, lngTemp, bytShortArr
    Close #1

End Sub

Save the file (for example with the name decode.xlsm). Don’t close it.

Open EPM protected file, select the protected sheet you want to get a password.
On Excel ribbon select Developer tab and press Macros button:

Run macro: decode.xlsm!ExportSheetOptions

Will run silent and the file (OriginalFileName_Extension_SheetName_SheetOptions.gz) will be created in the same folder as the folder of EPM file:

For example if the EPM file was: C:\Users\KalininVE\Documents\exp1.xlsx
The created file will be: C:\Users\KalininVE\Documents\exp1_xlsx_Sheet1_SheetOptions.gz

To open “.gz” file you will need 7-Zip (free zip file manager http://www.7-zip.org/) or any other working with gz compression format. Open C:\Users\KalininVE\Documents\exp1_xlsx_Sheet1_SheetOptions.gz in 7-Zip.

Right click on the file inside archive (exp1_xlsx_Sheet1_SheetOptions) and select Edit.
Notepad will open. In Notepad search for “SheetPwd”

Result:

Password 12345 found!

Code to Get Workbook Protection Password

In the code window paste the following code (can be done in the same file decode.xlsm and in the same module Module1):

Public Sub ExportWorkbookOptions()
'Tools -> References: Required "Microsoft XML, v6.0"

    Dim namWorkbook As Name
    Dim strPath As String
    Dim shpCur As Shape
    Dim strBase64 As String
    Dim objXML As MSXML2.DOMDocument60
    Dim objNode As MSXML2.IXMLDOMElement
    Dim bytArr() As Byte
    Dim bytShortArr() As Byte
    Dim lngSize As Long
    Dim lngTemp As Long

    strPath = ActiveWorkbook.FullName
    strPath = StrReverse(Replace(StrReverse(strPath), ".", "_", 1, 1))

    strBase64 = ""
    For Each namWorkbook In ActiveWorkbook.Names
        If namWorkbook.Name Like "EPMWorkbookOptions*" Then
            strBase64 = strBase64 & Mid(namWorkbook.Value, 3, Len(namWorkbook.Value) - 3)
        End If
    Next

    Set objXML = New MSXML2.DOMDocument60
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.Text = strBase64
    bytArr = objNode.nodeTypedValue
   
    Set objNode = Nothing
    Set objXML = Nothing
    
    lngSize = UBound(bytArr)
    ReDim bytShortArr(0 To lngSize - 4)
    
    For lngTemp = 4 To lngSize
        bytShortArr(lngTemp - 4) = bytArr(lngTemp)
    Next lngTemp

    For lngTemp = 4 To lngSize
        bytShortArr(lngTemp - 4) = bytArr(lngTemp)
    Next lngTemp
    
    Open strPath & "_WorkbookOptions.gz" For Binary Access Write As #1
    lngTemp = 1
    Put #1, lngTemp, bytShortArr
    Close #1

End Sub

Save decode.xlsm file. Don’t close it.

Open EPM protected file (workbook protection).
On Excel ribbon select Developer tab and press Macros button:

Run macro: decode.xlsm!ExportWorkbookOptions

Will run silent and the file (OriginalFileName_Extension_WorkbookOptions.gz) will be created in the same folder as the folder of EPM file:

For example if the EPM file was: C:\Users\KalininVE\Documents\exp1.xlsx
The created file will be: C:\Users\KalininVE\Documents\exp1_xlsx_WorkbookOptions.gz

To open “.gz” file you will need 7-Zip (free zip file manager http://www.7-zip.org/) or any other working with gz compression format. Open C:\Users\KalininVE\Documents\exp1_xlsx_WorkbookOptions.gz in 7-Zip.

Right click on the file inside archive (exp1_xlsx_WorkbookOptions) and select Edit.
Notepad will open. In Notepad you will see:

Password 12345 found!

P.S. Updated on 14.03.2017 – “Code to Get Workbook Protection Password” section added!