複数の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ヶ月)日記
昨日は、クリームシチューづくりを一緒に。
前日のビーフシチューもあり、両方並べてうれしそうでした。
シチューだと、いろいろと野菜も入れたがります。