依頼

• 数＋単位のエクセルをコピペしてまとめる作業が面倒
• 該当ファイルの同じ部分をコピーして同じシートにまとめたい

流れ

• まとめたいファイルを一時的にコピー用フォルダに移動or複製
• batでファイル名のみを取得->CSVとして吐き出す
• VBAを用意しているエクセルにCSVのA列をペースト
• マクロを実行して該当ファイルの該当範囲をシートに移す

作成コード

batファイル

Open summary For Output As #1
    cd /d %~dp0 

    REM  summary.csvを作成
        type nul > summary_list.csv

    REM summary.csvに書き込み
    
        REM /b（ディレクトリ名だけを取得） /o（並び替え）：n（名前の順） /ad（フォルダ名のみ出力）【参考サイト：https://www.javadrive.jp/command/file/index1.html http://office-qa.com/win/win95.htm】
        Dir /b /o:n %~dp0\files >> summary_list.csv

    REM メッセージボックスで完了通知
    mshta vbscript:execute("MsgBox(""完了しました""):close")
    
Close #1

VBA

    Dim sDir As String
    Dim now_row,now_row_data As Long
    Dim ws_table_paste,ws_table_data As Worksheet
    Dim ws_source As Workbook
        
    Set ws_table_paste = ThisWorkbook.Sheets("paste")
    Set ws_table_data = ThisWorkbook.Sheets("data")

    now_row = 1
    now_row_data = 1

    Do While True
    
        now_name = ws_table_paste.Cells(now_row, "A").Value
        now_file = ThisWorkbook.Path & "\files\" & now_name        

        If now_name = "" Then
            Exit Do
        End If
        
        Set ws_source = Workbooks.Open(now_file)
        Application.DisplayAlerts = False
        
        ws_source.Worksheets("データセット1").Range("A2:K201").Copy 
        ws_table_data.Cells(now_row_data,"A").PasteSpecial 
        ' xlPasteFormulasAndNumberFormats

        
        Application.CutCopyMode = False  'コピー切り取りを解除
        
        ws_source.Close False  'マスターデータ取り込み先のファイルを閉じる
        Application.DisplayAlerts = True
    


        now_row = now_row + 1
        now_row_data = now_row_data + 200
    Loop

    
    MsgBox "完了しました"

End Sub

参考ページ

参考URL：Windows：フォルダ内のファイル名一覧を取得（出力）するには

例：デスクトップの「Works」フォルダのファイル名一覧をC:\File.txtというテキストファイルに保存する場合は以下のように入力します。

 Dir /b C:\Users\YamadaTaro\Desktop\Works   >C:\File.txt

参考URL：EXCEL VBA 別のブックからデータをコピーする（ブック間のシートコピー）（テクニック）