【ExcelVBA】フォルダ内のExcelファイルにあるモジュールを全てエクスポートするには

この記事では、フォルダ内のExcelファイルにあるモジュールを全てエクスポートする方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

【動画】フォルダ内のExcelファイルにあるモジュールを全てエクスポートする実際の動き

本題に入る前に、まずは次の動画をご覧ください。

モジュールをエクスポートしたいExcelファイルを開き、そのExcelファイルの各モジュールをExportメソッドを実行してエクスポートしています。

モジュールをエクスポートする際はモジュール名が必要ですが、そのモジュールの名称はVBProject.VBComponentsオブジェクトのNameプロパティを使って取得しています。

マクロ作成の流れ

STEP.1

VBAのモジュールをエクスポートするExcelファイルを開く

VBAのモジュールをエクスポートするExcelファイルを開きます。

STEP.2

STEP.1で開いたExcelファイルに対してVBComponentsコレクションからVBAオブジェクトモジュールの名称を取得する

STEP.1で開いたExcelファイルに対してVBComponentsコレクションからVBAオブジェクトモジュールの名称を取得します。
名称はVBComponentsコレクションのNameプロパティから取得することができます。

STEP.3

STEP.2で取得したモジュール名を使ってExportメソッドを実行しモジュールをエクスポートする

STEP.2で取得したモジュール名を使ってExportメソッドを実行しモジュールをエクスポートします。

目次へ戻る

Excelファイルの例

今回は次のExcelファイルを用意しました。

A2の黄色のセルにはモジュールを出力したいExcelファイルの置き場を入力し、A5の黄色のセルにはモジュールの保存先を入力します。

実行ボタンをクリックするとマクロが呼び出されます。

目次へ戻る

【注意】VBComponentsコレクションを扱うにはある設定を行わないとエラーになり動作しない

VBComponentsコレクションを扱うにはある設定を行わないとエラーになり動作しません。

設定を行わないままVBComponentsコレクションを呼び出そうとすると下のようにエラーが発生して正常に動作しません。

ではその必要な設定はというと、次の画面(トラストセンター画面)で行う「VBA プロジェクトオブジェクトモデルへのアクセスを信頼する」のチェックを付ける設定です。

このチェックを付けておかないとエラーが先ほどのエラーが発生してしまうので、必ずチェックをつけましょう。

なお、チェックを付けるとセキュリティ面が弱くなってしまうデメリットがあるので、もし本マクロを使わないときはチェックを外しておきましょう。

補足

ただし、本マクロを使い終わった後のチェックの有無設定はExcelを使用している端末の環境の仕様を最優先し、その仕様に合わせて行って下さい。
あくまでExcelの設定はお使いの環境の仕様を絶対に守るようお使いくださいね。

トラストセンター画面の開き方及び「マクロの設定」の表示方法

トラストセンター画面の開き方及び「マクロの設定」の表示方法は次の通りです。

①「ファイル」をクリックする

「ファイル」をクリックします。

②「オプション」をクリックする

「オプション」をクリックします。

③「Excelのオプション」画面でトラストセンターをクリックしトラストセンターボタンをクリックする

「Excelのオプション」画面が表示されたら、「トラストセンター」をクリックします。

するとトラストセンターに関する画面が表示されるので、その中にある「トラストセンターの設定」ボタンをクリックします。

④トラストセンター画面の開き方及び「マクロの設定」が表示される

「トラストセンター」画面が表示され、「マクロの設定」をクリックすると、「マクロの設定」に関する画面が表示されます。

