Excel VBA project creates multiple Workbook objects

Question

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

I noticed that the VBA project acquired several workbook objects.

There is a standard ThisWorkbook. However, a number of worksheets have also been turned into Excel workbook objects, leaving the original sheet as a copy of the previous one, minus the code.

This is not the result of any action. In fact, I did not think it was possible to have more than one Workbook object!

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

I cannot delete the wksInputs workbook object.

Please help me explain what is happening here, and how can I solve the problem ...?

Thank you very much.

Answer 1

This problem occurred in my code when I passed the sheet to Sub as a parameter, for example:

Call BuildCodeStrings (Sheet2, sAccount)
Sub BuildCodeStrings (wsSource As Worksheet, s As String)

To fix the problem, I created a new book, copied all the data from all the legal sheets in my original into sheets with the same name in my new book. Then I copied all the code from the original into a new book.

Then I changed the subroutine call to

Call BuildCodeStrings ("IC Accounts", sAccounts)
Sub BuildCodeStrings (sSource As String, s As String)

and added one line of code to my BuildCodeString routine:

Set wsSource = ThisWorkbook.Sheets (sSource)

I don't know what causes this problem, but this workaround worked for me.

Answer 2

Here is my solution, it works sequentially, and you do not need to manually copy the sheets and code into an empty book. I tested this method on several damaged books, which on startup gave me the error "Automation error - catastrophic error."

NOTE. The original damaged file was saved as .xlsm

• Open an empty Excel workbook
• developer tab> Macro Protection> Disable all macros without notification
• Close excel
• Double-click the damaged file, for example MyFile.xlsm
• File> Save As ...> MyFile.xlsb (not .xlsm), choosing the .xlsb format is what the trick does
• Developer tab> Macro Security> Enable all macros (or whatever security level you prefer)
• Close excel
• Double-click the MyFile.xlsb icon

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

Hope someone finds this helpful :)

Answer 3

I had the same problem with a file in which there were several objects of the workbook, and when I opened it, the error "Automation error - catastrophic failure" occurred.

I saved the * .xlsm file as * .xlsb. When I opened the * .xlsb file again, all the objects in the workbook were still in the file. I reasonably suggested that errors in the file could ultimately cause problems, and reconciled to copying everything to a new file.

However, when I closed the * .xlsb file and reopened the original * .xlsm file, all the objects disappeared and the file did not generate the "Automation Error - Catastrophic Failure" error.

I confess strangely, but the problem still persisted in the * .xlsb file, but the original * .xlsm file (which I tried to save) was in order.

It may be once, but it may be worth a try ...

Answer 4

I had the same problem in PowerPoint (2007), where "Slide1" was empty and could not be removed. @Scoox's answer pointed me to a solution:

• Export all VBA modules to text (.bas) files
• Save the .pptm file (.xlsm) as .pptx (or .xlsx)
• Close PowerPoint (or Excel)
• Open this .pptx / .xlsx and save it as .pptm / .xlsm
• Import VBA source files (.bas)
• Manually re-bind all buttons to the original macro functions
• Add the external link in the source file
• Save and check if everything is fine

This worked for me, I believe that it will work with Excel too.

Answer 5

I had the same problem with Office365, I found an error when I had the same name for a public constant and a parameter for a function. After changing the parameter name in the function, this did not happen again.