Excel VBA 项目已生成多个 Workbook 对象

Excel VBA Project has generated multiple Workbook objects

提问by Alex Berry

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.

我负责一个非常大的 Excel 2010 电子表格，其中包含指向各种外部数据源的链接，包括 Bloomberg、带有 vba 模块的 65 个工作表以及对其他 vba 加载项的引用。

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

我注意到 VBA 项目已经获得了多个工作簿对象。

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.

有标准的ThisWorkbook。但是，许多工作表也已被 Excel 转换为 Workbook 对象，将原始工作表保留为前一个工作表的副本，减去代码。

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!

这似乎不是任何人行为的结果。事实上，我认为不可能有多个 Workbook 对象！

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.

例如，我有一个名为 wksInputs 的工作表，它现在已经变成了一个 Workbook 对象，而原来的 wksInputs 现在称为 wksInputs1。

I can't delete the wksInputs Workbook object.

我无法删除 wksInputs Workbook 对象。

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

请有人帮助解释这里发生了什么，以及我如何能够解决这个问题......？

Many thanks.

非常感谢。

采纳答案by James

This problem occurred in my code when I passed a worksheet to a Sub as a parameter, like this:

当我将工作表作为参数传递给 Sub 时，我的代码中出现了这个问题，如下所示：

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

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

To fix the problem, I created a new workbook, copied all the data from all the legitimate sheets in my original into identically named sheets in my new workbook. Then copied all the code from the original to the new workbook.

为了解决这个问题，我创建了一个新工作簿，将原始工作簿中所有合法工作表中的所有数据复制到新工作簿中同名工作表中。然后将原始代码中的所有代码复制到新工作簿中。

Then I changed the subroutine call to

然后我将子程序调用更改为

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

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

and added one line of code to my subroutine BuildCodeString:

并在我的子程序 BuildCodeString 中添加了一行代码：

Set wsSource = ThisWorkbook.Sheets(sSource)

设置 wsSource = ThisWorkbook.Sheets(sSource)

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

我不知道是什么导致了这个问题，但这个解决方法对我有用。

回答by Scoox

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

注意：原始损坏的文件已保存为.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 .xlsbformat 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

1. 打开空白 Excel 工作簿
2. 开发人员选项卡 > 宏安全 > 禁用所有宏而不通知
3. 关闭 Excel
4. 双击损坏的文件，例如 MyFile.xlsm
5. 文件 > 另存为... > MyFile.xlsb（不是 .xlsm），选择.xlsb格式有什么用
6. 开发人员选项卡 > 宏安全 > 启用所有宏（或您喜欢的任何安全级别）
7. 关闭 Excel
8. 双击 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.

该文件现已修复！如果需要，您可以将 MyFile.xlsb 文件重新保存为 .xlsm。根据我的经验，.xlsm 文件很容易损坏，因此我将养成始终使用 .xlsb 格式的习惯。

Hope somebody finds this helpful :)

希望有人觉得这有帮助:)

回答by Damian Fennelly

I had the same issue with a file that had multiple workbook objects and was generating the "Automation error - Catastrophic failure" error when opened.

我有一个具有多个工作簿对象的文件，并且在打开时生成“自动化错误 - 灾难性故障”错误。

I saved the *.xlsm file as an *.xlsb. When I re-opened the *.xlsb file all the workbook objects were still in the file. I reasonably assumed that the errors in the file were going to cause problems eventually and resigned myself to copy everything to a new file.

我将 *.xlsm 文件保存为 *.xlsb。当我重新打开 *.xlsb 文件时，所有工作簿对象仍在文件中。我合理地假设文件中的错误最终会导致问题，并辞职将所有内容复制到新文件中。

However when I closed the *.xlsb file and re-opened the original *.xlsm all the objects had disappeared and the file wasn't generating the "Automation error - Catastrophic failure" error.

但是，当我关闭 *.xlsb 文件并重新打开原始 *.xlsm 时，所有对象都消失了，并且该文件没有生成“自动化错误 - 灾难性故障”错误。

Weird I admit but the problem still persisted in the *.xlsb file but the original *.xlsm (which is the one I was trying to save) was fine.

我承认很奇怪，但问题仍然存在于 *.xlsb 文件中，但原始的 *.xlsm（这是我试图保存的文件）很好。

It may be a one off but it might be worth a try...

这可能是一次性的，但可能值得一试......

回答by PBeezy

You can introduce this behavior by doing the "convert to .zip and unzip trick", and then replacing the vbaProject.binfile (the macro code file) with a vbaProject.bin from a different project that has a different sheet structure. Don't know if that's what happened to OP, but it's what happened to me.

您可以通过执行“转换为 .zip 和解压缩技巧”来引入此行为，然后将vbaProject.bin文件（宏代码文件）替换为来自具有不同工作表结构的不同项目的 vbaProject.bin。不知道这是否是发生在 OP 上的事情，但这就是发生在我身上的事情。

回答by Kar.ma

I had the same issue in PowerPoint (2007), where "Slide1" was empty and couldn't be removed. The answer by @Scoox pointed me to a solution:

我在 PowerPoint (2007) 中遇到了同样的问题，其中“Slide1”为空且无法删除。@Scoox 的回答为我指出了一个解决方案：

1. Export all VBA Modules into text (.bas) files
2. Save the .pptm (or .xlsm) file as a .pptx (or .xlsx)
3. Close PowerPoint (or Excel)
4. Open this .pptx/.xlsx and save it back as a .pptm/.xlsm
5. Import the original VBA text (.bas) files
6. Manually re-associate all buttons to original macro functions
7. Add any external Referenceyou had in original file
8. Save and test if everything's fine

1. 将所有 VBA 模块导出为文本 (.bas) 文件
2. 将 .pptm（或 .xlsm）文件另存为 .pptx（或 .xlsx）
3. 关闭 PowerPoint（或 Excel）
4. 打开此 .pptx/.xlsx 并将其另存为 .pptm/.xlsm
5. 导入原始 VBA 文本 (.bas) 文件
6. 手动将所有按钮重新关联到原始宏功能
7. 添加您在原始文件中的任何外部参考
8. 保存并测试是否一切正常

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

这对我有用，我相信它也适用于 Excel。

回答by Beni

Had the same issue with Office365, found a mistake where I had the same name for a public constant and a parameter for a function. After changing the parameter name in the function it did not happen again.

Office365 也有同样的问题，发现一个错误，我的公共常量和函数参数的名称相同。更改函数中的参数名称后，它不再发生。