手順は以上になります。

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Private Sub btn_exec_Click()

    Dim cnt             As Integer      'カウンタ用変数
    Dim aryCnt          As Integer      'カウンタ用変数
    Dim excelFilePath   As String       'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先
    Dim expPath         As String       'エクスポートしたファイルの保存先のパス
    Dim buf             As String       'Excelのファイル名を受け取る用の一時格納用変数
    Dim ws              As Worksheet    'Worksheet用変数
    Dim sheetNM()       As String       'シート名用配列
    Dim VBComp          As Object       '標準モジュールやシートなどを扱うためのオブジェクト変数
    Dim expFile         As String       'エクスポートするファイル名
    Dim wsChk           As Boolean      'ワークシート判定フラグ
    
    'カウンタを初期化する
    cnt = 0
    aryCnt = 0
    
    'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先を取得する
    excelFilePath = Worksheets("work").Range("filePath").Value
    
    'モジュールの保存先を取得する
    expPath = Worksheets("work").Range("expPath").Value
    
    'VBAオブジェクトモジュールの名称を取得したいExcelファイルを取得する
    buf = Dir(excelFilePath & "*.xlsm")
    
    'Excelファイルの数だけ処理を繰り返すループ
    Do While buf <> ""
    
        'Excelファイルを読み取り専用で開く
        Workbooks.Open excelFilePath & buf, ReadOnly:=True

        'フォルダ名とファイル名貼り付け用シート有無のチェック
        For Each ws In Worksheets
            
            '動的配列の再宣言を行う(配列に格納されている値を残したまま)
            ReDim Preserve sheetNM(1, cnt)
            
            'シート名を配列に格納する
            sheetNM(0, cnt) = ws.Name
            
            'モジュールのコード名を配列に格納する
            sheetNM(1, cnt) = ws.CodeName
            
            cnt = cnt + 1
        
        Next ws
        
        'VBAオブジェクトモジュールの数だけ処理を繰り返すループ
        For Each VBComp In ActiveWorkbook.VBProject.VBComponents

            If Right(expPath, 1) <> "\" Then
        
                '入力されたパスの末尾に「\」が付いていない場合に付ける
                expPath = expPath & "\"
        
            End If
        
            'エクスポートするファイル名を取得する
            expFile = expPath & Replace(buf, ".xlsm", "") & "_" & VBComp.Name
        
            Select Case VBComp.Type

                Case 1

                    '標準モジュールの場合
                    
                    'モジュールをエクスポートする
                    ActiveWorkbook.VBProject _
                        .VBComponents(VBComp.Name).Export expFile & ".bas"

                Case 2

                    'クラスモジュールの場合

                    'モジュールをエクスポートする
                    ActiveWorkbook.VBProject _
                        .VBComponents(VBComp.Name).Export expFile & ".cls"

                Case 3

                    'ユーザーフォームの場合
                    
                    'モジュールをエクスポートする
                    ActiveWorkbook.VBProject _
                        .VBComponents(VBComp.Name).Export expFile & ".frm"

                Case 100

                    'ワークブックやシートの場合
                    
                    For aryCnt = 0 To UBound(sheetNM, 2)
                    
                        If sheetNM(1, aryCnt) = VBComp.Name Then
                        
                            '配列にシートモジュール名が格納されている場合
                            
                            'モジュールをエクスポートする(シートモジュールであることを知らせるためファイル名に「_sht」を付ける)
                            ActiveWorkbook.VBProject _
                                .VBComponents(VBComp.Name).Export expFile & "_sht.cls"
                                
                            'ワークシートの場合に
                            wsChk = True
                            
                            Exit For
                        
                        End If
                    
                    Next aryCnt
                    
                    If wsChk = False Then
                    
                        'wsChkの値がFalseの場合
                        '→VBComp.Nameの値がワークブックモジュールに該当
                    
                        'モジュールをエクスポートする(ブックモジュールであることを知らせるためファイル名に「_sht」を付ける)
                        ActiveWorkbook.VBProject _
                            .VBComponents(VBComp.Name).Export expFile & "_twb.cls"
                    
                    End If
                    
                    'フラグを初期化する
                    wsChk = False
                                    
                Case Else

            End Select
            
            cnt = cnt + 1
            
        Next
    
        '開いたExcelファイルを閉じる
        Workbooks(buf).Close SaveChanges:=False
    
        'Excelファイル名を取得する
        buf = Dir()
        
    Loop
    
End Sub

注目すべきコード①

最初に見て頂きたいのは21行目から24行目です。

    'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先を取得する
    excelFilePath = Worksheets("work").Range("filePath").Value
    
    'モジュールの保存先を取得する
    expPath = Worksheets("work").Range("expPath").Value

以上のコードは、VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先とモジュールの保存先を取得するコードです。

注目すべきコード②

