Gautam K Banik

Posts RSS

Comments RSS

Contact

Scrum

Agile

Excel

Wednesday, August 4, 2010

Liked it?
More

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!

Labels
.NET

Agile

Dial

Excel Flash

AS3 C#

Code

Gauge

Component Defend

General

gmail

Humour

Password POWERPOINT
Rally
Review Scrum

Integration IPL Karaoke Music

Protected

So how does Excel store the file contents - cell data including formulas and formats, conditional

Punctuality

SLIDESHOW UI

Unlock VBA You Tube

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).

About Me
G A UTA M K BA NIK
M UM BA I, INDIA

[email protected]
[email protected]
VIEW MY COMPLETE PROFILE

Blog Archive
2013 (2)

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Let us now see how excel stores this data in the file. Open the file in notepad or a hex editor.

2011 (2)

Did you notice the first 2 characters? "PK". So Excel compresses its file contents. Now we know

2010 (11)

why there is not much difference if you compress an Office 2007 file.

August (3)
Playing with Excel Worksheet Change event
Excel Template
Unlock a VBA password protected Excel file
March (3)
February (2)
January (3)

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.

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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.

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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]".

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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...
open in browser PRO version Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Well, I was pretty pleased that today I learned something new... and an unlocked VBA code.
POSTED BY GAUTAM K BANIK AT 3:28 PM
LABELS: CODE, EXCEL, PASSWORD, PROTECTED, UNLOCK, V BA

5 2 c o m m e n t s:
peppertea3 November 1, 2011 at 10:02 PM
Worked like a charm! Thank you!
Is it possible to convert an exe, that was previously a xlsm, back into xlsm?
Reply

. February 16, 2012 at 7:20 PM

Voc muito bom! Parabns!
Reply

MILIND February 20, 2012 at 4:53 AM

How to open password protected excel file?
Reply

Prabhu March 17, 2012 at 10:52 AM

great work... thanks a lot...
Reply

852ba436-9ae2-11e1-9017-000bcdcb5194 May 11, 2012 at 2:26 AM

Excellent post! I was searching everywhere for a VBA password unlocker/remover, and your
instructions above worked like a charm (with the Hex Editor suggested above).

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Reply

mj12 June 7, 2012 at 7:58 AM

Outstanding work, m8! You saved me a *lot* of work.
Reply

Kevin See August 10, 2012 at 4:39 PM

Amazing! It works! Thank you so much for this :)
Reply

abbas ali September 8, 2012 at 4:14 PM

Simply amazing.. a brilliant post!
Reply

Pradeep Jain October 5, 2012 at 2:20 PM

Hi Gautham,
Thanks a lot.
I tried the procedure for xls files - which are created in excel 2003.
It does not open when we save the file with the extension of .zip.
Is there any other method of opening these files.
Thanks,
Pradeep
Reply

Flow October 31, 2012 at 4:20 AM

it is not working for an excel 2010 file (.xlsm). it is dumping the vba projects and saying they
have been corrupted.
Reply

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Norm November 15, 2012 at 12:14 AM

Works great, thanks so much!
Reply

david sam January 11, 2013 at 11:09 AM

Hey Buddy... Thanks a mill.. Your guidance rocked... hurray
Reply

rajeshkumar January 11, 2013 at 12:40 PM

This comment has been removed by the author.
Reply

rajeshkumar January 11, 2013 at 1:15 PM

Working Dude Thanks a lot....
Reply

Marcelinho January 31, 2013 at 8:50 AM

awesome.
It really works.
Reply

rajeev February 4, 2013 at 10:19 AM

Thanks it works like awesome..........
Reply

Nipesh Shah March 1, 2013 at 9:23 AM

It works great for 2010 for xls file I open and save it as xlsm which opens fine as xls but
vbaProject does not cracked properly.

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Reply

purvesh prajapati March 26, 2013 at 12:36 PM

It works for me and got good knowledge as well. I circulate to my collegues as well.
it is very useful.
Reply

Unknown April 18, 2013 at 8:32 PM

Its Works fine!! Meus Parabens. Abraos do Brazil
Reply

The Warner Family May 1, 2013 at 8:19 AM

Thanks very much
Reply

DR DEATH May 4, 2013 at 10:25 AM

I won't be able to recover the password. I'll be very obliged if you crack my vba's password.
Kindly send me your email address at [email protected]
Reply

Huzefa Raja May 8, 2013 at 12:34 PM

I won't be able to recover the password.
i will be very thankful to you if help me on this.
please send me your email.
[email protected]
Reply

Huzefa Raja May 8, 2013 at 2:07 PM

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Thank you very much!

Its working now for me!
Great Post!
Reply

Moises Angelus May 15, 2013 at 4:45 PM

