複数のExcelにVBAのソースを一括インポートする

ExcelVBAについて、複数のブックで同じ共通関数のモジュールを使用していたことがありました。

例えばブックを開いたときに共通でやる「画面描画停止」とか「シート名が存在していたら削除」等のよく使う関数を1つのモジュールにまとめて、新しくVBAを作る時にお決まりでインポートをしていました。

しかし、その共通関数にバグが発見されてしまい、30個以上あるVBAツールをすべて直さなければいけなくなりました。

その場合の対処として、「複数のブックの共通関数モジュールを一括インポートで差し替えるVBAを作る」という方法をとりました。

VBAツールが多い現場では重宝すると思ったので、方法をまとめておきたいと思います。

セキュリティ設定を変更する

インポートを行うためには、Excelのセキュリティ設定を変更する必要があります。

デフォルトのセキュリティ設定でインポートやエクスポートをVBAで実行すると、下記のエラーが発生してしまうからです。

実行時エラー ‘1004’:

プログラミングによるVisual Basic プロジェクトへのアクセスは信頼性に欠けます。

要は他のブックのプロジェクトを直接操作するようなマクロはデフォルトでは禁止されているということです。

まずは、VBAを実行する端末のExcelセキュリティを下記方法により無効化します。

1.ファイルタブをクリックし、一番下の方にあるオプションをクリックします。

2.トラストセンター→トラストセンターの設定の順にクリックします。
※トラストセンターは以前のExcelバージョンでは「セキュリティセンター」という名前になっていたと思います。名前が変わっただけなので旧バージョンでは「セキュリティセンター」から設定します。

3.マクロの設定にある「VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックを入れます

これでエラーは出なくなるはずです。

この設定はExcelファイルに保存される設定ではなく、Excelソフトの設定になるので端末単位で設定を行う必要があります。

インポートしたいファイルをExcelからエクスポートする

インポートを行う前に、そもそもどうやってエクスポートをするかについて記載をしておきたいと思います。

VBEでプロジェクトを開き、エクスポートしたいモジュールやクラスなどを右クリックします。

「ファイルのエクスポート」という項目があるのでクリックすると、出力先選択ダイアログが表示されてファイルがエクスポートされます。

エクスポートするファイルが複数ある場合には面倒なのでバッチを作って対応するのが良いと思います。

ファイルを一括でエクスポートする方法については、下記の記事の後半で紹介しています。


一括インポートを行うVBAマクロを作成する

セキュリティ設定とエクスポートが完了したらインポートを実行するVBAマクロを作成していきます。

今回はファイルの操作にFileSystemObjectを使用するので、予め参照設定から「Microsoft Scripting Runtime」にチェックを付けておきます。

作成したソースが以下になります。

Public Sub main()
    'インポートされるブックが存在するフォルダ
    Const TARGET_XLS_FOLDER = "C:\xls"
    
    'インポートするモジュールが存在するフォルダ
    Const TARGET_MODULE_FOLDER = "C:\module"

    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    
    'ブックが存在するフォルダを取得する
    Dim xlsfolder As folder
    Set xlsfolder = fso.GetFolder(TARGET_XLS_FOLDER)
    Dim xlsfile As file
    
    'モジュールが存在するフォルダを取得する
    Dim mdlfolder As folder
    Set mdlfolder = fso.GetFolder(TARGET_MODULE_FOLDER)
    Dim mdlfile As file


    '対象フォルダ内のファイルをループさせる
    For Each xlsfile In xlsfolder.Files
        '拡張子がエクセルのマクロ有効ブックの場合、このブックは対象外とする
        If fso.GetExtensionName(xlsfile.Path) = "xlsm" And xlsfile.Path <> ThisWorkbook.Path Then
            Dim wb As Workbook
            Set wb = Workbooks.Open(xlsfile.Path)
            
            'モジュールファイルをループさせる
            For Each mdlfile In mdlfolder.Files
                If fso.GetExtensionName(mdlfile.Path) = "bas" _
                Or fso.GetExtensionName(mdlfile.Path) = "frm" _
                Or fso.GetExtensionName(mdlfile.Path) = "cls" Then
                    '既存のモジュールがあれば削除
                    Call removeObj(wb, fso.GetBaseName(mdlfile))
                    '新しいモジュールを追加する
                    Call wb.VBProject.VBComponents.Import(mdlfile)
                End If
            Next
            
            'ブックを保存して閉じる
            wb.Save
            wb.Close
        End If
    Next

    'オブジェクトを開放
    Set fso = Nothing

End Sub

Private Sub removeObj(wb As Workbook, filename As String)
    On Error Resume Next
    wb.VBProject.VBComponents.Remove (wb.VBProject.VBComponents(filename))
    Error.Clear
End Sub

ソースの上の方に定義を記載していますが、「C:\xls」フォルダ内にある全てのExcelファイルに対して、「C:\module」内にあるソースファイルを一括インポートしています。

FileSystemObjectを使用して、インポートしたいモジュールのFileオブジェクトを取得して、workbook.VBProject.VBComponents.Importの引数に与えてあげるだけですね。

既存の同名モジュールがあるときにはエラーになってしまうので、予め削除してからインポートを行うようにしています。

無条件で置き換えて保存してしまうので、実用性を考えればワークブックを上書き保存するのではなく「Save As」を使用して別名保存してあげるのがいいかと思います。

もしくは、保存先のフォルダを選ばせて、そちらにインポート済みのExcelファイルを出力するとか。


コメントを残す

メールアドレスが公開されることはありません。

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)