この記事では、フォルダにあるExcelファイルのVBAのソースコードを全てExcelのシートに出力する方法についてご説明します。
【動画】フォルダにあるExcelファイルのVBAのソースコードを全てExcelのシートに出力する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
フォルダ内のExcelファイルを一つ一つ開き、VBProjectのVBComponentsコレクション内からLinesプロパティでソースコードを1行ずつ取得します。
取得したソースコードはモジュールごとにシートを新規作成してそのシートに出力します。
そのシートがすぐに見れるよう、「top」のシートの表に出力された「シート名」にハイパーリンクを設置しています。
マクロ作成の流れ
Excelファイルの例
今回は次のExcelファイルを用意しました。
実行するマクロのExcelファイルには、「項番」「ファイル名」「モジュール名」「シート名」の4つの項目を表示する表を用意してあります。
これは、シートに出力するソースコードを出力する、というテーマから外れた内容ですが、ソースコードをどのExcelファイルから?そのモジュールから?取得したのかを示すものと、どのシートにソースコードが出力されたのかを示す情報になります。
ソースコードが出力されたシートのサンプルは次の通りです。
上の画像のシートに出力されているソースコードの取得元のソースコードは下の通りです。
ただソースコードをシートに出力した結果だけで終わるのではなく、以上の情報があると便利だと思い今回は用意しました。
マクロが正常に実行されると、以下の通りに情報が表に出力されます。
1つ例を説明すると、5行目の「項番」(A列)にはデータが1つ目であることを指す値「1」を、「ファイル名」(B列)にはソースコードを取得したファイル名「0226_1.xlsm」を、「モジュール名」(C列)にはソースコードを取得したモジュール名「ThisWorkbook」を、「シート名」(D列)には取得したソースコードを出力したシート名「0226_1_ThisWorkbook」を表示しています。
- 「項番」(A列)にはデータが1つ目であることを指す値「1」
- 「ファイル名」(B列)にはソースコードを取得したファイル名「0226_1.xlsm」
- 「モジュール名」(C列)にはソースコードを取得したモジュール名「ThisWorkbook」
- 「シート名」(D列)には取得したソースコードを出力したシート名「0226_1_ThisWorkbook」
ちなみに、「シート名」(D列)の値にはハイパーリンクを設置し、クリックするとそのシートに移動できるようにしてあります。(「注目すべきコード⑦」の中で説明しています)
【注意】VBComponentsコレクションを扱うにはある設定を行わないとエラーになり動作しない
VBComponentsコレクションを扱うにはある設定を行わないとエラーになり動作しません。
設定を行わないままVBComponentsコレクションを呼び出そうとすると下のようにエラーが発生して正常に動作しません。
ではその必要な設定はというと、次の画面(トラストセンター画面)で行う「VBA プロジェクト オブジェクト モデルへのアクセスを信頼する」のチェックを付ける設定です。
このチェックを付けておかないとエラーが先ほどのエラーが発生してしまうので、必ずチェックをつけましょう。
なお、チェックを付けるとセキュリティ面が弱くなってしまうデメリットがあるので、もし本マクロを使わないときはチェックを外しておきましょう。
ただし、本マクロを使い終わった後のチェックの有無設定はExcelを使用している端末の環境の仕様を最優先し、その仕様に合わせて行って下さい。
あくまでExcelの設定はお使いの環境の仕様を絶対に守るようお使いくださいね。
トラストセンター画面の開き方及び「マクロの設定」の表示方法
トラストセンター画面の開き方及び「マクロの設定」の表示方法は次の通りです。
①「ファイル」をクリックする
「ファイル」をクリックします。
②「オプション」をクリックする
「オプション」をクリックします。
③「Excelのオプション」画面でトラストセンターをクリックしトラストセンターボタンをクリックする
「Excelのオプション」画面が表示されたら、「トラストセンター」をクリックします。
するとトラストセンターに関する画面が表示されるので、その中にある「トラストセンターの設定」ボタンをクリックします。
④トラストセンター画面の開き方及び「マクロの設定」が表示される
「トラストセンター」画面が表示され、「マクロの設定」をクリックすると、「マクロの設定」に関する画面が表示されます。
手順は以上になります。
コードの例
Option Explicit Private Sub btn_exec_Click() Dim fso As FileSystemObject 'FileSystemObjectのインスタンス用変数 Dim thisWb As Workbook '本マクロ用のワークブック用変数 Dim topSheet As Worksheet 'topのシート Dim filePath As String 'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先 Dim buf As String 'Excelのファイル名を受け取る用の一時格納用変数 Dim ws As Worksheet 'ワークシート用変数 Dim addSheetNM As String '追加するシート名 Dim wb As Workbook 'ワークブック用変数 Dim vbComp As Object 'VBProjectのVBComponentsコレクション用変数 Dim aryCnt As Long '配列用カウンタ Dim mdlNM As String 'モジュール名 Dim allMdlCnt As Long 'モジュール数を数えるカウンタ Dim obj As Object 'CodeModuleオブジェクトを格納するオブジェクト変数 Dim rowCnt As Long '行数を数えるカウンタ 'FileSystemObjectのインスタンスを生成する Set fso = New FileSystemObject 'データを出力する行位置 Const bgnRowPos As Long = 5 '本マクロのブックを取得する Set thisWb = ActiveWorkbook '本マクロのブックのシート名を取得する Set topSheet = thisWb.Worksheets("top") 'モジュールに関する各情報を出力するセルをクリアする topSheet.Range("A" & bgnRowPos & ":D1000").ClearContents If Right(topSheet.Range("filePath").Value, 1) <> "\" Then '入力された「Excelファイルの置き場」のフルパスの末尾に「\」が付いていない場合 '「Excelファイルの置き場」のフルパスの末尾に「\」を付けて、フルパスを変数filePathに格納する filePath = topSheet.Range("filePath").Value & "\" Else '入力された「Excelファイルの置き場」のフルパスの末尾に「\」が付いている場合 '「Excelファイルの置き場」のフルパスを変数filePathに格納する filePath = topSheet.Range("filePath").Value End If 'ワークブックのすべてのシートをループ For Each ws In thisWb.Worksheets 'シートの名前が「top」と異なる場合 If ws.Name <> "top" Then 'メッセージを表示させないよう設定 Application.DisplayAlerts = False 'シートを削除する ws.Delete 'メッセージを表示させない設定を解除 Application.DisplayAlerts = True End If Next ws 'VBAオブジェクトモジュールの名称を取得したいExcelファイルを取得する buf = Dir(filePath & "*.xlsm") 'Excelファイルの数だけ処理を繰り返すループ Do While buf <> "" '拡張子なしのファイル名を取得する addSheetNM = fso.GetBaseName(buf) 'Excelファイルを開き、そのExcelファイルの情報をwbに代入する Set wb = Workbooks.Open(filePath & buf) ' 配列を初期化 ReDim myArray(0) 'VBProjectのVBComponentsコレクション内の各VBComponentに対して以下を繰り返すFor文 For Each vbComp In wb.VBProject.VBComponents '動的配列の再宣言を行う(配列に格納されている値を残したまま) ReDim Preserve myArray(aryCnt) '取得したモジュール名を配列に格納する myArray(aryCnt) = vbComp.Name 'カウンタを1増やす aryCnt = aryCnt + 1 Next vbComp For aryCnt = 0 To UBound(myArray) 'モジュール名を配列myArrayから取得する mdlNM = myArray(aryCnt) 'A列のセルに項番を出力する topSheet.Range("A" & bgnRowPos + allMdlCnt).Value = allMdlCnt + 1 'B列のセルにファイル名を出力する topSheet.Range("B" & bgnRowPos + allMdlCnt).Value = buf 'C列のセルにモジュール名を出力する topSheet.Range("C" & bgnRowPos + allMdlCnt).Value = mdlNM 'D列のセルにシート名を出力する topSheet.Range("D" & bgnRowPos + allMdlCnt).Value = addSheetNM & "_" & myArray(aryCnt) 'マクロ側のブックをActiveにする thisWb.Activate '新しいシートを作成する thisWb.sheets.Add(After:=sheets(sheets.Count)).Name = addSheetNM & "_" & myArray(aryCnt) 'D列に追加したシート名のセルから、新たに追加したシートに遷移できるリンクを設置する topSheet.Hyperlinks.Add _ Anchor:=topSheet.Range("D" & bgnRowPos + allMdlCnt), _ Address:="", _ SubAddress:=addSheetNM & "_" & myArray(aryCnt) & "!A1", _ TextToDisplay:=addSheetNM & "_" & myArray(aryCnt) '新たに追加したシートのA1のセルに、「top」のシートに遷移できるリンクを設置する ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="top!A1", TextToDisplay:="→topのシートに戻る" 'VBComponentsプロパティから取得したVBComponentオブジェクトのCodeModuleオブジェクトを取得する Set obj = wb.VBProject.VBComponents(myArray(aryCnt)).CodeModule With obj '対象モジュールの行数分ループするfor文 For rowCnt = 1 To .CountOfLines 'セルにソースコードを出力する Worksheets(addSheetNM & "_" & myArray(aryCnt)).Range("A" & rowCnt + 1).Value = .Lines(rowCnt, 1) DoEvents Next rowCnt End With allMdlCnt = allMdlCnt + 1 Next Set obj = Nothing '開いたExcelファイルを閉じる Workbooks(buf).Close SaveChanges:=False '次のファイルを取得する buf = Dir() '配列を初期化する Erase myArray 'カウンタを初期化する aryCnt = 0 Loop topSheet.Select '後処理 Set obj = Nothing Set wb = Nothing End Sub
注目すべきコード①
最初に見て頂きたいのは71行目から74行目です。
'VBAオブジェクトモジュールの名称を取得したいExcelファイルを取得する buf = Dir(filePath & "*.xlsm") 'Excelファイルの数だけ処理を繰り返すループ Do While buf <> ""
コードの説明
以上のコードは、今回のサンプルのA2の黄色いセルに入力されたExcelファイルの置き場にあるExcelファイルを取得し、Excelファイルの数だけ処理を繰り返すコードです。
Dir関数の引数にExcelファイルの置き場をフルパスで指定して実行することでExcelファイルを取得することができます。
取得したら変数bufに格納されます。
ちなみに、Excelファイルの置き場の全てを取得するには、159行目のDir関数を繰り返し実行することで取得することができます。
'次のファイルを取得する buf = Dir()
全てのExcelファイルを取得し終わったら、次のDir関数の戻り値がブランクになるのでDo文のループが終了します。
全てExcelファイル名を取得した後にDir関数を実行した直後の状態変数bufがブランクなのでDoループから抜ける
注目すべきコード②
次に見て頂きたいのは77行目です。
'拡張子なしのファイル名を取得する addSheetNM = fso.GetBaseName(buf)
コードの説明
以上のコードは、Excelのファイル名から拡張子と「.」を除いた文字列を取得する処理のコードです。
なぜこの処理を行っているのかというと、シート名の文字数を減らしたいからです。
この文字列は、新規でシートを生成する際のシート名に使います。
例えばファイル名が「0226_1.xlsm」なら「0226_1」を取得します。
下の画像のようにシート名にこの文字列「0226_1」が使われます。
注目すべきコード③
次に見て頂きたいのは80行目です。
'Excelファイルを開き、そのExcelファイルの情報をwbに代入する Set wb = Workbooks.Open(filePath & buf)
コードの説明
以上のコードは、Openメソッドの引数にExcelファイルをフルパスで指定して実行し、そのExcelファイルの情報をwbに代入している処理のコードです。
Openメソッドを実行することでExcelファイルが開かれ、マクロがそのExcelファイルのマクロのコードを参照することができます。
つまり、Excelファイルにどんなモジュールがあるのかや、そのモジュール内にどんなプロシージャがあるのかを特定することができるようになります。
注目すべきコード④
次に見て頂きたいのは83行目から97行目です。
' 配列を初期化 ReDim myArray(0) 'VBProjectのVBComponentsコレクション内の各VBComponentに対して以下を繰り返すFor文 For Each vbComp In wb.VBProject.VBComponents '動的配列の再宣言を行う(配列に格納されている値を残したまま) ReDim Preserve myArray(aryCnt) '取得したモジュール名を配列に格納する myArray(aryCnt) = vbComp.Name 'カウンタを1増やす aryCnt = aryCnt + 1 Next vbComp
コードの説明
以上のコードは、モジュール名を取得してそのモジュール名を配列に格納する処理のコードです。
コードの詳細
83行目のコードでは、配列myArrayを要素数0で初期化しています。
86行目では、VBProjectのVBComponentsコレクション内の各VBComponentに対して処理を繰り返すFor文を用意し、vbcompにはモジュールが格納されます。
89行目では動的配列の再宣言を行い、92行目ではnameプロパティからモジュールの名称を取得して配列myArrayに格納します。
For文内の処理が全て完了すると、Excelのモジュール名が全て取得されます。
注目すべきコード⑤
次に見て頂きたいのは83行目から97行目です。
'A列のセルに項番を出力する topSheet.Range("A" & bgnRowPos + allMdlCnt).Value = allMdlCnt + 1 'B列のセルにファイル名を出力する topSheet.Range("B" & bgnRowPos + allMdlCnt).Value = buf 'C列のセルにモジュール名を出力する topSheet.Range("C" & bgnRowPos + allMdlCnt).Value = mdlNM 'D列のセルにシート名を出力する topSheet.Range("D" & bgnRowPos + allMdlCnt).Value = addSheetNM & "_" & myArray(aryCnt)
コードの説明
以上のコードは、「項番」(A列)、「ファイル名」(B列)、「モジュール名」(C列)、「シート名」(D列)をセルに出力する処理のコードです。
コードの詳細
105行目のコードでは、項番をA列に出力します。
108行目のコードでは、ソースコードを取得するファイル名をB列に出力します。
111行目のコードでは、ソースコードを取得するモジュール名をC列に出力します。
114行目のコードでは、取得したソースコードを出力したシート名を出力します。
注目すべきコード⑥
次に見て頂きたいのは120行目です。
'新しいシートを作成する thisWb.sheets.Add(After:=sheets(sheets.Count)).Name = addSheetNM & "_" & myArray(aryCnt)
コードの説明
以上のコードは、ソースコードを出力するシートを新たに生成する処理のコードです。
シートを新たに生成するのに、そのシート名は「ファイル名(拡張子と「.」を除く)+モジュール名」
例えば「0226_1.xlsm」というファイルのモジュール「ThisWorkbook」からソースコードを取得した場合に、「0226_1_ThisWorkbook」という名前でシート名を生成します。
注目すべきコード⑦
次に見て頂きたいのは123行目から127行目です。
'D列に追加したシート名のセルから、新たに追加したシートに遷移できるリンクを設置する topSheet.Hyperlinks.Add _ Anchor:=topSheet.Range("D" & bgnRowPos + allMdlCnt), _ Address:="", _ SubAddress:=addSheetNM & "_" & myArray(aryCnt) & "!A1", _ TextToDisplay:=addSheetNM & "_" & myArray(aryCnt)
コードの説明
以上のコードは、「シート名」(D列)の値にハイパーリンクを設置し、クリックするとそのシートに移動できるようにする処理のコードです。
リンク先で表示された時のセルの選択位置はA1としてあります。
注目すべきコード⑧
次に見て頂きたいのは130行目です。
'新たに追加したシートのA1のセルに、「top」のシートに遷移できるリンクを設置する ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="top!A1", TextToDisplay:="→topのシートに戻る"
以上のコードは、新たに追加したシートのA1のセルに、「top」のシートに遷移できるリンクを設置している処理のコードです。
新たに生成されたシートのA1のセルに「→topのシートに戻る」という文字列を出力し、そのセルには「top」のシートに移動するハイパーリンクを設置しています。
注目すべきコード⑨
次に見て頂きたいのは132行目から147行目です。
'VBComponentsプロパティから取得したVBComponentオブジェクトのCodeModuleオブジェクトを取得する Set obj = wb.VBProject.VBComponents(myArray(aryCnt)).CodeModule With obj '対象モジュールの行数分ループするfor文 For rowCnt = 1 To .CountOfLines 'セルにソースコードを出力する Worksheets(addSheetNM & "_" & myArray(aryCnt)).Range("A" & rowCnt + 1).Value = .Lines(rowCnt, 1) DoEvents Next rowCnt End With
コードの説明
以上のコードは、Linesプロパティからソースコードを取得してセルに出力する処理のコードです。
Linesプロパティからソースコードを取得するには、まずはVBComponentsプロパティから取得したVBComponentオブジェクトのCodeModuleオブジェクトを取得する必要があります。
コードの詳細
133行目のコードでは、VBComponentsプロパティから取得したVBComponentオブジェクトのCodeModuleオブジェクトを取得します。
138行目で、モジュール内のソースコードの行数分ループを行うFor文を用意し、141行目でソースコードをシートに出力しています。
注目すべきコード⑩
次に見て頂きたいのは156行目です。
'開いたExcelファイルを閉じる Workbooks(buf).Close SaveChanges:=False
コードの説明
以上のコードは、開いたExcelファイルを閉じる処理のコードです。
Excelのシートにあるすべてのモジュールからソースコードがシートに出力し終わたら開いたExcelファイルを閉じます。
動作確認
マクロ実行前
今回は以下のExcelファイルを用意しました。
また、ソースコードの取得先のExcelファイルを下の通りに用意しました。
マクロ実行後
マクロを実行すると「top」のシートに、出力したソースコードに関する情報(取得したソースコードがどのExcelファイルからなのか、どのモジュールからなのかなど)をが出力されました。
また、新たにシートが追加されてソースコードが出力されました。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどの「fso.CreateFolderメソッドを使って2階層以上のフォルダを作成」する方法の説明で紹介したコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Windows Script Host Object Model(wshom.ocx)
なぜ必要かというと、Excelのマクロのコードの11行目の「FileSystemObject」というオブジェクトが「wshom.ocx」というファイルを参照するからです。
Dim fso As FileSystemObject 'FileSystemObjectのインスタンス用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「wshom.ocx」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「wshom.ocx」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、フォルダにあるExcelファイルのVBAのソースコードを全てExcelのシートに出力する方法についてご説明しました。
VBAのソースコードを見るには、いちいちExcelファイルを開いてVBE画面を表示させてないといけません。
VBAのソースコードを1つのExcelファイルの中にすべて書き出されるので、ソース内の文字列や処理を確認しやすくなると思います。
いちいちExcelファイルを開くその手間が面倒だと感じている場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。