66

How can I programmatically remove a (known) password from an Excel VBA project?

To be clear: I want to remove the password from the VBA Project, not the workbook or any worksheets.

braX
  • 11,506
  • 5
  • 20
  • 33
Barry-Jon
  • 1,341
  • 4
  • 13
  • 16
  • Did some googling - it seems there is no direct way - you have to use a sendkeys kludge [http://www.mrexcel.com/forum/showthread.php?t=49034](http://www.mrexcel.com/forum/showthread.php?t=49034) – DJ. Nov 07 '08 at 16:44
  • 4
    The marked answer is NOT the best.... the best answer has come years later by Uygar Y – Jeremy Thompson Apr 16 '13 at 04:41
  • 4
    @JeremyThompson - that answer does not answer this question. It explains how to manually remove an unknown password. This question asks how to _programmatically_ remove a _known_ password. – Charles Wood Oct 18 '13 at 15:02
  • [**if none of the below answers work for you try this one**](http://stackoverflow.com/questions/19670057/bypass-password-on-an-excel-vba-project-xla/19704979#19704979) –  Oct 31 '13 at 11:47
  • Office 365 Excel (2016 at this point in time of this msg). Performed edit using Notepad++ (x64) w/ Hexedit plugin (x64) 1) Using 7-zip, open xl file via rt clk ' open archive * ' 2) Drill down into ' xl ' 3) Rt clk ' vbaProject.bin ', open as: ' open outside * ' 4) Open using Notepadd++ 5) Switch to HexEdit 6) Find DPB= and rename to DPX= 7) Save edit and update archive 8) Open xl file, select yes wen prompted to continue (xl found error) 9) Create new pwd in visual basic (found under dev/visual basic: tools/vbaproject properties) 10) Save and reopen. Viola! – turtlkky Jan 24 '19 at 17:56

6 Answers6

140

