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. Add some formulas and conditional formatting (if you want to really understand the details).
Let
us now see how excel stores this data in the file. Open the file in
notepad or a hex editor. Did you notice the first 2 characters? "PK". So
Excel compresses its file contents. Now we know why there is not much
difference if you compress an Office 2007 file.
Lets look into the compressed contents. Rename the file extension from .xlsm to .zip
Open
the MyFile.zip file. Wow! its an extensive structure with xml files to
store the workbook, worksheets, calculations, sharedstrings, etc.
This is how the XML of the Sheet1 looks
Lets explore more. Lets go back to our original file and add some VBA code to it.
Add a password and protect the VBA code.
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. This is
the file that I need to recover. Lets investigate further. Open this
file in a Hex Editor (there are lots of free ones out there... the one I
use is Hex Editor Neo at http://www.hhdsoftware.com/Products/home/hex-editor-free.html).
Search for the keyword "DPB" in the content. Remember to find it just above "[Host Extender Info]".
Once
found, replace the string "DPB" to "DBx" and save the file. Now replace
the edited "vbaProject.bin" file and place it back into the compressed
file collection (replace with the old one). Rename the compressed file
back to ".xlsm".
Try to open the file in Excel. It gives an error saying that the file contains an invalid key DBx. Hit Yes and proceed. The file opens. So far good. Now go to the VBA editor.
Next it gives another error "Unexpected Error". Proceed again by hittng OK
VBA
Editor opens. Now try to open the module code. Again the "Unexpected
Error". Dont lose heart... we are just there. Save the file and close
it. We are all set. Your file is as good as new without the VBA
password. Go check for yourself!
So Excel may NOT be encrypting the VBA file after all, rather it is only setting a flag to lock the VBA content. This is evident because we can still see the code in the vbaProject.bin file (that we had written in the VBA earlier).
Some
more interesting bits. Check how Excel stores its file contents. Look
into the file sharedStrings.xml. It stores the strings at a global
level, not in the sheet itself. Also look into how it stores the
formulas and formatting. Try out things like like dragging a cell
content (like Area 1) into 5 cells (producing Area 1, Area 2, Area 3...)
and see how it stores the values... using ranges.
That also says that, it may be better to access Excel data directly from these XML files rather than through the Excel application model. We could have much more control and simplified logic for storing (for instance in database), transporting (eg client machine to server), opening and displaying simple content. Many possibilities here...
Well, I was pretty pleased that today I learned something new... and an unlocked VBA code.
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. Add some formulas and conditional formatting (if you want to really understand the details).
Try to open the file in Excel. It gives an error saying that the file contains an invalid key DBx. Hit Yes and proceed. The file opens. So far good. Now go to the VBA editor.
So Excel may NOT be encrypting the VBA file after all, rather it is only setting a flag to lock the VBA content. This is evident because we can still see the code in the vbaProject.bin file (that we had written in the VBA earlier).
That also says that, it may be better to access Excel data directly from these XML files rather than through the Excel application model. We could have much more control and simplified logic for storing (for instance in database), transporting (eg client machine to server), opening and displaying simple content. Many possibilities here...
Well, I was pretty pleased that today I learned something new... and an unlocked VBA code.
-----------------------------------------------------------------------------------
And now unlock your protected Powerpoint Slideshow file
The previous post on how to unlock (your own) VBA password protected Excel file with simple tools. More importantly understand how Microsoft organizes its files on the disk. Here is one for a Microsoft Powerpoint Slide show file - recover an editable copy of a protected ppsx file (or similarly a protected pptx file).
Just as we did with the Excel files, lets investigate how Powerpoint stores it file contents on the disk. Create a new Powerpoint Slideshow file MyTest.pptx and enter some dummy content. Choose to file as [ppsx]. Let us now protect it by hitting the [General Options] (from [Tools] in the [Save Dialog]). Set a very complex password. We are safe now. The Slideshow file can be distributed without the contents being stolen.
Going by our earlier experience, we rename our file from [MyTest.ppsx] to [MyTest.zip]
Lets look into this file. Amoung other things we notice that a new node has been added into the xml (as compared to a unprotected file).
hmm...
Seems like the salt and hash of the password we had set. What the heck! Lets delete the node altogether.
Save the file and replace it in the zip file (drag and drop it into the open zip file collection).
Save and close your Zip file. Rename it back to [MyTest.ppsx]. Right click the file and select [New] to open the file in good'ol Powerpoint.
There it goes again. We have an unprotected copy of the Slidehow file that we had created earlier.
Enjoy!!
