VBA code Export to a Folder

Public Sub ExportModules()

    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

    ''' The code modules will be exported in a folder named.

    ''' MFExport in the Documents folder.

    ''' The code below create this folder if it not exist

    ''' or delete all files in the folder if it exist.

    

    On Error Resume Next

    MkDir "MFExport"

    On Error GoTo 0

    

    If FolderWithMFExport = "Error" Then

        MsgBox "Export Folder not exist"

        Exit Sub

    End If

    

    On Error Resume Next

        Kill FolderWithMFExport & "\*.*"

        

'        Dim oldfolder As String

'        Dim Newfolder As String

'        oldfolder = "MFExport"

'        Newfolder = "MFExportOLD"

'        Name oldfolder As Newfolder

        

    On Error GoTo 0

    ''' NOTE: This workbook must be open in Excel.

    szSourceWorkbook = ActiveWorkbook.Name

    Set wkbSource = Application.Workbooks(szSourceWorkbook)

    

    If wkbSource.VBProject.Protection = 1 Then

    MsgBox "The VBA in this workbook is protected," & _

        "not possible to export the code"

    Exit Sub

    End If

    

    szExportPath = FolderWithMFExport & "\"

    

    For Each cmpComponent In wkbSource.VBProject.VBComponents

        

        bExport = True

        szFileName = cmpComponent.Name

        ''' Concatenate the correct filename for export.

        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

                ''' This is a worksheet or workbook object.

                ''' Don't try to export.

                bExport = False

        End Select

        

        If bExport Then

            ''' Export the component to a text file.

            cmpComponent.Export szExportPath & szFileName

            

        ''' remove it from the project if you want

        '''wkbSource.VBProject.VBComponents.Remove cmpComponent

        

        End If

   

    Next cmpComponent

 

    Dim OldFolderName As String, NewFolderName As String

    

    '-- Set Folder Names

    OldFolderName = szExportPath

    NewFolderName = Replace(szExportPath, "MFExport\", "MFExport") + "_" + Format(Now(), "yyyymmdd") + "_" + Format(Now(), "HHMMSS")

    

    On Error Resume Next

        Kill NewFolderName

    '-- Rename them

    Name OldFolderName As NewFolderName

        

        

        

    MsgBox "Export is ready"

End Sub

Public Sub ImportModules()

    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

    If ActiveWorkbook.Name = ThisWorkbook.Name Then

        MsgBox "Select another destination workbook" & _

        "Not possible to import in this workbook "

        Exit Sub

    End If

    'Get the path to the folder with modules

    If FolderWithMFExport = "Error" Then

        MsgBox "Import Folder not exist"

        Exit Sub

    End If

    ''' NOTE: This workbook must be open in Excel.

    szTargetWorkbook = ActiveWorkbook.Name

    Set wkbTarget = Application.Workbooks(szTargetWorkbook)

    

    If wkbTarget.VBProject.Protection = 1 Then

    MsgBox "The VBA in this workbook is protected," & _

        "not possible to Import the code"

    Exit Sub

    End If

    ''' NOTE: Path where the code modules are located.

    szImportPath = FolderWithMFExport & "\"

        

    Set objFSO = New Scripting.FileSystemObject

    If objFSO.GetFolder(szImportPath).Files.Count = 0 Then

       MsgBox "There are no files to import"

       Exit Sub

    End If

    'Delete all modules/Userforms from the ActiveWorkbook

    Call DeleteVBAModulesAndUserForms

    Set cmpComponents = wkbTarget.VBProject.VBComponents

    

    ''' Import all the code modules in the specified path

    ''' to the ActiveWorkbook.

    For Each objFile In objFSO.GetFolder(szImportPath).Files

    

        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

    

    MsgBox "Import is ready"

End Sub

Function FolderWithMFExport() As String

    Dim WshShell As Object

    Dim FSO As Object

    Dim SpecialPath As String

    Set WshShell = CreateObject("WScript.Shell")

    Set FSO = CreateObject("scripting.filesystemobject")

' Define file Path

    'SpecialPath = WshShell.SpecialFolders("MyDocuments")

    SpecialPath = WshShell.SpecialFolders("c:/")

    

'//SpecialPath = "c:/temp"

    If Right(SpecialPath, 1) <> "\" Then

        SpecialPath = SpecialPath & "\"

    End If

    

    If FSO.FolderExists(SpecialPath & "MFExport") = False Then

        On Error Resume Next

        MkDir SpecialPath & "MFExport_" + Date

        On Error GoTo 0

    End If

    

    If FSO.FolderExists(SpecialPath & "MFExport") = True Then

        FolderWithMFExport = SpecialPath & "MFExport"

    Else

        FolderWithMFExport = "Error"

    End If

    

End Function

Function DeleteVBAModulesAndUserForms()

        Dim VBProj As VBIDE.VBProject

        Dim VBComp As VBIDE.VBComponent

        

        Set VBProj = ActiveWorkbook.VBProject

        

        For Each VBComp In VBProj.VBComponents

            If VBComp.Type = vbext_ct_Document Then

                'Thisworkbook or worksheet module

                'We do nothing

            Else

                VBProj.VBComponents.Remove VBComp

            End If

        Next VBComp

End Function