How to Get EPM Protection Password

[former_member186338]

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!