Excel VBA Project has generated multiple Workbook objects

Question

I am responsible for a very large Excel 2010 spreadsheet with links to all sorts of external data sources including Bloomberg, 65 worksheets with vba modules and references to other vba add-ins.

I have noticed that the VBA project has acquired multiple Workbook Objects.

There's the standard ThisWorkbook. However, a number of the worksheets have also been turned into Workbook objects by Excel, leaving the original worksheet as a copy of the previous one, minus the code.

This doesn't appear to be a result of anyone's actions. Indeed, I didn't think it possible to have more than one Workbook object!

For example, I had one worksheet called wksInputs which has now been turned into a Workbook object, and the original wksInputs is now called wksInputs1.

I can't delete the wksInputs Workbook object.

Please could someone help explain what's going on here, and how I might be able to resolve the problem...?

Many thanks.

Answer 1

Here is my solution, it works consistently and you don't need to manually copy the sheets and code across to a blank workbook. I've tested this method on several corrupted workbooks that were giving me the "Automation error - Catastrophic failure" error upon launch.

NOTE: Original corrupted file was saved as .xlsm

1. Open a blank Excel workbook
2. Developer tab > Macro security > Disable all macros without notification
3. Close Excel
4. Double-click corrupted file, for example, MyFile.xlsm
5. File > Save as... > MyFile.xlsb (not .xlsm), choosing the .xlsb format is what does the trick
6. Developer tab > Macro security > Enable all macros (or whatever level of security you prefer)
7. Close Excel
8. Double-click MyFile.xlsb

The file is now fixed! You can re-save the MyFile.xlsb file as .xlsm if required. In my experience .xlsm files become corrupted quite easily, so I'm going to get into the habit of always using the .xlsb format.

Hope somebody finds this helpful :)