次に見て頂きたいのは27行目です。

    'VBAオブジェクトモジュールの名称を取得したいExcelファイルを取得する
    buf = Dir(excelFilePath & "*.xlsm")

以上のコードは、フォルダ内の拡張子が「xlsm」のExcelファイル名を取得するコードです。

今回はフォルダ内の拡張子が「xlsm」のExcelファイルからモジュールをエクスポートするので、Dir関数にエクスポートしたいExcelファイルの置き場と「xlsm」の拡張子を指定しています。

注目すべきコード③

次に見て頂きたいのは30行目です。

    'Excelファイルの数だけ処理を繰り返すループ
    Do While buf <> ""

以上のコードは、「注目すべきコード②」で説明したExcelファイルの置き場にあるExcelファイルのモジュールを全てエクスポートし終わるまで繰り返すループです。

なお、1ファイルの全モジュールをエクスポートし終わったら、下のコードを実行して次のExcelファイル内のモジュールをエクスポートします。

        'Excelファイル名を取得する
        buf = Dir()

注目すべきコード④

次に見て頂きたいのは33行目です。

        'Excelファイルを読み取り専用で開く
        Workbooks.Open excelFilePath & buf, ReadOnly:=True

以上のコードは、モジュールをエクスポートするExcelファイルを開く処理のコードです。

OpenメソッドにモジュールをエクスポートするExcelファイルを指定して実行することでExcelファイルを開くことができます。

Excelファイルを開くことでモジュールをエクスポートすることができます。

注目すべきコード⑤

次に見て頂きたいのは36行目から49行目です。

        'フォルダ名とファイル名貼り付け用シート有無のチェック
        For Each ws In Worksheets
            
            '動的配列の再宣言を行う(配列に格納されている値を残したまま)
            ReDim Preserve sheetNM(1, cnt)
            
            'シート名を配列に格納する
            sheetNM(0, cnt) = ws.Name
            
            'モジュールのコード名を配列に格納する
            sheetNM(1, cnt) = ws.CodeName
            
            cnt = cnt + 1
        
        Next ws

以上のコードは、Excelファイルから全てのシート名とそのシートのモジュール名を取得するコードです。

なぜシート名とシートのモジュール名を取得するのかというと、シートモジュールなのかブックモジュールなのかを判別するためです。

というのも、モジュール名を取得する際シートモジュールなのかブックモジュールなのかを判別する情報がありません。

そこでどうやって判別するのかというと、シートモジュールにはシート名がありますがブックモジュールにはシート名がありません。

シートモジュール：シート名がある
ブックモジュール：シート名がない

なので、シート名の有無でシートモジュールなのかブックモジュールなのかが判別することができます。

そのために以上のコードでシート名を取得しています。

今回は取得したシート名を42行目で、モジュール名を45行目で配列に格納しています。

39行目は以上のシート名とモジュール名をどんどん配列sheetNMに格納するために配列の再宣言を行っています。

シート名とモジュール名を取得したら配列sheetNMの要素数を1つ拡張してその拡張した要素位置に格納します。

この配列sheetNMに取得したシート名とモジュール名を、Excelのファイル内に存在するシートの数だけ繰り返します。

この繰り返しの処理を36行目のループ内で行います。

注目すべきコード⑥

次に見て頂きたいのは52行目です。

        'VBAオブジェクトモジュールの数だけ処理を繰り返すループ
        For Each VBComp In ActiveWorkbook.VBProject.VBComponents

以上のコードは、開いたExcelファイルにあるモジュールの名称をVBComponentsコレクションから取得するためのループ処理のコードです。

VBComponentsコレクションから取得したオブジェクトを変数VBCompが受け取ります。

ActiveWorkbookは開いたExcelなので、そのExcelファイルからモジュールの名称をVBComponentsコレクションから取得することができるようになります。

なおこのループは開いたExcelファイルにあるモジュールの数だけループします。

注目すべきコード⑦

次に見て頂きたいのは62行目です。

            'エクスポートするファイル名を取得する
            expFile = expPath & Replace(buf, ".xlsm", "") & "_" & VBComp.Name

以上のコードは、エクスポートするファイル名を取得するコードです。

