みなさん、こんにちは。
ITソリューション事業部の林です。

本記事では、ExcelVBAのテクニックを紹介・解説いたします。

今回紹介するマクロの機能は、「Excelシートの情報をリスト化」です。
前回の記事からの続きとなりますので、まだ読んでいない方は以下のリンクから記事の確認お願いします。
前回記事：フォルダ内のファイル情報を取得

【目次】

リスト作成の処理順序
リスト初期化のソースコード
リストへ追記するソースコード
リスト作成のソースコード
マクロの動作確認
終わりに

それでは、始めましょう！

リスト作成の処理順序

前回は「フォルダ内のファイル情報を取得」するところまで解説しました。
やり方としては、FileSystemObjectで指定フォルダのExcelファイルパスを取得して、
それをWorkbooks.Openで開き、Cells.Valueプロパティから値を取得するという順で処理しました。

今回はそこからの続きです。
ExcelシートからCells.Valueプロパティで取得した値を、あらかじめ用意しておいた表に出力します。
出力の際は、A列へ出力順に通番を設定していきます。

マクロでリストを作る際の注意点としては、出力前にリストを初期化しておくことです。
これをやらないと、前回の出力結果に今回の出力結果を上書きしてしまい、データが混在してしまいます。
例えば、前回の出力結果が5件だったとして、それが残ったままで3件出力すると、2件は消えずに残ったままです。

これらを踏まえて、今回作成するマクロの処理順序は以下になります。
①フォルダ選択ダイアログを表示する（前回作成）
②リストの初期化（今回作成）
③FileSystemObjectを定義する（前回作成）
④指定されたExcelファイルを開く（前回作成）
⑤開いたファイルからデータを取得しリストへ出力する（今回作成）
⑥開いたファイルを閉じる（前回作成）
⑦全てのファイルを読み終わるまで上記④～⑥を繰り返す（前回作成）

上記②と⑤が新規に作成するソースコードです。
ひとつひとつ作成していきましょう。

リスト初期化のソースコード

今回のように、Excelシートの値を初期化するマクロを作る場合、「どのシートを初期化しているか」を明確にしましょう。
これはかなり重要になってきます。
なぜかというと、操作対象を明確にしていないと、意図しないExcelシートの値を初期化してしまいかねないからです。

操作対象を明確にするには、対象のシートをオブジェクト変数として定義します。
ワークブックやワークシートをオブジェクト変数定義するには、「Set」ステートメントを使用します。
今回はワークシートの初期化なので、ワークシートをオブジェクトとして変数定義します。

ワークブックを変数定義⇒　Set オブジェクト変数 = 操作するワークブック
ワークシートを変数定義⇒　Set オブジェクト変数 = 操作するワークブック.操作するシート

次に、ClearContentsメソッドでリストを初期化します。
リストの1行目は項目名として残しておくので、2行目以降が初期化対象となります。
行を指定するにはRowsオブジェクトで範囲指定します。

Rows(行指定).ClearContents

2行目から最終行まで指定するには、Rows("2:" & Rows.Count).ClearContentsのように、括弧内に文字列で指定しましょう。

以上を踏まえ、リストを初期化するソースコードは以下になります。

Sub ExcelVBA_012_001()
    'リストの初期化
    Dim objList As Object 'オブジェクト変数を用意
    Set objList = ThisWorkbook.Worksheets("リスト") '「リスト」シートをオブジェクト変数定義
    With objList
        .Rows("2:" & .Rows.Count).ClearContents '2行目から最終行まで値をクリア
    End With
End Sub

リストへ追記するソースコード

リストに書き込む時は、行番号を意識しましょう。
今回のマクロのようにリストへ追記していく場合、すでに書き込まれている行の下に出力していくことになります。

行追記にはいくつかやり方がありますが、シンプルなのは「すでに書き込まれている行の下の行に書き込む」というものです。
すでに書き込まれている行の求め方は、過去記事の「終端行・終端列の求め方」にて解説していますので、そちらをどうぞ。
過去記事：終端行・列の求め方

終端行の次の行、つまり追記行は、「終端行の番号+1」で算出します。
ここまでわかったら、この追記行の各セルへと値を出力します。
以下がリストへ追記するソースコードとなります。

Sub ExcelVBA_012_002()
    Dim objList As Object 'オブジェクト変数を用意
    Set objList = ThisWorkbook.Worksheets("リスト") '「リスト」シートをオブジェクト変数定義
    Dim RowIdx As Long ''追記行の変数を用意
    With objList
        RowIdx = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 '追記行を求める
        .Cells(RowIdx, 1).Value = 1 'A列に書き込み
        .Cells(RowIdx, 2).Value = "[ファイル名]" 'B列に書き込み
        .Cells(RowIdx, 3).Value = "[Q1]" 'C列に書き込み
        .Cells(RowIdx, 4).Value = "[Q2]" 'D列に書き込み
    End With
End Sub

リスト作成のソースコード

ここまでの内容と、前回記事の使用例③を組み合わせ、さらにA列に通番を入力すると、以下のソースコードが出来上がります。
12～16行と、28～35行が今回更新した箇所になります。

Sub ExcelVBA_012_003()
    'フォルダ選択ダイアログを表示する
    Dim objFD
    Set objFD = Application.FileDialog(msoFileDialogFolderPicker) 'オブジェクトを定義
    With objFD
        If .Show = False Then 'ダイアログ表示⇒キャンセルボタンが押された場合
            MsgBox "キャンセルされました", vbInformation 'メッセージ表示
            Exit Sub
        End If
    End With
    'リストの初期化
    Dim objList As Object 'オブジェクト変数を用意
    Set objList = ThisWorkbook.Worksheets("リスト") '「リスト」シートをオブジェクト変数定義
    With objList
        .Rows("2:" & .Rows.Count).ClearContents '2行目から最終行まで値をクリア
    End With
    'FileSystemObjectを定義する
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    'フォルダ内のExcelファイルを開いて情報取得する
    Dim objFile As Object
    Dim RowIdx As Long '追記行の変数を用意
    Dim Cnt As Long '連番用変数を用意
    Cnt = 0 '連番の値をリセット
    For Each objFile In FSO.GetFolder(objFD.SelectedItems(1)).Files
        If Left(objFile.Name, 5) = "アンケート" And Right(objFile.Name, 4) = "xlsx" Then
            Workbooks.Open objFile.Path 'ファイルを開く
            Cnt = Cnt + 1 'ファイルを開いたら連番をカウントアップ
            With objList
                RowIdx = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 '追記行を求める
                .Cells(RowIdx, 1).Value = Cnt 'A列に書き込み(通番)
                .Cells(RowIdx, 2).Value = objFile.Name 'B列に書き込み(ファイル名)
                .Cells(RowIdx, 3).Value = Range("D11").Value 'C列に書き込み(Q1回答)
                .Cells(RowIdx, 4).Value = Range("D16").Value 'D列に書き込み(Q2回答)
            End With
            ActiveWorkbook.Close 'ファイルを閉じる
        End If
    Next
    MsgBox "終了しました", vbInformation
End Sub

A列の通番には、ファイルを読み込んだ回数を記録した変数Cntを入力します。