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 :)

Answer 2

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.

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.

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.

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

Answer 3

You can introduce this behavior by doing the "convert to .zip and unzip trick", and then replacing the vbaProject.bin file (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.

Answer 4

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

Call 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)

and added one line of code to my subroutine BuildCodeString:

Set wsSource = ThisWorkbook.Sheets(sSource)

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

Answer 5

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:

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 Reference you had in original file
8. Save and test if everything's fine

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

Answer 6

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.

Answer 7

My error was caused when I passed a worksheet to a function and then used that variable name later on in a for loop as the loop variable (because I'm a bad and lazy programmer.) I had Option Explicit in the module so I don't think it knew what to do with the references.

As an alternative to completely reworking the workbook:

1. Copy all the worksheets to a new workbook
2. Copy all the modules, forms and classes to that as well
3. Save that as an .xlsm
4. Change both of the file names (new and old) to .zip
5. Copy the file "xl/vbaProject.bin" from the new one to the old one
6. Change the file name back to .xlsm

This sounds like you just copied everything anyway but this means that you don't have to check links, named ranges, conditional formatting or data validation to ensure everything copied over correctly.

Answer 8

Maybe someone still need this... i managed to code the solution... basically it creates a copy of your vba and excel copy to another one in your desktop.

:)

References:

1. Microsoft Visual Basic for Applications Extensibility 5.3

2. Microsoft Scripting Runtime

3. Microsoft VBScript Regular Expressions 5.5

