Skip to Content
Technical Articles
Author's profile photo Vadim Kalinin

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!

Assigned tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Quite useful Vadim.....Thanks! Just a small question.....the above code will also work for .xlsm workbooks? Macro enabled??

      JP

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Yes, it will work with any type of EPM workbooks (including xlsm)!
       

      Author's profile photo Durgaprasad Goka
      Durgaprasad Goka

      Thanks for wonderful Document Vadim

      Author's profile photo Prasad Rangudu
      Prasad Rangudu

      Thanks a lot !! Very useful document.

      Author's profile photo Cliff Stinson
      Cliff Stinson

      Hi Vadim,

       

      First, that is brilliant! I've used the sheet version and it worked beautifully. However On the workbook version I am getting an error

      If I choose debug I get to this line

      bytArr = objNode.nodeTypedValue

       

      Any guidance you can provide would be most appreciated.

       

      Thanks

      Cliff

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Workbook password is stored in named range with the name like

      EPMWorkbookOptions*

      In my tests the Base64 encoded string is stored starting from 3rd character in this string.

      Can you show the full value of this name?

          For Each namWorkbook In ActiveWorkbook.Names
              If namWorkbook.Name Like "EPMWorkbookOptions*" Then
                  Debug.Print namWorkbook.Value
              End If
          Next

       

       

      Author's profile photo Bhagyesh Ravange
      Bhagyesh Ravange

      Very very helpful article... It worked for me thanks...

      Author's profile photo Alina Schmidt
      Alina Schmidt

      Hi Vadim,

      First of all, thank you for the useful code.

      It works perfectly with a regular sheet that is protected but with the EPM sheet, it would always display an error when trying to run the macro.

      For the following code line the system says that no object can be found:

      Set shpCur = wshCur.Shapes("FPMExcelClientSheetOptionstb1")

      Do you have any advice?

      Thank you in advance.

       

      Alina

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      EPM version? Code used?

      Author's profile photo Alina Schmidt
      Alina Schmidt

      We are using the EPM for IBP Excel Add-In 2005.2.0 and we're referring to the code for sheet protection.

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Sorry, but I have no idea about IBP Excel Add-In 2005.2.0... And unable to test it.

      My code will work with standard EPM add-in or AO with some minor changes.