Extracting VBA code from a closed, corrupt workbook

[HarveyS]

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?

 

[Fluff]

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]

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.
   
   
   
   
   
   
4. Right click on the original file, and Extract All. You will have a folder called as the workbook file name:
   
   
   
   
   
   
5. Right click on the Book1.xlsm, and select 7zip->Open Archive.
   
   
   
   
   
   
6. The archive will be opened in the 7zip explorer window. Go to xl folder in this window, and locate the vbaProject.bin file.
   
   
   
   
   
   
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.
   
   
   
   
   
   
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.
   
   
   

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.

 

[Jaspington]

I'd just like to say that @smozgur's reply worked exactly as advertised, to enable me to extract the VBA modules from an XLSB file I couldn't open. Do pay attention to the process as it's a bit finnicky, as one file is unzipped and the other isn't. But it was 100% successful for the job I needed, super work Mr Smozgur!

 

[smozgur]

I'd just like to say that @smozgur's reply worked exactly as advertised, to enable me to extract the VBA modules from an XLSB file I couldn't open. Do pay attention to the process as it's a bit finnicky, as one file is unzipped and the other isn't. But it was 100% successful for the job I needed, super work Mr Smozgur!

Glad to hear it helps, @Jaspington!

 

[mikeymay]

@smozgur you are a star!!

A file I was building yesterday came back today as corrupted and the VBA was inaccessible. This worked a treat and a few hours work has not been lost!

 

[smozgur]

A file I was building yesterday came back today as corrupted and the VBA was inaccessible. This worked a treat and a few hours work has not been lost!

Glad to hear that it helps, @mikeymay!

 

[FerdieB]

Everyone, I found this post very interesting, although from backup perspective. I've built a workbook I use to export all vba code in another workbook as files. I however want to import the modules into my backup workbook - but without opening the 'source' workbook as it may be damaged, or having extremely large data sets. My thinking is to do the same as described above, but using VBA to fully automate the process (Basically, select the source file using a filepicker window), and the modules appears in the backup VBAProject file, with a prefix of "_ to the module name. The modules need to be all types (cls, bas, frm, etc).

Has anyone done something like that?