複数のファイルの、複数のシートにあるD9セルの数字を、新しいブックにリ

質問

複数のファイルの、複数のシートにあるD9セルの数字を、新しいブックにリストしたいのですが、上手くいかないようです。
以下、それらしきマクロのコピペです。
（使用ソフトはエクセル2003です）

Sub sample()
Dim folder As String
Dim sh As Worksheet
Dim file As String
Dim r As Long

folder = "C:\abc\M1501～M2140\" 'ファイルがあるフォルダ

sh.Range("A1").Value = "ファイル名" '見出し
sh.Range("B1").Value = "A1" '同上

r = 2 '結果出力行の初期値
file = Dir(folder & "*.xls") 'フォルダ内の最初の.xlsファイルを取得
Do While file <> "" 'ファイル名がある間
Workbooks.Open folder & file 'そのファイルを開く
sh.Range("A" & r).Value = file '結果シートのA列にファイル名を
sh.Range("B" & r).Value = ActiveWorkbook.Sheets("あいうえお").Range("D9") '結果シートのB列に開いたブックのあいうえおのD9の値

ActiveWorkbook.Close False '開いたブックを閉じる
r = r + 1 '結果出力行+1
file = Dir '次のファイル名取得
Loop '繰り返す
End Sub

これですと、「あいうえお」のシートのD9しか結果表示されないようです（実際、これは複数ファイルからの抽出用です）。
この式に複数シート対応の式を加えれば出来そうな気がするのですが、ここからどうすればいいかわかりません；

上記の式を大幅変更でも構いませんので、教えて下さい。

回答No.1

o_chi_chi · ベストアンサー

ここまできているのに。。。。

＞sh.Range("B" & r).Value = _
　　　ActiveWorkbook.Sheets("あいうえお").Range("D9")

これは当然、「あいうえお」のシートのD9のセットですよね。
上記の行をブックのシート数分ループするように変更するだけです。

---
For i = 1 to ActiveWorkbook.Sheets.Count
sh.Range("B" & r).Value = _
　　　ActiveWorkbook.Sheets(i).Range("D9")
r = r + 1
next i

補足

お蔭様で全てのシートのデータを取得できるようになりました。
どうやらこのマクロだとシート追加の行をいれないと動かなくなるようでした。
素人は適当に編集するもんじゃありませんね；

さて、せっかくでしたので、ファイル名の取得だけでなくシート名の取得も出来るようにしたいと思いましたが・・・

Sub sample()
Dim folder As String
Dim sh As Worksheet
Dim file As String
Dim r As Long

folder = "C:abc\M1501～M2140\" 'ファイルがあるフォルダ
Set sh = Worksheets.Add(After:=Worksheets(Worksheets.Count)) '結果用にシート追加（今までのシートの後ろに）

sh.Range("A1").Value = "ファイル名" '見出し
sh.Range("B1").Value = "A1" '同上
sh.Range("C1").Value = "B3" '同上
r = 2 '結果出力行の初期値
file = Dir(folder & "*.xls") 'フォルダ内の最初の.xlsファイルを取得

Do While file <> "" 'ファイル名がある間
Workbooks.Open folder & file 'そのファイルを開く
sh.Range("A" & r).Value = file '結果シートのA列にファイル名を
sh.Range("B" & r).Value = sheet

For i = 1 To ActiveWorkbook.Sheets.Count
sh.Range("C" & r).Value = _
ActiveWorkbook.Sheets(i).Range("D9")
r = r + 1
Next i '結果シートのC列に開いたブックのSheet1のA1の値

ActiveWorkbook.Close False '開いたブックを閉じる
r = r + 1 '結果出力行+1
file = Dir '次のファイル名取得
Loop '繰り返す
End Sub

直感で
sh.Range("B" & r).Value = Sheet
と追加しましたがどうやらこれ自体がまったく違うようで、Bが空欄で出力されてしまいます。
どのようにすればいいでしょうか？

回答No.2

imogasi

質問のコードでは「ｓｈ」が具体的に決る（決める）までに、使っているがこれはおかしい。
set Workbooks(ブックが決った段階でそのブック名).Worksheets(book名シート名またはシートのインデックス番号）
のように、シートオブジェクトを特定してから使わないといけない。
「複数の」とか、なぜ、一般的なあいまいな表現で質問するのですか。
フォルダに在るエクセルブックすべてでしょう？
シートも同じです。「複数」のと言っているが、そのブックに存在するすべてのシートなのか？
一部のようにも採られかねない。
プログラマは文章表現に神経質になれ。特に質問では。其れで大幅にかわることもあるよ。
ブック内のすべてならFor Each sh In Wb.Worksheetsが使えないかやってみること。
Wbは今問題にしているブックオブジェクト。Setで具体的にブックオブジェクトを指定した後のこと。