excelのvbaマクロのsrcを一括exportするvbaマクロ

標準のexcelでは、vbaマクロのsrcは一括exportできないらしく、 一括エクスポートするマクロが必要らしい。

step 1 - VBAの参照設定を追加

VBAの画面で、ツール→参照設定を開き、以下のライブラリを追加

• Microsoft Visual Basic for Applications Extensibility
• Microsoft Scripting Runtime

step 2 - export用vba srcを記載

以下を記載し、実行すると、VbaSource 以下にexportされます

Sub ExportVbaMacroSrc()

    Dim p_fso As Scripting.FileSystemObject
    Set p_fso = New Scripting.FileSystemObject
    
    Dim p_macroDir As String
    p_macroDir = p_fso.BuildPath(Application.ActiveWorkbook.Path, "VbaSource")
    If Not p_fso.FolderExists(p_macroDir) Then
        p_fso.CreateFolder p_macroDir
    End If

    Dim p_vbComp As VBIDE.VBComponent
    Dim p_typeLabel As String
    Dim p_extension As String
    Dim p_outputFileName As String
    For Each p_vbComp In Application.VBE.ActiveVBProject.VBComponents
        Select Case p_vbComp.Type
            Case vbext_ct_ActiveXDesigner
                p_typeLabel = "ActiveXDesigner"
                p_extension = "cls"
            
            Case vbext_ct_ClassModule
                p_typeLabel = "ClassModule"
                p_extension = "cls"
            
            Case vbext_ct_Document
                p_typeLabel = "Document"
                p_extension = "cls"
            
            Case vbext_ct_MSForm
                p_typeLabel = "MSForm"
                p_extension = "frm"
            
            Case vbext_ct_StdModule
                p_typeLabel = "StdModule"
                p_extension = "bas"
        End Select
    
        p_outputFileName = p_fso.BuildPath(p_macroDir, p_vbComp.Name & "." & p_extension)
        Debug.Print "[export] " & p_outputFileName
        p_vbComp.Export FileName:=p_outputFileName
     
    Next p_vbComp
End Sub