4. Microsoft Windows Common Controls 6.0

    Sub CopiarHojasANuevoLibro()
        ' Desactivar actualizaciones de pantalla, alertas, eventos e interacción con el usuario
        ' Disable screen updates, alerts, events and user interaction
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        Application.Interactive = False
   
        ' Exportar los módulos de código al archivo
        ' Export code modules to file
        Call ExportModules
   
        ' Crear un nuevo libro de Excel
        ' Create a new Excel workbook
        Dim wbNuevo As Workbook
        Set wbNuevo = Workbooks.Add
   
        ' Renombrar la primera hoja del nuevo libro
        ' Rename the first sheet of the new workbook
        wbNuevo.Sheets(1).Name = "zzzzzzzzz"
   
        ' Copiar cada hoja del libro actual al nuevo libro
        ' Copy each sheet from the current workbook to the new workbook
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            ws.Copy After:=wbNuevo.Sheets(wbNuevo.Sheets.Count)
        Next ws
   
        ' Eliminar la primera hoja del nuevo libro
        ' Delete the first sheet of the new workbook
        wbNuevo.Sheets(1).Delete
   
        ' Activar el nuevo libro y realizar la importación de los módulos de código
        ' Activate the new workbook and import the code modules
        wbNuevo.Activate
        Call ImportModules
   
        ' Definir la ruta y el nombre del archivo de destino
        ' Define the path and name of the destination file
        Dim rutaArchivo As String
        rutaArchivo = "C:\Users\" & CStr(Environ$("Username")) & "\Desktop\CodeSaver " & Format(Date, "DD-MM-YYYY") & ".xlsm"
   
        ' Eliminar el archivo existente si existe
        ' Delete existing file if it exists
        On Error Resume Next
        Kill rutaArchivo
        On Error GoTo 0
   
        ' Guardar el libro activo en la ubicación especificada con el formato de archivo xlOpenXMLWorkbookMacroEnabled
        ' Save the active workbook to the specified location in the file format xlOpenXMLWorkbookMacroEnabled
        ActiveWorkbook.SaveAs Filename:=rutaArchivo, FileFormat:=xlOpenXMLWorkbookMacroEnabled
   
        ' Cerrar el libro activo guardando los cambios
        ' Close the active workbook saving changes
        ActiveWorkbook.Close SaveChanges:=True
   
        ' Restaurar las configuraciones originales de la aplicación
        ' Restore original application settings
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.Interactive = True
   
        ' Activar el libro original
        ' Activate the original workbook
   
        ThisWorkbook.Activate End Sub
   
    Public Sub ExportModules()
        ' Variables para exportar módulos de código
        ' Variables to export code modules
        Dim bExport As Boolean
        Dim wkbSource As Excel.Workbook
        Dim szSourceWorkbook As String
        Dim szExportPath As String
        Dim szFileName As String
        Dim cmpComponent As VBIDE.VBComponent
   
        ' Comprobar si la carpeta de destino existe
        ' Check if the destination folder exists
   
        If FolderWithVBAProjectFiles = "Error" Then
            MsgBox "Export Folder Not exist"
            Exit Sub
        End If
   
        ' Eliminar los archivos existentes en la carpeta de destino
        ' Delete existing files in the destination folder
   
        On Error Resume Next
        Kill FolderWithVBAProjectFiles & "\*.*"
        On Error GoTo 0
   
        ' Obtener el nombre del libro de origen y establecer una referencia al libro de origen
        ' Get the name of the source workbook and set a reference to the source workbook
        szSourceWorkbook = ThisWorkbook.Name
        Set wkbSource = Application.Workbooks(szSourceWorkbook)
   
        ' Comprobar si el proyecto VBA del libro de origen está protegido
        ' Check if the source workbook VBA project is protected
        If wkbSource.VBProject.Protection = 1 Then
            MsgBox "The VBA in this workbook Is protected," & _
                   "not possible To export the code"
            Exit Sub
        End If
   
        ' Establecer la ruta de exportación como la carpeta de destino
        ' Set the export path as the destination folder
        szExportPath = FolderWithVBAProjectFiles & "\"
   
        ' Iterar a través de los componentes de VBA del libro de origen
        ' Iterate through the VBA components of the source workbook
        For Each cmpComponent In wkbSource.VBProject.VBComponents
            bExport = True
            szFileName = cmpComponent.Name
   
            ' Asignar la extensión de archivo adecuada según el tipo de componente
            ' Assign the appropriate file extension based on the component type
            Select Case cmpComponent.Type
                Case vbext_ct_ClassModule
                    szFileName = szFileName & ".cls"
                Case vbext_ct_MSForm
                    szFileName = szFileName & ".frm"
                Case vbext_ct_StdModule
                    szFileName = szFileName & ".bas"
                Case vbext_ct_Document
                    ' No exportar componentes de tipo Document
                    ' Do not export components of type Document
                    bExport = False
            End Select
   
            ' Exportar el componente si está marcado para exportación
            ' Export the component if it is marked for export
            If bExport Then
                cmpComponent.Export szExportPath & szFileName
            End If
        Next cmpComponent
   
        Debug.Print "Export Is ready" End Sub
   
    Public Sub ImportModules()
        ' Variables para importar módulos de código
        ' Variables to import code modules
        Dim wkbTarget As Excel.Workbook
        Dim objFSO As Scripting.FileSystemObject
        Dim objFile As Scripting.file
        Dim szTargetWorkbook As String
        Dim szImportPath As String
        Dim szFileName As String
        Dim cmpComponents As VBIDE.VBComponents
   
        ' Comprobar si el libro activo es el mismo que el libro de origen
        ' Check if the active workbook is the same as the source workbook
        If ActiveWorkbook.Name = ThisWorkbook.Name Then
            MsgBox "Select another destination workbook" & _
                   "Not possible To import in this workbook "
            Exit Sub
        End If
   
        ' Comprobar si la carpeta de origen existe
        ' Check if the source folder exists
        If FolderWithVBAProjectFiles = "Error" Then
            MsgBox "Import Folder Not exist"
            Exit Sub
        End If
   
        ' Obtener el nombre del libro de destino y establecer una referencia al libro de destino
        ' Get the name of the destination workbook and set a reference to the destination workbook
        szTargetWorkbook = ActiveWorkbook.Name
        Set wkbTarget = Application.Workbooks(szTargetWorkbook)
   
        ' Comprobar si el proyecto VBA del libro de destino está protegido
        ' Check if the target workbook VBA project is protected
        If wkbTarget.VBProject.Protection = 1 Then
            MsgBox "The VBA in this workbook Is protected," & _
                   "not possible To Import the code"
            Exit Sub
        End If
   
        ' Establecer la ruta de importación como la carpeta de origen
        ' Set the import path as the source folder
        szImportPath = FolderWithVBAProjectFiles & "\"
   
        ' Crear un objeto FileSystemObject para trabajar con archivos
        ' Create a FileSystemObject to work with files
        Set objFSO = New Scripting.FileSystemObject
   
        ' Comprobar si hay archivos para importar en la carpeta de origen
        ' Check if there are files to import in the source folder
        If objFSO.GetFolder(szImportPath).Files.Count = 0 Then
            MsgBox "There are no files To import"
            Exit Sub
        End If
   
        ' Eliminar los módulos de código y formularios de usuario existentes en el libro de destino
        ' Delete existing code modules and userforms in the target workbook
        Call DeleteVBAModulesAndUserForms
   
        ' Obtener una referencia a los componentes de VBA del libro de destino
        ' Get a reference to the VBA components of the target workbook
        Set cmpComponents = wkbTarget.VBProject.VBComponents
   
        ' Iterar a través de los archivos en la carpeta de origen
        ' Iterate through the files in the source folder
        For Each objFile In objFSO.GetFolder(szImportPath).Files
            ' Importar el archivo si tiene una extensión válida
            ' Import the file if it has a valid extension
            If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _
               (objFSO.GetExtensionName(objFile.Name) = "frm") Or _
               (objFSO.GetExtensionName(objFile.Name) = "bas") Then
                cmpComponents.Import objFile.Path
            End If
        Next objFile
   
        Debug.Print "Import Is ready" End Sub
   
    Function FolderWithVBAProjectFiles() As String
        ' Función para obtener la carpeta de destino de los archivos VBA
        ' Function to get the destination folder of the VBA files
        Dim wshShell As Object
        Dim fso As Object
        Dim SpecialPath As String
   
        ' Crear objetos Shell y FileSystemObject
        ' Create Shell and FileSystemObject objects
        Set wshShell = CreateObject("WScript.Shell")
        Set fso = CreateObject("scripting.filesystemobject")
   
        ' Obtener la ruta de la carpeta "Mis documentos"
        ' Get the path of the "My Documents" folder
        SpecialPath = wshShell.SpecialFolders("MyDocuments")
   
        ' Agregar una barra diagonal si no está presente al final de la ruta
        ' Add a forward slash if not present at the end of the path
        If Right(SpecialPath, 1) <> "\" Then
            SpecialPath = SpecialPath & "\"
        End If
   
        ' Crear la carpeta "VBAProjectFiles" si no existe
        ' Create the folder "VBAProjectFiles" if it doesn't exist
   
        If fso.FolderExists(SpecialPath & "VBAProjectFiles") = False Then
            On Error Resume Next
            MkDir SpecialPath & "VBAProjectFiles"
            On Error GoTo 0
        End If
   
        ' Devolver la ruta de la carpeta "VBAProjectFiles" si existe, o "Error" si no existe
        ' Return the path of the "VBAProjectFiles" folder if it exists, or "Error" if it doesn't
        If fso.FolderExists(SpecialPath & "VBAProjectFiles") = True Then
            FolderWithVBAProjectFiles = SpecialPath & "VBAProjectFiles"
        Else
            FolderWithVBAProjectFiles = "Error"
        End If End Function
   
    Function DeleteVBAModulesAndUserForms()
        ' Función para eliminar los módulos de código y formularios de usuario existentes
        ' Function to remove existing code modules and userforms
        Dim VBProj As VBIDE.VBProject
        Dim vbComp As VBIDE.VBComponent
   
        ' Obtener una referencia al proyecto VBA del libro activo
        ' Get a reference to the VBA project of the active workbook
        Set VBProj = ActiveWorkbook.VBProject
   
        ' Iterar a través de los componentes de VBA en el proyecto
        ' Iterate through the VBA components in the project
   
        For Each vbComp In VBProj.VBComponents
            ' Eliminar el componente si no es de tipo Document
            ' Delete the component if it is not of type Document
            If vbComp.Type = vbext_ct_Document Then
            Else
                VBProj.VBComponents.Remove vbComp
            End If
        Next vbComp End Function