'Best way to replace VBA code in multiple files?

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.

excel vba

Solution 1:^[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

Solution 2:^[2]

I can assure everybody because I am working on this subject for years now (I gave up several times). When the code is programmatically modified either line-based or - what my preferred approach is 1. rename, 2. delete the renamed, 3. re-import from export file, Workbook Save will crash, will say Excel closes the Workbook. In fact my approach works most of the time but since it is unpredictable I learned to live with it. In most cases the code change has already successfully been done. So I just reopen the Workbook and continue.

The code I use. I just removed all the execution trace and execution log code lines but some lines may still look a bit cryptic:

    With rn_wb.VBProject
    
    '~~ Find a free/unused temporary name and re-name the outdated component
    If mComp.Exists(wb:=rn_wb, comp_name:=rn_comp_name) Then
        sTempName = mComp.TempName(tn_wb:=rn_wb, tn_comp_name:=rn_comp_name)
        '~~ Rename the component when it already exists
        .VBComponents(rn_comp_name).Name = sTempName
        .VBComponents.Remove .VBComponents(sTempName) ' will not take place until process has ended!
    End If
   
    '~~ (Re-)import the component
    .VBComponents.Import rn_raw_exp_file_full_name
    
    '~~ Export the re-newed Used Common Component
    Set Comp = New clsComp ' class module provides the export files full name
    With Comp
        Set Comp.Wrkbk = rn_wb
        .CompName = rn_comp_name
    End With
    .VBComponents(rn_comp_name).Export Comp.ExpFileFullName
    
    '~~ When Excel closes the Workbook with the subsequent Workbook save it may be re-opened
    '~~ and the update process will continue with the next outdated Used Common Component.
    '~~ The (irregular) Workbook close however may leave the renamed components un-removed.
    '~~ When the Workbook is opened again these renamed component may cause duplicate declarations.
    '~~ To prevent this the code in the renamed component is dleted.
    ' EliminateCodeInRenamedComponent sTempName ' this had made it much less "reliablele" so I uncommented it
    
    SaveWbk rn_wb ' This "crahes" every now an then though I've tried a lot

End With

Private Sub SaveWbk(ByRef rs_wb As Workbook)

Application.EnableEvents = False
DoEvents ' no idea whether this helps. coded in desparation. at least it doesn't harm 
rs_wb.Save
DoEvents ' same as above, not executed when Excel crashes
Application.EnableEvents = True

End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	R3uK
Solution 2	Walter Rauschenberger

'Best way to replace VBA code in multiple files?

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]