Another way to remove VBA project password is;

  • Open xls file with a hex editor. (ie. Hex Edit http://www.hexedit.com/)
  • Search for DPB
  • Replace DPB to DPx
  • Save file.
  • Open file in Excel.
  • Click "Yes" if you get any message box.
  • Set new password from VBA Project Properties.
  • Close and open again file, then type your new password to unprotect.

UPDATE: For Excel 2010 (Works for MS Office Pro Plus 2010 [14.0.6023.1000 64bit]),

  • Open the XLSX file with 7zip

If workbook is protected:

  • Browse the folder xl
  • If the workbook is protected, right click workbook.xml and select Edit
  • Find the portion <workbookProtection workbookPassword="XXXX" lockStructure="1"/> (XXXX is your encrypted password)
  • Remove XXXX part. (ie. <workbookProtection workbookPassword="" lockStructure="1"/>)
  • Save the file.
  • When 7zip asks you to update the archive, say Yes.
  • Close 7zip and re-open your XLSX.
  • Click Protect Workbook on Review tab.
  • Optional: Save your file.

If worksheets are protected:

  • Browse to xl/worksheets/ folder.
  • Right click the Sheet1.xml, sheet2.xml, etc and select Edit.
  • Find the portion <sheetProtection password="XXXX" sheet="1" objects="1" scenarios="1" />
  • Remove the encrypted password (ie. <sheetProtection password="" sheet="1" objects="1" scenarios="1" />)
  • Save the file.
  • When 7zip asks you to update the archive, say Yes.
  • Close 7zip and re-open your XLSX.
  • Click Unprotect Sheet on Review tab.
  • Optional: Save your file.
Uygar Y
  • 1,992
  • 1
  • 12
  • 16
  • This worked for me. I had to also click "Ok" a few times when viewing the VBA code. – The Dog May 03 '12 at 05:04
  • 6
    And one more addition to the original answer: If you have some xlsx file, open it in Excel and "Save As" xls. And then follow the above steps... – Uygar Y May 07 '12 at 12:01
  • 6
    If you have an `.xlsx` file you can also unzip it, do the search/replace on the `xl/vbaProject.bin` file and then re-zip. – Sander Steffann May 20 '13 at 17:01
  • 3
    This trick doesn't seem to work anymore in Excel 2010 (V 14.0.6129.5000). Although I can enter the VBA-Editor, on opening the module I get the error message "unexpected error (40230)" – Andreas J Aug 15 '13 at 15:16
  • You missed two very important words in the question: _programatically_ and _known_. – Charles Wood Oct 18 '13 at 15:01
  • 1
    You can still write a program and do the above steps in the application you write, can't you? – Uygar Y Oct 18 '13 at 15:44
  • Used this for access 2007 and 2010, works like a charm. Thank you – Arvid Vermote Apr 25 '14 at 08:26
  • what if project in Excel 2007 or 2010 is protected (with viewing project blocked) ? – Qbik May 18 '14 at 21:03
  • @UygarY does this work for unlocking a whole VBA project in Excel 2013 64 bit windows? – Learner Mar 29 '16 at 08:18
  • 2
    Just used this with Office Pro Plus 2016 (32-bit office on 64-bit os). Got the "40230" errors, but those stopped once I went to Project Properties and disabled/re-enabled password protection. – r2evans Jul 14 '17 at 22:55
  • If you're dealing with a .xltm file, I found first changing it to a .xlsm file worked using this strategy. – Parrish Husband Mar 14 '18 at 22:23
31

I found another way to solve this one to avoid password of VBA Project, without losing password.

If the file type is XLSM files:

  1. Rename the .xlsm as .zip (or you can open the .xlsm with 7zip/WinRAR) and open/extract it
  2. Edit the xl/vbaProject.bin file with Notepad++ or HexEdit
  3. Search for DPB= and replace it with DPx=

enter image description here

  1. Save the file
  2. Copy this file back into the .zip (or zip the files back up)
  3. Rename the archive back to .xlsm
  4. Open the file in Excel, if prompted to "Continue Loading Project", click Yes. If prompted with errors, click OK. Note: the code may still be blank or corrupted, which we fix in the next step
  5. Save the file as a new .xlsm file
  6. Open the new file in Excel (should be no errors now)
  7. Press Alt+ F11 to open the VBA editor

or

Follow this Step Also

I found another way to solve this one to avoid password of VBA Project, without losing excel password. use Hex-editor XVI32 for the process

if the file type is XLSM files:

  1. Open the XLSM file with 7-Zip (right click -> 7-Zip -> Open archive). 2. Copy the xl/vbaProject.bin file out of the file (you can drag and drop from 7-Zip), don't close 7-Zip

  2. Open the vbaProject.bin file with HexEdit

  3. Search for "DPB=" and replace it with "DPx="

  4. Save the file

  5. Copy this file back into 7-Zip (again, drag and drop works)

  6. Open the XLSX file in Excel, if prompted to "Continue Loading Project", click Yes. If prompted with errors, click OK. 8. Press Alt+ F11 to open the VBA editor.

  7. While press it will show error “Unexpected error (40230)”, just click OK (6 or 7 times) until it goes away.

  8. Then it will open Automatically

Thomas
  • 1,445
  • 14
  • 30
  • 5
    This trick worked for me...but with a small modification...at step:9 error was not going after 20-25 click also. I have saved the file into another one and it worked. – Anupam Maiti Jun 12 '18 at 11:32
  • 3
    This worked for me in Excel 2016 64 bit. And you don't really need a hex editor. You can search and replace (step 4) using notepad++ or any similar text editor. Previous comment is also true. "Unexpected error (40230)" goes away after saving as a new XLSM file and reopening excel. – Mike Feb 05 '19 at 09:39
  • I had a `xls` file and Excel 2016. Using Mikes help worked when I changed the file from `xls` to `xlsm`. I did not need `7-zip` to get a bin file. – Timo Aug 25 '20 at 14:42
  • With step 7 I get the following error: "Removed Part: /xl/vbaProject.bin part". Now all macros aren't present any more. What do I do wrong? – LB1234 Jun 03 '23 at 10:01
10

This has a simple method using SendKeys to unprotect the VBA project. This would get you into the project, so you'd have to continue on using SendKeys to figure out a way to remove the password protection: http://www.pcreview.co.uk/forums/thread-989191.php

And here's one that uses a more advanced, somewhat more reliable method for unprotecting. Again, it will only unlock the VB project for you. http://www.ozgrid.com/forum/showthread.php?t=13006&page=2

I haven't tried either method, but this may save you some time if it's what you need to do...

Jon Fournier
  • 4,299
  • 3
  • 33
  • 43
  • 1
    I have the code. What to do with? I can't open VBEditor, nor execute something, because the project is locked, so, any new script is not possible to execute!? – serhio Aug 05 '12 at 21:41
  • + 1 :) @serhio: Another way using [APIS](http://stackoverflow.com/questions/16174469/unprotect-vbproject-from-vb-code/16176557#16176557) – Siddharth Rout Apr 25 '13 at 09:07
  • 2
    [Uygar Y's answer below](https://stackoverflow.com/a/7835861/3358272) is still a relevant answer that works with Office 2016. The fact that it works completely and easily undermines whatever expectation one might have about using Office-passwords for securing documents. I've never had much faith in them, assuming that they could be disabled with hours of computer-work, but his answer worked for me with two *minutes* of work. The passwords keep the honest and non-computer-savvy out, but it is mostly snake-oil, IMHO. – r2evans Jul 14 '17 at 23:00
4

My 2 cents on Excel 2016:

  1. open the xls file with Notepad++
  2. Search for DPB= and replace it with DPx=
  3. Save the file
  4. Open the file, open the VB Editor, open modules will not work (error 40230)
  5. Save the file as xlsm
  6. It works
Timo
  • 2,922
  • 3
  • 29
  • 28
3

I found this here that describes how to set the VBA Project Password. You should be able to modify it to unset the VBA Project Password.

This one does not use SendKeys.

Let me know if this helps! JFV

JFV
  • 1,803
  • 6
  • 24
  • 38
1

After opening xlsm file with 7 zip, extracting vbaproject.bin and in Notepad ++ replacing DpB with DPx and re-saving I got a Lot of vbaproject errors and vba project password was gone but no code/forms.

I right clicked to export and was able to re-import to a new project.

geisterfurz007
  • 5,292
  • 5
  • 33
  • 54
Aveesh
  • 65
  • 1
  • 2
  • 6
  • Yep it's this simple, just replace DPB with DPX, 7zip it back up as an XLSM, open that file and you can export the modules 1x1 to .bas files. Open a new Excel file and import the modules 1x1 again and you have the readable code. Then you can reconstruct the VBA code you need from the original workbook with the VBA modules that you import. – Matt Apr 09 '22 at 15:17