Best way to replace VBA code in multiple files?

Question

I used to use something like this:

Dim vbaComponent As Variant
For Each vbaComponent In inputWorkbook.VBProject.VBComponents
    vbaComponent.CodeModule.DeleteLines 1, vbaComponent.CodeModule.CountOfLines
    vbaComponent.CodeModule.AddFromFile importComponentFileName
Next vbaComponent

This worked perfectly for some time but now it crashes when the Excel file gets saved. I guess the files got too big or something.

Is there better way to do this?

EDIT:

The problem seems to be frm and cls files. The replacement of bas files works perfectly.

EDIT2:

On some machines even bas files don't work.

EDIT3 (My current solution): So my current solution was simply doing it by hand once and recording all mouse and keyboard input and then replaying this over and over again.

If there is no proper solution to this I plan on creating an AutoIt script for this.

Answer 1

you will have to export/import components, because not all lines are exposed to CodeModule, here is sample

Private Sub exportImportComponent(Project1 As VBIDE.VBProject, Project2 As VBIDE.VBProject)
    Dim i As Long, sFileName As String

    With Project1.VBComponents
        For i = 1 To .Count
            sFileName = "C:\Temp\" & .Item(i).Name
            Select Case .Item(i).Type
                Case vbext_ct_ClassModule
                    .Item(i).Export sFileName & ".cls"
                    Project2.VBComponents.Import sFileName & ".cls"

                Case vbext_ct_StdModule
                    .Item(i).Export sFileName & ".bas"
                    Project2.VBComponents.Import sFileName & ".bas"

                Case vbext_ct_MSForm
                    .Item(i).Export sFileName & ".frm"
                    Project2.VBComponents.Import sFileName & ".frm"

                Case Else
                    Debug.Print "Different Type"
            End Select
        Next
    End With
End Sub