エクセル フォルダ内の.xlsファイルの集計

質問

エクセルで、複数の.xlsファイルの特定セルを集計するマクロを組みたいのですが、VBA勉強しはじめでうまくいかず、皆様の知識をお借りできればありがたいと思い質問させていただきます。

作業としては
あるフォルダに複数の.xlsファイル（それぞれのファイルは同一形式で、sheet1およびsheet2は作業用シート。sheet3以降がデータの入ったシートとする）を置いておき、それぞれのデータ入りシート（シート数はファイルにより異なる）の特定セルを集計したものを、新たなブックに書き込む
という形です。

その際に、各シートの特定セルの内容により出力するセルを変えたいと思っています。

実際には、
AAA.xlsというファイルの3枚目以降のシートで、L2セルに「Ｂ」と記述があれば別ブックのB3セルに、「Ｃ」と記載されていれば別ブックのC3セルに、それぞれＤ４セルの数値を集計する。
次にBBB.xlsというファイルに関しても同様に集計し、その結果はB4セルとC4セルに出力する
これを当該フォルダに入っているファイル全てについて行う
※L2セルには「Ｂ」「Ｃ」以外の文字は入りません。

こういった作業ができればと思っています。

お時間のある方がおられましたら、ご教授いただけますでしょうか。
参考になるサイトなどがありましたら、アドレスだけでも御教えいただけると幸いです。

質問の仕方が悪いようでしたら、ご指摘いただければ再度説明させていただきます。拙劣な説明ですがどうかよろしくお願いします。

回答No.1

keithin · ベストアンサー

集計するんじゃなくて，単純に転記して並べていくだけですね。

つぎのようなごく基本的なマクロを組み合わせて，応用をしていくだけの作業です。
１．ブックを次々調べて巡回するdir関数の使い方
２．見つけたブックを開く方法
３．開いたブックから値を引き出す方法
４．転記先を適切に設定する方法
などの手段について，VBAのヘルプやヘルプに書いてある使用例などを見ながら理解してください。

手順：
集計ブックを用意し，AAA.xlsやBBB.xlsが入れてあるフォルダに一緒に放り込んでおく
集計ブックに次のマクロを用意して実行する

sub macro1()
　dim myPath as string
　dim myFile as string
　dim i as long
　dim w as workbook
　dim n as long
　dim s as variant

’準備
　thisworkbook.worksheets(1).range("B:C").clearcontents
　thisworkbook.worksheets(1).range("B2:C2") = array("B","C")
　mypath = thisworkbook.path & "\"
　n = 3

’そこにあるブックを調べる
　myfile = dir(mypath & "*.xls*")
　do until myfile = ""
　　if myfile <> thisworkbook.name then

　’開いて調査する
　　set w = workbooks.open(filename:=mypath & myfile)
　　for i = 3 to w.worksheets.count
　　　s = w.worksheets(i).range("L2")
　　　if s="B" or s="C" then
　　　　thisworkbook.worksheets(1).cells(n, s) = w.worksheets(i).range("D4").value
　　　　n = n + 1
　　　end if
　　next i
　　w.close savechanges:=false
　　end if
　　myfile = dir()
　loop
end sub

お礼

補足と御礼間違えておりました・・・かさねがさね失礼しました。

補足

早速のお返事ありがとうございます。

私の書き方がよくありませんで、誤解を招いてしまいました。
「Ｂ」というのは文字であり、セルの列を表すわけではありませんでした。たとえば「東京」と同じ感じです。（同様に「Ｃ」も「大阪」のような文字として考えておりました。）

でも、ご教授いただいたコードを参考に変えてそこはクリアできました。

あともう一つ、単純に転記するわけじゃなく集計するマクロを考えております。

ですので、
・AAA.xlsというファイルの各シートのうち、L2セルが「東京」のシート（複数あるとする）のD4セルの数値を集計して集計ブックのB3セルに出力する。同様に、L2セルが「大阪」であるシートのD4セルの合計値を集計ブックのC3セルに出力する
・BBB.xlsのファイルについても同じように処理し、それぞれB4・C4セルに出力する

といった処理を考えておりました。

しかしながら、なんとなく私の今のスキルでは無謀なことに挑戦しているようですので、ひとまず独力でやってみたほうがいい気がしてきました。

上記コードは大変勉強になりました！ありがとうございました。

回答No.2

imogasi

ＶＢＡをはじめて間もないようなのに、こういうブックをまたいだ課題をこなすのは無理があると思う。
ブックーシートーセルのオブジェクトの階層関係などもわかってないのでは。
＞あるフォルダに複数の.xlsファイル・・
のブックの名前をとらえるのは、よく出る質問である。
Ｇｏｏｇｌｅででも「フォルダ　ファイル　捕まえる」ででも照会すれば、沢山記事があり（１００万？）その中にはコード例が出る記事も在る。
勉強のこと。
ーー
１つのブック名が決ると、そのブックを開くコードを勉強する。
ブックを開かないとシートデータは取れないと心得ること。
ーー
またはエクセルブック名が２０ぐらい以下の数で、増減しないなら、ブック名を文字列でプログラムの中で指定する方法もある。
シートのセルに対象データ元のブック名を指定する方法もある。
標準の方法を追求・実現するのでなく、コーディングのやさしい方法で、当面凌ぐ手も考えないと実際問題前に進まない。
ー
そして望みのブックの、望みのシートの、望みのセルの計数のとらえ方の勉強が必要だが、それは
モデル的に１つのデータ元ブックと集計ブック（１つのブック固定）を開いて、データ転記などして、マクロの記録を取ってコードがどうなるか、勉強してみたら。
そのままでは完成しないが、データの取り方などはわかるだろう。
そこぐらいはやった上で質問を改めてやるべきだ。
ーー
似た件の別件だが、１ブックの複数シートのＢ４セルの値を足しこんで、合計を出すコードなどわかるのか。
ーー
＞L2セルに「Ｂ」と記述があれば別ブックのB3セルに・・
これなど、ＶＢＡを少し判っていれば、書くまでも無いこと。ＩＦ文を使って、場合をＢとＣの場合に分けたら仕舞い。
何処が本題で難しいか、判ってないか、在るセルの集計のコードさえも、判らないのかこころもとない。
ーーー
ＶＢＡについては、丸投げするコーナーではない。もっと簡単なケースからやっていかないと丸投げになるだろう。回答のコードの意味の理解も出来ないだろう。

お礼

勉強不足で申し訳ございません。やはり大それたことを実現しようとしていたのですね。

総計が、結局50ぐらいのブックになりそうなので（また、状況に寄りブック名を私の側で固定することが出来かねる状況です）、keithinさんのコードを参考に独力で頑張ってみたいと思います。

マクロ記録を取って…というのは確かに試しておりませんでした。至らなさに反省するばかりです。
VBAの本をきっちり読んで勉強していきたいとおもいますので、勘弁してください。

やはりブックをまたぐということで最初からわからないと決めつけてかかっていたのかもしれません。今後質問はせずに自力で頑張ります。