500個あるファイルの特定部分を抜き出すには？

質問

手元に500個のエクセルファイルがあります。（１つ80KBほど）
それらのそれぞれのファイルからA01からG01までを抜き出して、１つのエクセルファイルにまとめたいと考えています。１つ１つコピー&ペースト以外でどのような方法があるでしょうか。

500個のファイルは連番のファイル名。
抜き出す部分は全て同じ場所。
抜き出したデータから最大値や平均値を出したい。

回答No.5

zap35 · ベストアンサー

＃０３です
Q50:X50を抜き出すなら、以下の２行を置換してください

Const startCOL As Long = 17, lastCOL As Long = 24 '列範囲を指定。Q:17、X:24

wkSTR = "'" & bkPath & "[" & buf & "]" & shtNAME & "'!R50C" & idxC

お礼

御回答ありがとうございます。
以下の通りでマクロを組んで、満足のいく結果が得られました。
Sub 他ブック抽出()
Dim idxC, ofROW, ofCOL As Integer
Dim curROW As Long, wkSTR, buf As String

Const startCOL As Long = 17, lastCOL As Long = 24 '列範囲を指定。Q:17、X:24
Const shtNAME As String = "Sheet1" '←シート名は固定。ここで指定
Const bkPath As String = "C:\ABC\" '←抽出対象のフォルダパスを指定

curROW = 1
buf = Dir(bkPath & "*.xls")
Do While buf <> ""
ActiveSheet.Cells(curROW, 1).Select
ActiveCell.Value = buf
ofCOL = 1

For idxC = startCOL To lastCOL
wkSTR = "'" & bkPath & "[" & buf & "]" & shtNAME & "'!R50C" & idxC '50行
Selection.Offset(0, ofCOL) = ExecuteExcel4Macro(wkSTR)
ofCOL = ofCOL + 1
Next idxC

curROW = curROW + 1
buf = Dir()
Loop
End Sub
'ABCフォルダにある複数ファイル全てのSheet1にあるQ50:X50のセルを
'抽出して、このシート１つに連続で貼り付けるマクロ。
'マクロはALT＋F11でVBE画面を開き、「VBAProjectエクスプローラのシート名右クリック」
'→「挿入」→「標準モジュール」で表示される画面にペースト。
'実行はALT＋F8を押して、マクロ名（他ブック抽出（））を選択します。

質問というよりも、マクロ作成依頼になってしまいました。
本当にありがとうございました。

回答No.4

zap35

＃０３です。以下の前提のもとにマクロを作成しました
・500個のファイルは１つのフォルダに格納されている
・そのフォルダのEXCELファイルは全て抽出対象
・抽出するデータのシート名は共通（同じ名前）
・各シートのA1:G1を抽出する

・現在開いているシートに500ファイルのデータを抽出する（１行が１ファイルのデータ）
・A列にはブック名、B～H列にデータ(抽出元のA列～G列）を抜き出す。

なおマクロ中のシート名、フォルダのパス名は環境に合わせて修正が必要です（CONSTの２行）

Sub 他ブック抽出()
Dim idxC, ofROW, ofCOL As Integer
Dim curROW As Long, wkSTR, buf As String

Const startCOL As Long = 1, lastCOL As Long = 7 '列範囲を指定。A:1、G:7
Const shtNAME As String = "シート名" 　　'←シート名は固定。ここで指定する
Const bkPath As String = "D:\対象フォルダ名\" 　　　'←抽出対象のフォルダパスを指定

　curROW = 1
　buf = Dir(bkPath & "*.xls")
　Do While buf <> ""
　　ActiveSheet.Cells(curROW, 1).Select
　　ActiveCell.Value = buf
　　ofCOL = 1

　　For idxC = startCOL To lastCOL
　　　wkSTR = "'" & bkPath & "[" & buf & "]" & shtNAME & "'!R1C" & idxC
　　　Selection.Offset(0, ofCOL) = ExecuteExcel4Macro(wkSTR)
　　　ofCOL = ofCOL + 1
　　Next idxC

　　curROW = curROW + 1
　　buf = Dir()
　Loop
End Sub

マクロはALT＋F11でVBE画面を開き、「VBAProjectエクスプローラのシート名右クリック」→「挿入」→「標準モジュール」で表示される画面にペーストして下さい。実行はALT＋F8を押して、マクロ名を選択します。

補足

ご意見ありがとうございます。
仮に提示してもらった前提はほぼ合っています。
今は手元にそれらのファイルが無いので、明日以降に
チャレンジしてみます。

回答No.3

zap35

マクロでないと無理でしょう。補足お願いします

・500個のファイルが入っているパスとフォルダ名
・ファイルのネーミングルール（そのフォルダに対象外のファイルがある場合のみ。全ファイルで良いならそのように書いて下さい）
・各ブックで抽出対象となるシート名。また各ブックで対象となるシートは１枚だけ？

・抽出したデータをどのようにまとめればよいか
　（シート名、セルの配列、何行目から開始　等）

補足

補足を付ける場所がズレていますがご容赦下さい。
ご意見もらったようにマクロを作ってみました。
それっぽく動いたのですが、セルの座標を指定する部分がよく理解できません。
Const startCOL As Long = 1, lastCOL As Long = 7 '列範囲を指定。A:1、G:7
この範囲を"Q50:X50"のセルに変更したいので、COLとROWやRangeを使って指定するようなのですが、どう入れ込んだらいいのか、何度も試行しているのですがうまくいきません。範囲の指定をどのように行えばよいのか御指示願えますでしょうか？お手数かけます。宜しくお願いします。

回答No.2

Wendy02

こんにちは。

このご質問では、いくつか分からないことがあります。

シート名が分からないので、今の段階では、一括にマクロで処理するにしても、不可能だと思います。

>A01からG01
と書かれていますが、「A1:G1」のことでしょうか？

500個のExcelファイルは、除外条件というものがあるのでしょうか？
ある程度ファイル名などが決まっているものなのでしょうか？

>抜き出したデータから最大値や平均値を出したい。

というのは、抜き出したデータすべてからの最大値や平均値ということでしょうか？
ということは、3,500個(7 * 500)のデータということでしょうか？

お礼

ご回答、ありがとうございます。
回答を得られやすいように質問したつもりでしたが、
もっと条件が必要みたいでしたね。今後は気をつけます。
他の方に案を出していただいたので、明日以降チャレンジしてみます。
後日、条件を整理して、結果をまとめます。
抜き出して１つのファイルにしてしまえば後は自力でいけると思います。
ありがとうございました。

回答No.1

noname#22222

１、特定のフォルダに対象ファイルを移す。
２、合成したい順番にファイル一覧を作成する。
３、ファイル一覧に従ってファイルを開いて新しいファイルに書き出す。

というような手順かと思います。

２は、コマンドプロンプトでディレクトリを取ってファイルへ出力しても良いかも知れません。

>DIR *.xls > xlslist.txt

xlslist.txtをメモ帳で開いて、合成したい順番になっているのか確認できます。
後は、合成するのみです。

※多分、20～30行程度のVBAコードを書けば実現できると思います。

補足

御回答ありがとうございます。
開くファイルリスト.txtまでの作成は容易なのですが、
抽出して１つのファイルにするのがわからないのです・・・
マクロ必須ですかね