How to import code from a .cls file into a worksheet code module without the header info?

Question

I have a class module that dynamically updates all the code modules in distributed workbooks. The snippet below shows how I'm updating the "ThisWorkbook" module. This is basically deleting the existing code in the workbook module and then importing new code from a .CLS file.

The problem is, that the CodeModule.AddFromFile method copies the header info from the file as well.

I'm using CodeModule.DeleteLines to clean this up. I prefer to not bring this junk in to begin with.

Is there any way to ignore this header information when the code is added or, is there a different approach that would import the code without copying the header info?

Private Sub ImportNew()
    Dim vbP As VBIDE.VBProject
    Dim vbC As VBIDE.VBComponent
    Set vbP = Application.ThisWorkbook.VBProject
    
    Dim FSO As scripting.FileSystemObject
    Dim fsoFolder As scripting.Folder
    Dim fsoFile As scripting.File
    Set FSO = New scripting.FileSystemObject
    Set fsoFolder = FSO.GetFolder(pRep)
    
    For Each fsoFile In fsoFolder.Files
        Dim FileName As String
        Dim CodeName As String
        Dim FileDate As Date
    
        FileName = fsoFile.ShortName
        CodeName = Left(FileName, InStrRev(FileName, ".") - 1)
        FileDate = fsoFile.DateLastModified

        Select Case CodeName
            Case "ThisWorkbook"
                'Do some stuff to check version - update if needed:
                Dim Dest As VBIDE.CodeModule
                Set Dest = vbP.VBComponents("ThisWorkbook").CodeModule
                Dest.DeleteLines 1, Dest.CountOfLines 'Erase existing lines
                Dest.AddFromFile fsoFile.Path 'import new code from file
                'remove header info from file:
                Dim OpExLine As Long
                If Dest.Find("Option Explicit", OpExLine, 0, Dest.CountOfDeclarationLines, 999) Then
                    Dest.DeleteLines 1, OpExLine - 1
                End If
                    
            Case "Coder"
                'do nothing
            Case Else
                'Do some other stuff
        End Select
        'Do some other stuff
    Next
End Sub

user_9912714 · Answer

You can Import module files like this:

ThisWorkbook.VBProject.VBComponents.Import fsoFile.Path

Addendum

The OP pointed out that worksheet modules can not be imported. For that reason we need to remove the class headers. The last line of a worksheet's module header is always the same. Know this, it is simply a matter of splitting off the code by the header, removing the first element and rejoining the text.

Function RemoveClassHeader(Text As String) As String
    Const LastClassAttribute As String = "Attribute VB_Exposed = True"
    Dim Parts() As String
    Parts = Split(Text, LastClassAttribute)
    If UBound(Parts) > 0 Then Parts(0) = ""
    RemoveClassHeader= Join(Parts, "")
End Function

How to import code from a .cls file into a worksheet code module without the header info?

1 Answers

Addendum