複数のエクセルブックを一覧にすることはできますか？

質問

200個くらいの同一形式のエクセルを一覧にしたいのですが、
アクセスがない為、エクセルでなんとかできないかと、悩んでおります。

データは1つのフォルダの中に、
1.xls
2.xls
・
・
・
200.xls
と200個あり、それぞれA1:N20の範囲の中のセル内容を、
＿＿｜＿A列＿｜＿B列＿｜＿C列＿｜・・・｜＿？列 ＿｜
1行 ｜_1.xls＿｜＿A1 ＿｜＿B1 ＿｜・・・｜＿N20 ＿｜
2行 ｜_2.xls＿｜＿A1 ＿｜＿B1 ＿｜・・・｜＿N20 ＿｜
3行 ｜_3.xls＿｜＿A1 ＿｜＿B1 ＿｜・・・｜＿N20 ＿｜
上記のように、
A列にファイル名を入れて、B列以降に1つのブックの内容を入れたいです。
（A列へのファイル名は、なくても最悪OKなのですが・・・。）

説明が足りないかもしれませんが、どなたか教えてください。
よろしくお願い致します。

回答No.4

keithin · ベストアンサー

sub macro2()
　dim i
　dim w
　set w = activesheet
　w.cells.clearcontents

　for i = 1 to 200
　　workbooks.open filename:="c:\test\Q(" & i & ").xls"
　　w.cells(i, "B").value = worksheets("フォーマット").range("A1").value
　　w.cells(i, "C").value = worksheets("フォーマット").range("B1").value
　　w.cells(i, "D").value = worksheets("フォーマット").range("N20").value
　　activeworkbook.close false
　next i
end sub
みたいな？

お礼

始めのご回答も、こちらのご回答も、大変的確なご回答をくださいまして、
本当にありがとうございました。

マクロが実行され、とても感動致しました☆

結果、両方の式を組み合わせて使用する事に致しました♪

おかげさまで、無事に解決する事がき、大変助かり、また勉強になりました。
心から感謝しております。

もし、良かったら教えてください。
今回ファイル名は、共通の文字+1～200がふられていましたが、
これが、歯抜けである場合や、共通文字意外が数字ではなく規則性のない文字の場合は不可能でしょうか？？

回答No.3

keithin

ALT+F11を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
　dim i
　dim w
　set w = thisworkbook.worksheets(1)
　w.cells.clearcontents

　for i = 1 to 200
　　workbook.open filename:="c:\test\Q" & i & ".xls"　’★
　　w.cells(i, "B").value = range("A1").value
　　w.cells(i, "C").value = range("B1").value
　　w.cells(i, "D").value = range("N20").value
　　activeworkbook.close false
　next i
end sub

★Q.xlsファイルの保存場所を間違えずに訂正すること

ファイルメニューから終了してエクセルに戻る
ALT+F8を押してマクロを実行する

＃シート名について考えていないようなので，各Qブックはブックを開いただけで間違いなく目的のシートが開くものとします。

補足

とても親切なご回答ありがとうございます。

実際のファイル名は、Q(1).xlsという風に（）の中に1～200まで連番となっておりまして。。。
私の浅はかな考えで、*.xlsもしくは、Q*.xlsでできるかと考えており、
正確なファイル名を書かず、申し訳ございませんでした。

testフォルダをCの直下に配置し、実行をしたのですが、実行エラー'424':となり、デバックを見てみますと、
workbook.open filename:="c:\test\Q" & i & ".xls"　’★が黄色く表示されています。

シート名については、各ブック対象のシートが開くようになっております。
ちなみに対象シート名は、”フォーマット”となっています。

もし、よろしければ、再度お助けください（*＿*）
よろしくお願い致します。

回答No.2

imogasi

INDIRECT関数で参照ブック名を変える方法もあるが、各ブックは開いてないとダメ。２００ファイルもあるとね。
ブック名もばらばらだったりすると
ファイル名をシートのどこかに入力しておかねばならない。
だからそれでは現実的でない。
今日も似た質問があったが、
VBAで
（１）同一フォルダ内のファイル名を１つづつとらえる
（２）（１）のブック名でそのブックを開く
例えばシート１のA1:N20のセルを横方向の１行のセルに代入する。（シート名を質問に書くこと）。同じ名前と仮定しておくが。
この点もバラバラだと難しいことはわかっているのか。
（３）そのブックは閉じる。
（４）（１）（２）（３）をフォルダに存在するすべてのブックについて繰返す。FSO利用の場合はFor Each。
ーー
質問者のVBAの技量は？
（１）を知らないようだから、初心者か。
Googleででも「VBA フォルダ　ファイル名　取得」で照会して勉強のこと。FAQであるのでここには書かない。
http://officetanaka.net/excel/vba/file/file07.htm
など
Dir()関数利用法と、
FSO利用法と在る。
２００のブックを扱うなんてのはエクセルごときでは無理があるだろう。普通は毎日作業して、最終結果が出やすいように
準備していくものだ（日次や月次・期次処理でまとめていくとか）。そういうしシステムの経験を積まないと仕事ではうまく行かない。まだまだ、データをバラバラにためて、あるとき必要な結果を出そうというのは、特別のソフト（高価）を使わないと、無理がある。

お礼

細かく解説、ありがとうございます。
初めての質問で、情報不足で申し訳ございません。

ファイル名は、Q1.xlsという風に”Q”＋"No"を番号順にQ200.xlsにしております。
特定のシートはすべて同じ名前になっております。

他の似た質問を拝見させて頂き、関数を組まずにできる様な印象を受け、
マクロもVBAの知識はないのですが、以前に他の方の質問に記載してあった通りに行ったところ、
魔法の様に実現する事ができ、今回は、マッチする質問が見つけられず、
そのようにできないかと、初めて投稿させて頂きました。。。

回答No.1

bin-chan

できると思います。

１）参照用ブックを新規に作成してください。
２）参照先の一番目（1.xls）を開いてください。
３）１）の一覧表とするシートを開いて、セルＢ１を選択し、= を入力。
４）２）のセルA1をクリックして　[Enter]

これで一覧表とするシートのセルＢ１に、1.xlsのセルA1を参照する式が設定されます。
あとは、残りのセルに式内容をコピーして、適宜置き換え。
でも、できれば一覧表とするシートのセルＡ１には、参照先フォルダを入れるのが良いかも。

お礼

とっても、お早いご回答をくださいまして、ありがとうございます。
言葉が足りず、申し訳ございません。。。
200個のブックがあるので、マクロやVBAなどで一括で処理できる方法を探しておりました。
ずぐに、回答が届いた事とても心強く思いました！
本当に、ありがとうございます～！