ただし、ここではクラスモジュールなのかフォームモジュールなのか、またシートモジュールなのかまだ特定できていないので、以上のコードが実行されたタイミングではまだ拡張子がない状態です。

拡張子を付与する前のエクスポートファイル名

C:\work\10_勉強\10_VBA関連\0202\file\output\file1_ThisWorkbook
「file1_ThisWorkbook」に「.cls」「.frm」「.bas」のいずれかを後続のコードで行います。
なおファイル名の構成は、「エクスポート元のExcelのファイル名(拡張子抜き)」+「_」+「モジュール名」としています。

注目すべきコード⑧

次に見て頂きたいのは64行目です。

            Select Case VBComp.Type

以上のコードは、VBComponentsコレクションのTypeプロパティの値を判定するコードです。

VBComponentsコレクションのTypeプロパティの値によって「.cls」「.frm」「.bas」のいずれかを付与してファイルをエクスポートします。

1：標準モジュール(.bas)
2：クラスモジュール(.cls)
3：ユーザーフォーム(.frm)
100：Excelオブジェクト(ワークブック・シート)(.cls)

1：標準モジュール(.bas)

標準モジュールの場合は66行目の条件に合致し、71行目と72行目のコードが実行されます。

                Case 1
                    '標準モジュールの場合
                    
                    'モジュールをエクスポートする
                    ActiveWorkbook.VBProject _
                        .VBComponents(VBComp.Name).Export expFile & ".bas"

標準モジュールの場合は拡張子が「bas」なので「.bas」の文字列をエクスポートするファイルに結合させてExportを実行してエクスポートします。

2：クラスモジュール(.cls)

クラスモジュールの場合は74行目の条件に合致し、79行目と80行目のコードが実行されます。

                Case 2
                    'クラスモジュールの場合
                    'モジュールをエクスポートする
                    ActiveWorkbook.VBProject _
                        .VBComponents(VBComp.Name).Export expFile & ".cls"

クラスモジュールの場合は拡張子が「cls」なので「.cls」の文字列をエクスポートするファイルに結合させてExportを実行してエクスポートします。

3：ユーザーフォーム(.frm)

フォームモジュールの場合は82行目の条件に合致し、87行目と88行目のコードが実行されます。

                Case 3
                    'ユーザーフォームの場合
                    
                    'モジュールをエクスポートする
                    ActiveWorkbook.VBProject _
                        .VBComponents(VBComp.Name).Export expFile & ".frm"

フォームモジュールの場合は拡張子が「frm」なので「.frm」の文字列をエクスポートするファイルに結合させてExportを実行してエクスポートします。

100：Excelオブジェクト(ワークブック・シート)(.cls)

ブックモジュールまたはシートモジュールの場合は90行目の条件に合致し、94行目と125行目のコードが実行されます。

                Case 100

                    'ワークブックやシートの場合
                    
                    For aryCnt = 0 To UBound(sheetNM, 2)
                    
                        If sheetNM(1, aryCnt) = VBComp.Name Then
                        
                            '配列にシートモジュール名が格納されている場合
                            
                            'モジュールをエクスポートする(シートモジュールであることを知らせるためファイル名に「_sht」を付ける)
                            ActiveWorkbook.VBProject _
                                .VBComponents(VBComp.Name).Export expFile & "_sht.cls"
                                
                            'ワークシートの場合に
                            wsChk = True
                            
                            Exit For
                        
                        End If
                    
                    Next aryCnt
                    
                    If wsChk = False Then
                    
                        'wsChkの値がFalseの場合
                        '→VBComp.Nameの値がワークブックモジュールに該当
                    
                        'モジュールをエクスポートする(ブックモジュールであることを知らせるためファイル名に「_sht」を付ける)
                        ActiveWorkbook.VBProject _
                            .VBComponents(VBComp.Name).Export expFile & "_twb.cls"
                    
                    End If
                    
                    'フラグを初期化する
                    wsChk = False

ブックモジュールとシートモジュールどちらもVBComponentsコレクションのTypeプロパティの値が100なのですが、「注目すべきコード⑤」でも説明した通りブックモジュールとシートモジュールの判定する情報がありません。

