Export Modules to Folder

Export Modules

Export Modules to Folder

This will create a folder using the base name of your Excel file, which is the filename without its extension, along with a vba subfolder. Your VBA modules will be placed there.
Set Reference to Microsoft Visual Basic for Applications Extensibility

Sub ExportModules( _
    Optional PathToVBAModules As String = "" _
)
    
    Dim objMyProj As VBProject
    Dim objVBComp As VBComponent
    Dim strExt As String
    
    Set objMyProj = Application.VBE.ActiveVBProject
    
    If PathToVBAModules = "" Then
        MakeFolder _
            ThisWorkbook.Path & _
            "\" & _
            GetBaseName( _
                ThisWorkbook.Name _
            ) & _
            "\"
        MakeFolder _
            ThisWorkbook.Path & _
            "\" & _
            GetBaseName( _
                ThisWorkbook.Name _
            ) & _
            "\vba"
        PathToVBAModules = _
            ThisWorkbook.Path & _
            "\" & _
            GetBaseName( _
                ThisWorkbook.Name _
            ) & _
            "\vba\"
    Else
        ' Leave provided path
    End If
    
    For Each objVBComp In objMyProj.VBComponents
        Select Case objVBComp.Type
            Case vbext_ct_StdModule
                strExt = ".bas"
            Case vbext_ct_ClassModule
                strExt = ".cls"
            Case vbext_ct_MSForm
                strExt = ".frm"
            Case vbext_ct_Document
                strExt = ".txt"
        End Select
        objVBComp.Export PathToVBAModules & objVBComp.Name & strExt
    Next
        
End Sub

Leave a Reply

Your email address will not be published.