ＶＢＡでたくさんのファイルを一つにまとめる

質問

いつもお世話になっています。ＶＢＡでファイルを順番に起動、処理を行いたいと思っています。
集計001.xls～集計100.xlsというファイルがあります。１ファイルにそれぞれの支店と数値があって、それを総合.xlsファイルに支店シートを作って貼り付けます。

つまり、集計001.xlsには
東京　大阪　福岡
100　100　　100
200　300　　500
100　400　　600
400　500　　100
とあり他ファイルも同じように入っています。（数値は適当です）

それを総合.xlsでは東京シートに
集計001　集計002　集計003
100　　　400　　　500　
200　　　300　　　400
100　　　400　　　300
400　　　200　　　400
と各支店にシートを貼付します。他に大阪シートや福岡シートもできます。

今、「総合.xlsの各支店シート作成」と「集計001.xlsの支店数値→総合ファイル支店シート貼付」はできています。即興で作ったのでかなりいい加減ですが。

問題は「数あるファイルを順番に開いては閉じること」と「ファイル名を取得して各列の１行目にその名前を代入」です。
総合.xlsは常に起動していますが、集計ファイルは途中なかったりします。
そのために、いちいち手動で集計ファイルを起動して、コピペマクロを動かし、手動で集計ファイルを閉じています。

かなりわかりにくい説明だとは思いますが（やっている私もたまに勘違いしています）、ファイルを順番に動かすいい方法ってないでしょうか？できれば001　→　100の順番でいきたいんです。手動だとある時順番を間違えそうだし、ファイル名を１行目に入力するのも面倒なので。
すごくわかりにくいですよね。どうやったら文字列のファイル名をカウントする感じで動かしていくのかわからなくて・・・。どうかお知恵を貸してください。よろしくお願いします。

回答No.2

papayuka · ベストアンサー

ざっと読んだ感じでは、全てを実現するのはある程度VBAの知識が必要と思います。

参考に。

新規ブックに下記のマクロをコピペして、必ず保存する。
同じフォルダに集計xxx.xls のコピーをいくつか用意する。

Sub Test()
Dim wb As Workbook, fName As String, i As Integer, r As Range
For i = 1 To 100
　fName = ThisWorkbook.Path & "\集計" & _
　Format(i, "000") & ".xls"
　If Dir(fName) <> "" Then
　　Set wb = Workbooks.Open(fName)
　　Set r = ThisWorkbook.Worksheets(1).Range("IV1").End(xlToLeft)
　　If r.Value = "" Then
　　　 r.Value = Left(wb.Name, Len(wb.Name) - 4)
　　Else
　　　 r.Offset(0, 1).Value = Left(wb.Name, Len(wb.Name) - 4)
　　End If
　　wb.Close
　End If
Next i
End Sub

お礼

いつも回答ありがとうございます。すごい、途中をカウントしちゃうんですね。stringでファイル名を取得しているので・・・どうやって入れるのかと思っていました。すべてができるようになるにはまだまだ先のようですね。明日は頂いたコードをテストしてみます。で・・・いずれは何とか作れるようになったらと思います。ありがとうございました。

補足

本来お礼を申し上げる場ではありませんが・・・・動作は完璧でした。ありがとうございました。

回答No.1

diashun

diashun です。
これって殆どデータベース（ＤＢ）の世界ですよね。
これから先も、どうしてもＥｘｃｅｌでやるということなら、それなりの回答もありと思いますが、
私なりには、ＤＢ構築をお勧めします。
ご希望の方法を実施するにしても、コピペでの実行は
結構手間がかかりますし、間違いも多いと思います。
データが大量に蓄積する前にＤＢ構築をお勧めします。でもこの場合だとMS ACCESSで十分とは思いますが・・・。

お礼

回答ありがとうございます。そうですよね・・・データ多すぎですよね。でもこの会社ではAccessは使わないで、Excelと決まっているのだそうです。誰が決めたかは知らないけど・・・。コピペでもかなり時間がかかります。それを・・・明日もかなりの量手動＋コピペマクロでしないといけません。順番を間違えないかどうかが心配で心配で。実は１００というのは仮の数字でそれだけのファイルじゃ済まなくて・・・泣いています。アドバイスありがとうございます。