Excel – Recovering VBA modules from corrupt Excel workbook

microsoft-excel-2010vba

I have a workbook that suddenly got corrupted. I am trying to recover the VBA code from it. I can open the workbook in Protected View by opening a blank Excel instance and then using Excel to open the workbook, but when I click "Enable Editing" or try to open the workbook from Windows, it hangs.

I just want to recover the VBA. But when the workbook is in Protected View, I can't view the VBA project; it just doesn't appear in the VBE Project Explorer.

Any ideas?

Best Answer

sigil's solution has worked for me. Instead of steps 3-5, however, I found it safer to export the module(s) and then import them into the desired workbook. This also has the added advantage of having the macro(s) always available.

Follow these steps:

  1. Click the Start menu (Windows 7 and 8), and then type "excel /safe" in the Search box (right above Start: box has gray 'Search programs and files' text)

  2. Open the corrupted workbook

  3. Open the VBA editor (Alt+F11)

  4. Right-click on the module you want to recover and click 'Export File...'. Choose a location, type a name, and save your module. Repeat for all modules you will need.

  5. Close the corrupted workbook

  6. Create a new workbook or open the workbook in which you want to use the macro(s)

  7. Open the VBA editor (Alt+F11)

  8. Right-click on the VBA Project that has the filename of your workbook, and select Import File...'. Navigate to the exported VBA module and click Open. The module will now be a part of your workbook.

Related Question