Hi Gautam,
Is working fine, is just fantastic!
Thank you so much!
P.s There is a way to recover the password?
Reply

dipesh May 20, 2013 at 1:47 AM

Thanks a ton...it worked gr8....cab u tell mw how to open a file which i s password protected
Reply

Raj May 26, 2013 at 3:05 PM

Thanks a lot. Worked like a charm.
Reply

Phunita Thevathesan June 6, 2013 at 1:26 PM

Thank You very much
Reply

xxyyzz54 June 19, 2013 at 8:53 PM

Hi, this trick is amazing and it work for me for long time, but not work anymore in Windows 7
x64 and Office 2013 x64.
I have compared files one protected and other not protected and I find 2 XML files and

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

vbaProject.bin with some different code lines in it.

There are another project about it?
Thanks a lot for to answer me!
Vilmar
Reply

jj sjja July 3, 2013 at 11:23 PM

Thanks a lot.
damet garm
in kharejia ajab mokhi daran
Reply

Chupa Chups August 2, 2013 at 7:42 PM

Thanks for the info. I wish I found it earlier. A few years back, your company had some locked
VBA projects created by a fired employee. We had to use the service called www.passswordfind.com to remove the VBA passwords - we had to buy a license to use the service, but it did
well and did the trick. Hope this information will prove useful to someone if you do not succeed
with this method..
Reply
Replies
Van Fin September 19, 2013 at 2:42 PM
Stop advertising that software all over the web. IT DOESN'T WORK!!!
Reply

Newton August 20, 2013 at 5:59 PM

This comment has been removed by the author.
Reply

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Newton August 20, 2013 at 6:11 PM

Happy to see everyone can get that... but sorry i couldnt get that. I search for DPB but text not
found I am using Free Hex Editor Neo. Did I miss anything... Anybody pls help me out ???
Reply

Anna September 5, 2013 at 11:41 AM

This comment has been removed by the author.
Reply

Shashank Sangal December 20, 2013 at 12:52 PM

Yes it works.. Gotcha.. Superb
@Newton : Dude just read the above blog completely and follow the exact steps. I am dead
sure, it will surely works for you also.
Reply

Ben Waugh December 22, 2013 at 11:09 PM

Get Best VBA Password Recovery Software to recover VBA password via this link
http://gallery.technet.microsoft.com/VBA-Password-Recovery-eac07070
Reply

Ben Waugh December 22, 2013 at 11:10 PM

Recover VBA Password http://mail2web.com/plugins/addons/addins-for-excel/passwordrecovery/vba-password-recovery_1206.html
Reply

Rodrigo Varela January 3, 2014 at 12:00 AM

That's why we have genius for. Tks man. The best.
Reply

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Tomas Oliyan January 15, 2014 at 3:01 PM

Dear Gautam, thanks a lot. This is very simple to understand and easy to use. Great!
Reply

aliva devid February 6, 2014 at 5:19 PM

This comment has been removed by a blog administrator.
Reply

Manikandan February 12, 2014 at 4:08 AM

Awesome. It works perfectly. Thanks Gautam !!
Reply

bhuvanesh February 14, 2014 at 11:00 PM

Cool, awesome! Works perfect.
Reply

ciota devid February 17, 2014 at 3:58 PM

This comment has been removed by a blog administrator.
Reply
Replies
elis smith March 19, 2014 at 5:55 PM
Yes,
I used This software to recover my Excel password & unlock Excel spreadsheet
within just few minutes.
PDS ( Perfect Data Solutions )
Thanks You
Reply

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Saidkamol Kurbanov March 24, 2014 at 10:18 PM

Gooooooood Jooooooob THX
Reply

jack Methue April 22, 2014 at 5:52 PM

The given solution is working properly so thanks for providing this informative web page with
best solution. I also used the given solution for microsoft office password recovery.
Reply

kiran kumar April 30, 2014 at 7:08 PM

Excelent info thx
Reply

sylvie james May 15, 2014 at 5:01 PM

Thank you for nice posting. To unlock Excel password really this is a very helpful tool in which a
user recover Excel password and by this office password recovery it gives instant solution to
recover password from the MS Office.
Reply

Edgar Zavala May 22, 2014 at 10:19 PM

Thank you so much! It worked perfectly
Reply

payam_prz August 23, 2014 at 5:31 AM

You are a genious! Thanks!
Reply

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Rifqi Anshory November 13, 2014 at 7:20 AM

thanks a lot
Reply

J Barretta November 20, 2014 at 11:31 PM

Worked great using a 2010 xlsm. FYI I did not need to open the bin file using a hex editor. I
opened it using Notepad++ and edited/saved it no problem.
Reply

Enter your comment...

Comment as:

Publish

Select profile...

Preview

Newer Post

Home

Older Post

Subscribe to: Post Comments (Atom)

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Top

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com