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:UsersKalininVEDocumentsexp1.xlsx
The created file will be: C:UsersKalininVEDocumentsexp1_xlsx_Sheet1_SheetOptions.gz

To open “.gz” file you will need 7-Zip (free zip file manager https://www.7-zip.org/) or any other working with gz compression format. Open C:UsersKalininVEDocumentsexp1_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:UsersKalininVEDocumentsexp1.xlsx
The created file will be: C:UsersKalininVEDocumentsexp1_xlsx_WorkbookOptions.gz

To open “.gz” file you will need 7-Zip (free zip file manager https://www.7-zip.org/) or any other working with gz compression format. Open C:UsersKalininVEDocumentsexp1_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!