複数のExcelファイルからデータを集めるExcelマクロ

フォルダにある複数のExcelファイルからデータを集めるには、Excelマクロ（VBA）を使うと便利です。

※Excelの画面　by　Panasonic S1

データを集める（転記する）Excelマクロ

データを集める、つまり転記するには、次のようなコード（プログラム）を書きます。

〇　＝　△

「〇を△に入れる」というものであり、〇や△に入るのは、データを入れるExcelのセルです。

今回の事例は、この請求書から、会社名（セルA3）と金額（セルE43）を

このようにシート（シート名「data」）に集める（会社名はA列、金額は、B列）ものです。

最終的は、フォルダ内の複数の請求書ファイルすべてから集めますが、こういうとき、まずは、1つのファイルから転記するところをつくりましょう。

〇　＝　△　という形式でつくります。

・集める先のファイルのシート「data」のセルA2に、請求書ファイルのシートのセルA3を入れる

・同じくセルB2に、請求書ファイルのシートのセルE43を入れる

ので、

ThisWorkbook.Worksheets("data").Range("a2" ).Value = ActiveWorkbook.ActiveSheet.Range("a3").Value

ThisWorkbook.Worksheets("data").Range("b2") .Value = ActiveWorkbook.ActiveSheet.Range("e43").Value

といれましょう。

これで、転記ができます。

 

フォルダ内のすべてのファイルを読み込むExcelマクロ

フォルダ内のすべてのファイルについて処理をするには、設定が必要です。

（別の方法もあります）

マクロを書くVBEで、［ツール］→［参照設定］、

［Microsoft Scripting Runtime］にチェックを入れ、［OK］をクリックしましょう。

これで設定は完了です。

次のように書くと、フォルダ内のファイルを読み込めます。

Dim Fd
Set Fd = New FileSystemObject

Dim Fd_name
Fd_name = "フォルダ名"

フォルダ名には、それぞれご自身のフォルダ名を入れていただければ。

これで準備は完了です。

複数のExcelファイルからデータを集める（転記する）Excelマクロ

上記の
・データを転記
・フォルダからファイルを読み込む
を組み合わせていきましょう。

結果的に書くのは、こういうコードです。
（フォルダ名には、それぞれ入れていただければ）

Sub folder_data()

'■フォルダを扱う準備
　　Dim Fd
　　Set Fd = New FileSystemObject

'☆フォルダの場所を指定
　　Dim Fd_name
　　Fd_name = "フォルダ名"

'■転記先の指定
　　Dim n
　　n = 2

'■データを集める
　　Dim W_b
　　For Each W_b In Fd.GetFolder(Fd_name).Files



　　　　If Left(W_b.Name, 1) <> "~" Then '隠しファイル以外

　　　　　Workbooks.Open (W_b.Path)

　　　　　ThisWorkbook.Worksheets("data").Range("a" & n).Value = ActiveWorkbook.ActiveSheet.Range("a3").Value
　　　　　ThisWorkbook.Worksheets("data").Range("b" & n).Value = ActiveWorkbook.ActiveSheet.Range("e43").Value

　　　　　n = n + 1

　　　　　ActiveWorkbook.Close


　　　　End If

　　Next

End Sub

パーツごとに解説していきます。

データの転記先は、は、A2、A3……、B2、B3……と2行目からはじまるので、ここで2と設定します。
繰り返し処理をする中で、2、3、4……と増やしていくものです。

‘■転記先の指定
Dim n
n = 2

データを集めるところでは、次の部分が繰り返す処理をするコードで、

Fd.GetFolder(Fd_name).Filesは、指定した名前のフォルダからファイルを読み取るという意味です。

この部分を繰り返します。

W_b（ファイル＝ブック。任意の名前）で、繰り返すという意味なので、「指定したフォルダにあるすべてのブックで繰り返す」ということです。

ここでIF（条件によって処理）を入れています。
普通にやると、隠しファイルまで読み取ってしまうからです。

隠しファイルは、~で始まるので、「もしファイル名の左から1文字が~でなければ」繰り返すという処理にしています。

なお、指定のフォルダには、データを集めたいファイルのみ入れておきましょう。

条件を満たせば、
・ファイルを開く
・データを転記
・転記先の行を1つ増やす
・ファイルを閉じる
という処理をします。

 

・フォルダの指定
・フォルダ内にファイルを入れる
・どこからどこに転記するかの部分をアレンジする
ことで、使えるかと思いますので、今回のコードを使ってみていただければ。

 

---

■編集後記
昨日は、セミナー準備、Kindle本など。

■「1日1新」
パインジュース
冨澤商店のカオマンガイソース

■娘(4歳2ヶ月）日記
昨日は、クリームシチューづくりを一緒に。
前日のビーフシチューもあり、両方並べてうれしそうでした。
シチューだと、いろいろと野菜も入れたがります。