Extracting VBA code from a closed, corrupt workbook

HarveyS

New Member
Joined
Nov 6, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have a large excel workbook that has become corrupt, and I am unable to open it (even in safe mode), without excel crashing or giving me an out of memory error.
Is there a way to extract the VBA code from the workbook without actually opening it?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
When you tried to open the file in safe mode was calculation set to automatic? If so try it again but change to manual calc before trying to open the problem workbook.
Another possible option is to download & install OpenOffice & try to open the file with that.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,400
As an additional suggestion to @Fluff's suggestions which will likely solve the problem quicker, a VBA project can be extracted, and copied to another workbook without opening it in Excel. However, it wouldn't work for the Excel files saved in the older versions, older than 2010 perhaps.

Obviously, the VBA project will remain locked if it is password protected in the original workbook.

Tools needed: An archive tool like 7zip as normal compression won't work.

I cannot guarantee that it will work for your file but following procedure successfully worked for me.

  1. Create a temporary working folder and copy the original file here (1150459.xlsm), so you are working on a backup copy in an isolated folder.
  2. Also create another empty macro enabled workbook. Do not forget to insert an empty standard module in this new workbook and save it with this dummy module (Book1.xlsm).
  3. Change the original workbook names by adding .zip extension to the full file names.

    step1.png


  4. Right click on the original file, and Extract All. You will have a folder called as the workbook file name:

    step2.png


  5. Right click on the Book1.xlsm, and select 7zip->Open Archive.

    step3.png


  6. The archive will be opened in the 7zip explorer window. Go to xl folder in this window, and locate the vbaProject.bin file.

    step4.png


  7. Go back to the previously extracted folder in Windows explorer, go to the same path, xl, and find the same file, vbaProject.bin. Make sure keeping the 7zip explorer window accessible as you'll move this particular file into the 7zip explorer window. Basically keep them side by side.

    step5.png


  8. Drag the vbaProject.bin file and drop in the Book1.xlsm.zip/xl/ folder in the 7zip explorer window. It will ask for overwrite confirmation. Accept it. Quit 7zip.
  9. Rename Book1.xlsm.zip as Book1.xlsm, and try to open the file.

    step6.png

Note: Actually you can skip renaming the file names by adding .zip extension, and open both xlsm file in 7zip as archive, drag necessary file from the original to the Book1.xlsm directly, however it sometimes doesn't work due to different platforms or versions, so I tried to explain with rename procedure.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,394
Messages
5,636,050
Members
416,894
Latest member
Hari1992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top