複数のエクセルファイルからデータ抽出とまとめる方法について。

質問

500のエクセルファイルがあり、それぞれのエクセルデータのＡ２からＦ２までのデータのをまとめた一覧表（１行目は、１枚目のエクセルファイルのＡ２－Ｆ２、２行目は２枚目のエクセルファイルの・・・、という感じでです）のを、新しいエクセルファイルもしくはテキストなどにでも手早く作れる方法をご存知の方、ご教授ください。コピペでやっていますが嫌になってしまいました。。マクロも触っては見たのですが、２枚目のエクセルファイルを２行目に、３枚目のエクセルファイルを３行目になどの方法の解決は思いつかず、挫折しました。よろしくお願いいたします

回答No.4

kigoshi · ベストアンサー

対象のフォルダをｃドライブの「sample」とした場合で回答します。
新規エクセルブックのシート名タブを右クリックし「コードの表示」
右側のエディタエリアに下記コードを貼り付け、実行して下さい。
Ｇ列にファイル名が入るようになっています。不要であれば
Cells(rIdx, 7).Value = fName
を削除して下さい。

Sub getA_F()
Const myPath As String = "c:\sample\"
Dim rIdx As Long
Dim fName As String
fName = Dir(myPath & "*.xls")
Do Until fName = ""
Workbooks.Open Filename:=myPath & fName
rIdx = rIdx + 1
Me.Range(Cells(rIdx, 1), Cells(rIdx, 6)).Value = ActiveSheet.Range("A2:F2").Value
Cells(rIdx, 7).Value = fName
Windows(fName).Close
fName = Dir
Loop
End Sub

お礼

おかげさまで、一覧表を作ることが出来ました。有難うございました。

マクロ初心者なのでどれが定義語なのかわからず、こんな感じで解析させていただいてます。
Sub getA_F()
getA_F()・・・？保留
Const myPath As String = "c:\sample\"
myPath は文字列として定数扱いとする

Dim rIdx As Long　　
Dim　変数宣言
Long 　長整数型
rIdxは任意の文字
AAAでもかまわない
Dim fName As String
ｆnameは文字列として

fName = Dir(myPath & "*.xls")
Do Until fName = ""
保留

Workbooks.Open Filename:=myPath & fName
rIdx = rIdx + 1
繰り返し作業定義

Me.Range(Cells(rIdx, 1), Cells(rIdx, 6)).Value = ActiveSheet.Range("A2:F2").Value
(Cells(rIdx, 1), Cells(rIdx, 6))の範囲について開いたファイルの("A2:F2")で置き換え

Cells(rIdx, 7).Value = fName
(rIdx, 7)のセルにfName代入

・・・・・
まだ半分以下もわからず、悪戦苦闘しております。
お世話になりました。

回答No.5

squip

path は、適切なフルパスに変更してください。
ファイル名は、1.xls、2.xls、... 500.xls と想定しています、これ以外の形式の場合は、filename を変更します。
シート名は、Sheet1 と想定しています、これ以外の場合は、postfix 内の対応する文字列を変更します。

定数を適切に変更して実行できたら、'// 注意 としている行の "A1:F2" を "A1:F500" に変更します。
上記内容がわからない場合、以下のコードは実行しない方がよいと思います。

Sub do_it()
　　Const path As String = "=""C:\Documents and Settings\Guest\My Documents\[""&"
　　Const filename As String = "ROW()"
　　Const postfix As String = "&"".xls]Sheet1'!R2C"""
　　Dim src As Range
　　Set src = Range("A1:F2")　'// 注意
　　With src.Columns(1)
　　　　.FormulaR1C1 = path + filename + postfix
　　　　.FormulaR1C1 = .Value
　　End With
　　Dim it As Range
　　For Each it In src.Columns(1).Cells
　　　　it.FormulaR1C1 = "='" + it.Value
　　Next
　　src.Columns(1).AutoFill src
　　src.FormulaR1C1 = src.Value
　　Set src = Nothing
End Sub

お礼

有難うございます。No.4さんのマクロで手一杯でまだ分解解析を行えておりません。後ほどこのマクロからも勉強させていただきます。
私の質問にお答えいただき有難うございました。
ご厚意に厚く御礼申し上げます。

回答No.3

n-jun

>また、ファイルは連番です。
>エクセルファイル1のＡ２－Ｆ２を、一覧表のＡ1－Ｆ1
エクセルファイル1.xls　って事？
それとも、1.xls って事？

あとは、ANo.2さんのコメントをまず理解して下さい。

回答No.2

imogasi

人間(質問者）がやっているときは、問題にならないが、プログラムでやると、すぐ問題になるのが、５００のファイルをどういう範疇で選んでくる（指定する）かです。プログラムの経験が少ないようで、質問に書いてない。
例えば
A.同一フォルダにある（それも対象外は含まれて無いか）
B.ブックがあるフォルダが同一で名前が例えば共通文字列＋連番になっているとか。
やりたいことは、各ブックのある決まったシート（これの名称等が同じなのかも書いてない）のA2:F2の６セルのデータを採って、
データ集約ブックの第１行目にセット。
次のブックのA2:F2をデータ集約ブックの第2行目にセットを繰り返す
ということらしい。
必要なVBAのスキルは
（１）フォルダ内のエクセル・ブックの名称を全て捉える。
場合によっては、対象外は捨てる。
（２）Openした・読み込んだあるブックのA2:F2を捉える（Copy)
（３）データ集約ブックに貼り付けか値代入する。
（４）以上繰り返し。終了条件は。
質問者にとっては、丸投げ回答をそのまま使うならともかく、VBAでは荷が重いのでは。
そもそもVBAも経験無いのに５００ものブックを作ったり、扱おうというのは、おかしいと思う。設計段階から、少ないブックに収まるよう考えるべきだ。エクセルは、そんなヘビーユーザーに適したサービスは考えられていない。

回答No.1

n-jun

５００のExcelファイルの一覧表は、同じシート名の同じ範囲にあるのですか？

＞１行目は、１枚目のエクセルファイルのＡ２－Ｆ２、２行目は２枚目のエクセルファイルの・・・、
１枚目のExcelファイルとは、１個目のファイル？複数シートの１番目？

補足

有難うございます。
＞１枚目のExcelファイルとは、１個目のファイル？複数シートの１番目？

複数シートではなく、複数のファイルになります。すべて同じフォルダに入っております。

また、ファイルは連番です。
一覧表は新規に作成して、
エクセルファイル1のＡ２－Ｆ２を、一覧表のＡ1－Ｆ1
エクセルファイル2のＡ２－Ｆ２を、一覧表のＡ2－Ｆ2
エクセルファイル3のＡ２－Ｆ２を、一覧表のＡ3－Ｆ3
・・・・
にできればなと思っております。
言葉足らずになり申し訳ございません。
もしよろしければお知恵をお貸しください。