It is not unusual for people other than the creators/developers of Excel spreadsheets to take ownership of these tools, especially in a business environment. These transitions of ownership usually go smooth. But there are definitely times that can cause some real headaches for the new owners.
We receive emails similar to the following all the time:
“Hi, I am working for [XYZ] company and one of our staff have recently left. I need to modify one of their excel files but it is password protected and we do not know the password. We tried to contact them, but have not had any response (or they do not remember the password). I urgently need to update this file. Are you able to help or tell me how to unprotect a VBA project in Excel without the password?”
The good news if you are in this situation is: first off, don’t worry, all is not lost. With a bit of tinkering around behind the scenes, it is possible to unprotect a VBA project in Excel without the password.
ExcelSuperSite Ultimate Guide – How to Unprotect an Excel Spreadsheet (when I have forgotten the password)
Here at ExcelSuperSite, we come across many situations where our clients have locked their spreadsheets to prevent access or accidental changes being made, but then, forget or lose access (staff leave and don’t share passwords etc) to the passwords used.
So often in fact that we created the following Ultimate Guide.
How to Unprotect an Excel Spreadsheet (when I have forgotten the password)
This guide presents simple to understand and follow, step-by-step instructions to lead you through all the steps you need to regain full access to Excel spreadsheets.
*.xls (Excel 97-2003 workbook)
1. Download and install a HEX Editor onto your computer. There are many free versions available to you – we personally like using the Freeware Hex Editor XVI32.
2. Open your spreadsheet in XVI32
3. Search for the text “DPB=” (no quotes).
4. Modify “DPB=” to “DPx=” (no quotes)
5. Save the file and close the HEX Editor
6. Open your modified spreadsheet with Excel. When you first do this you will be prompted with quite a number of Error Messages. This is perfectly OK. Keep clicking Yes / OK until they stop.
7. Try opening your VBA Project inside the Visual Basic Editor (use the Alt+F11 short-cut) and click OK if you receive any error messages.
8. In the left-hand Project window, right-click on the VBAproject name then select VBAProject Properties…
9. Click the Protection tab and uncheck the Lock project for viewing checkbox and delete any existing passwords if they exist in the Password boxes.
10. Save your spreadsheet as a different workbook – “original name – unprotected.xls” (or whatever you like. Just Save As a different spreadsheet).
11. Close out of your spreadsheet and re-open your unprotected version of the spreadsheet and your VBA code should now be visible in the Visual Basic Editor.
*.xlsm (Excel 2007-present macro-enabled workbook)
1. Rename the file extension of your spreadsheet from *.xlsm to *.zip and click Yes when prompted “Are you sure you want to change it?”.
2. Using any File Compression software of your choice, open your “zip” file.
There are many free versions of file compression software available – we like using 7-Zip, but you can use any software of your choice.
3. Locate and double click on the “xl” folder. Inside this folder you will find the file vbaProject.bin (or vbaProject.bas). Extract this file to a temporary location on your computer (i.e. to your Desktop).
NOTE:
DO NOT close your File Compression Software (i.e. 7-Zip) after this step as you will still need to use it in the following step 9.
4. Download and install a HEX Editor onto your computer. There are many free versions available to you – we personally like using the Freeware Hex Editor XVI32.
5. Open the vbaProject.bin (or vbaProject.bas) file in XVI32
6. Search for the text “DPB=” (no quotes).
7. Modify “DPB=” to “DPx=” (no quotes)
8. Save the file and close the HEX Editor
9. Re-insert your saved and modified vbaProject.bin file (or vbaProject.bas) back into your zip file. After step 3 you should still have open your file in the File Compression Software (7-Zip) and you should still be in the “xl” directory – then from your desktop (or the temporary folder where you saved the modified vbaProject.bin file), left click (and hold) and “drag and drop” this file into the 7-Zip xl folder, overwriting the existing file in this location.
10. Rename your *.zip file back to *.xlsm.
11. Open your modified spreadsheet with Excel. When you first do this you will be prompted with quite a number of Error Messages. This is perfectly OK. Keep clicking Yes / OK until they stop.
12. Try opening your VBA Project inside the Visual Basic Editor (use the Alt+F11 short-cut) and click OK if you receive any error messages.
13. In the left-hand Project window, right-click on the VBAproject name then select VBAProject Properties…
14. Click the Protection tab and uncheck the Lock project for viewing checkbox and delete any existing passwords if they exist in the Password boxes.
15. Save your spreadsheet as a different workbook – “original name – unprotected.xls” (or whatever you like. Just Save As a different spreadsheet).
16. Close out of your spreadsheet and re-open your unprotected version of the spreadsheet and your VBA code should now be visible in the Visual Basic Editor.
*.xlsb (Excel 2007-present macro-enabled binary workbook)
The process to remove the password protection to view your VBA project code for a macro-enabled binary workbook is identical to that for a *.xlsm file except for 2 small steps.
Step 1 – Save your macro-enabled binary workbook to the *.xlsm spreadsheet format and then follow the step-by-step instructions for that particular file type.
*.xlsm (Excel 2007-present macro-enabled workbook) instructions
Step2 – Once you have finished step 1, simply “Save As” your file from *.xlsm format back to *.xlsb
ExcelSuperSite Ultimate Guide – How to Unprotect an Excel Spreadsheet (when I have forgotten the password)
Here at ExcelSuperSite, we come across many situations where our clients have locked their spreadsheets to prevent access or accidental changes being made, but then, forget or lose access (staff leave and don’t share passwords etc) to the passwords used.
So often in fact that we created the following Ultimate Guide.
How to Unprotect an Excel Spreadsheet (when I have forgotten the password)
This guide presents simple to understand and follow, step-by-step instructions to lead you through all the steps you need to regain full access to Excel spreadsheets.