どちらのモジュールもTypeプロパティの値が100なので、もしブックモジュールの名称をデフォルトの「ThisWorkBook」から適当な名称に変更されてしまった場合に、エクスポートファイルを見てもそれがブックモジュールのファイルなのかシートモジュールのファイルなのかが分かりません。

ブックモジュールの名称が変更されてもエクスポートファイルがブックモジュールのものなのかを特定するためにはどうすればいいのかというと、シート名とそのシートのモジュール名を使って判定します。

シートモジュール：シート名がある
ブックモジュール：シート名がない

シートモジュールはシート名があるので95行目の条件に合致し、100行目と101行目でエクスポートするファイルに「_sht.cls」を結合させてExportを実行してエクスポートします。(「sht」はsheetを略した文字列にしました)

ブックモジュールはシート名がないので113行目の条件に合致し、119行目と120行目でエクスポートするファイルに「_twb.cls」を結合させてExportを実行してエクスポートします。(「twb」はThisWorkBookを略した文字列にしました)

注目すべきコード⑨

次に見て頂きたいのは136行目です。

        '開いたExcelファイルを閉じる
        Workbooks(buf).Close SaveChanges:=False

上記のコードは、モジュールすべてをエクスポートし終わったらExcelファイルを閉じる処理のコードです。

もう開いておく必要が無いのでCloseメソッドを実行してExcelファイルを閉じます。

目次へ戻る

動作確認

マクロ実行前

今回は以下のファイルを使います。

A2のセルにはモジュールを出力したいExcelファイルの置き場を入力し、A4のセルにはモジュールの保存先を入力します。

今回モジュールをエクスポートさせるExcelファイルは以下の3ファイルです。

3ファイルにあるすべてのモジュールをエクスポートします。

マクロ実行後

マクロを実行すると、以下のフォルダにモジュールがエクスポートされます。

モジュールのコードとエクスポートしたファイルの中身の確認

①標準モジュール

cls_getRowPos

file1_mdl_funcExp.bas

ソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。

2行目から最後まではソースコードに相違なく書き込まれていることが確認できます。

②クラスモジュール

cls_getRowPos

file1_cls_getrowPos.cls

ソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。

10行目から最後まではソースコードに相違なく書き込まれていることが確認できます。

③フォームモジュール

cls_getRowPos

file1_frm_getFileInfo.frm

ソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。

16行目から最後まではソースコードに相違なく書き込まれていることが確認できます。

なお、フォームモジュールは「.frx」の拡張子が付いたファイルもエクスポート時に作成されます。

このファイルはフォームモジュールのエクスポートファイルを使ってインポートする際必要になります。

もし「.frx」の拡張子が付いたファイルが無いとエラーになりインポートができません。

④シートモジュール

Sheet1

file1_Sheet1_sht.cls

ソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。

10行目から最後まではソースコードに相違なく書き込まれていることが確認できます。

また、シートモジュールはエクスポートするのに拡張子が「.cls」で生成されるので、シートモジュールのものと分かるようにエクスポートファイルに「_sht」の文字列が付けられています。

手動でエクスポート時「クラスモジュール(.cls)」しか選べない

⑤ブックモジュール

TWB

ソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。

10行目から最後まではソースコードに相違なく書き込まれていることが確認できます。

また、ブックモジュールはエクスポートするのに拡張子が「.cls」で生成されるので、ブックモジュールのものと分かるようにエクスポートファイルに「_sht」の文字列が付けられています。

手動でエクスポート時「クラスモジュール(.cls)」しか選べない

目次へ戻る

最後に

本記事では、フォルダ内のExcelファイルにあるモジュールを全てエクスポートする方法についてご説明しました。

モジュールの中身を確認したい場合にExcelファイルを開かないと確認できないですが、今回のマクロを使ってモジュールをエクスポートしておけばExcelファイルを開かずにモジュールのソースの中身を確認することができます。

ソースの中のコードをGrep検索したい時には便利です。

Excelファイルを開いてソースを開く手間を減らしたい、grepで一括検索したい、などのいくつかの場面で役に立つかもしれないので本記事を参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→　受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら

目次へ戻る