【初心者向け】Excel VBAを使って複数のExcelファイルへの同一作業を自動化してみた

4906 ワード
自動化 VBA Excel マクロ Excel テキストリンク

この記事を読んでできるようになること

  • 基礎的な処理を大体網羅しているので、下記に載せたコードを読みながら勉強すれば手を動かしながらVBAを学べます。
  • いろんな実験データをまとめる自動化の基礎がわかります。

環境

Windows10
Macでも同様の自動化はできるが、ディレクトリの構造などパスの扱い方がWindowsとは異なるため注意が必要。

前提とやりたいこと

「試験データ」フォルダに入っているExcelファイルのデータを転記し、計算結果をまとめたシートを「結果まとめ」フォルダに新たに作成したい。

試験データが入ってるブックに新しくシート作って計算結果もそこに自動で入るようにしたらええやん！という意見もあるかもしれないが、今回は試験データと結果をまとめたデータは独立させたいという考えがあるという前提。

フォルダの構造は以下の通り。
「結果まとめ」フォルダは初めは空で、マクロを実行するとExcelファイルが生成される。

  • BookA：マクロ実行用File

  • テンプレート：試験データ転記、計算結果まとめ用のテンプレート

  • 試験データ/Book●：実験データのマスタ

Code

実際に組んだマクロはこちら
ファイルのパスは各自のPCをみて適宜変更すること

※ヘッダーとかは適当に入れているので各値を求める実際の計算式とは異なる。

Sub data_calc()
    '用いたい変数を定義
    Dim last_column As Long
    Dim data_amount As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    '試験データと結果を入れるフォルダパス
    Const data_path As String = "C:\Users\user\Desktop\マクロ\抽出練習\試験データ\"
    Const result_path As String = "C:\Users\user\Desktop\マクロ\抽出練習\結果まとめ\"
    Dim data_fileName As String
    Dim result_fileName As String
    'data_pathに存在する、拡張子がxlsxのファイルをすべて取ってくる
    data_fileName = Dir(data_path & "*.xlsx")
    Dim fileNumber As Long
    fileNumber = 1
    Do While data_fileName <> ""
        FileCopy "C:\Users\user\Desktop\マクロ\抽出練習\テンプレート.xlsx", "C:\Users\user\Desktop\マクロ\抽出練習\結果まとめ\結果" & fileNumber & ".xlsx"
        result_fileName = "結果" & fileNumber & ".xlsx"
        '結果の中の話
        Workbooks.Open (result_path & result_fileName)
        'オブジェクトの代入はSetを用いる
        Set ws1 = Worksheets("計算結果")
        Set ws2 = Worksheets("コピー先")
        'データの中の話
        Workbooks.Open (data_path & data_fileName)
        data_amount = Range("A1").CurrentRegion.Rows.Count - 1
        Range("A2").Resize(data_amount, 4).Copy Destination:=ws2.Cells(2, 1)
        ActiveWorkbook.Close False
        'テーブルの最下行の行数
        last_column = ws2.Range("A1").CurrentRegion.Rows.Count
        '見出し行を除いたデータ数
        data_amount = last_column - 1
        Dim num As Long
        For num = 2 To last_column
            'データを用いてws1に計算する
            '容量の計算
            ws1.Range("A" & num) = ws2.Range("A" & num) * ws2.Range("B" & num)
            '電力の計算
            ws1.Range("B" & num) = ws2.Range("B" & num) * ws2.Range("C" & num)
        Next num
        data_fileName = Dir
        fileNumber = fileNumber + 1
        ActiveWorkbook.Close True
    Loop
End Sub

実行結果

「結果まとめ」フォルダに試験データのファイル数分結果をまとめたシートが作成できた。

最後に

ここまで読んでいただきありがとうございました。
もっとこうしたらいいよ、などコメント等ありましたらお待ちしております。
この記事が何かの役に立てれば幸いです。

参考テキスト
できる イラストで学ぶ 入社1年目からのExcel VBA できる イラストで学ぶシリーズ