HKL's Notes
N/A C.V. · Linkedin · Github

Unlock a VBA password protected Excel file

By , on , tagged: 🏷️Windows  🏷️Office 

Unlock a VBA password protected Excel file


Ever felt the need to open a VBA protected excel file… maybe one of your old files that contained an excellent routine! How do you come out of that pain?


Important: This article is for educational purposes. Try this method for opening ONLY your own files, as I did too!



So how does Excel store the file contents – cell data including formulas and formats, conditional formatting, VBA code, etc. etc. Lets investigate.

Create a new Excel file MyTest.xlsm and enter some dummy test data in the first sheet. (先新建一个xlsm文件。)

Add some formulas and conditional formatting (if you want to really understand the details).)(自己输入一些内容)

alt text

Let us now see how excel stores this data in the file.

Open the file in notepad or a hex editor.(用记事本打开该文件,推荐用notepad++)

Did you notice the first 2 characters? “PK”. (前两字“PK”)

So Excel compresses its file contents. Now we know why there is not much difference if you compress an Office 2007 file.

alt text

Lets look into the compressed contents. Rename the file extension from .xlsm to .zip。(改文件后缀为zip)

alt text

Open the file. (打开改后缀后的文件)

Wow! its an extensive structure with xml files to store the workbook, worksheets, calculations, sharedstrings, etc.

alt text

This is how the XML of the Sheet1 looks

alt text

Lets explore more. Lets go back to our original file and add some VBA code to it.

alt text

Add a password and protect the VBA code.(添加个VBA密码)

alt text

Save the file and redo the same steps as earlier to open the xml file structure.

We now have another XML file called vbaProject.bin.(之前的xml文件都变成了个vbaProject.bin)

This is the file that I need to recover.

Lets investigate further.

Open this file “vbaProject.bin” in a Hex Editor (there are lots of free ones out there… the one I use is Hex Editor Neo

alt text

(用 Hex Editor Neo打开文件 “vbaProject.bin”)

Articles are licensed under CC BY-NC 4.0