複数のエクセルファイルからのデータの抽出、一覧の作成について

質問

エクセルでのデータ整理の件で以下の質問があります。
以下のことができるようなマクロは組めませんでしょうか？

同一フォルダ（仮に、「C:\Sample」とします）に日々の業務データが
入力されたエクセルファイルが300個ほどあります。
各ファイル内にはSheetが5枚あり、各ファイルで入力されているデータは
すべて同一のフォーマットです（Sheet毎では異なる。
例えば、Sheet1の1行A列には日付データ、Sheet2の2-5行B列には名前データ、といった感じです）。

これら300個のファイルから、これまでのデータを一覧にしたリストを
新規に作りたいと考えています（作成場所はどこでも構いません）。
例えばSheet1の、1列目にはファイル名、2列目には各ファイルSheet1の1行A列の日付データ、
3-6列目には各ファイルSheet2の2-5行B列の名前データ・・・、といった具合です。
（縦に日付が並び、1行内に各ファイルのデータが入力されている、
といった風にしたいと考えています。）

参考になりそうな過去の回答もありましたので、いくつか試してみたものの、
うまくいきませんでした（私はマクロは少しかじった程度です）。
一個ずつコピペでは非常に骨が折れ、難儀しております。

ややこしい質問ではございますが、ご回答お待ちしております。

回答No.7

noname#99913 · ベストアンサー

そのやり方だと、INDIRECT関数を使わなければいけません。
=INDIRECT("["&A1&"]Sheet1!A1")

マクロでは次のようになります。ファイル数のところは正しい数に変えてください。

Option Explicit

Private Sub GetData()

Dim wbk As Workbook
Dim wst1 As Worksheet
Dim wst2 As Worksheet
Dim fnm As String
Dim i As Integer

For i = 1 To 300 'ファイル数

fnm = Sheet1.Cells(i, 1)
Set wbk = Workbooks.Open(fnm, ReadOnly:=True)
Set wst1 = wbk.Worksheets("Sheet1")
Set wst2 = wbk.Worksheets("Sheet2")

ThisWorkbook.Activate
Sheet1.Activate

Cells(i, 2) = wst1.Cells(i, 1)
Cells(i, 3) = wst2.Cells(2, 2)
Cells(i, 4) = wst2.Cells(3, 2)
Cells(i, 5) = wst2.Cells(4, 2)
Cells(i, 6) = wst2.Cells(5, 2)

wbk.Close

Next i

End Sub

お礼

長々とお付き合いくださり、ほんとにほんとにありがとうございます！
回答No.8との組み合わせで以下のようなマクロを組んでうまくできました！

＋＋＋
Private Sub GetData()

Dim wbk As Workbook
Dim wst1 As Worksheet
Dim wst2 As Worksheet
Dim fnm As String
Dim i As Integer

For i = 1 To 300 'ファイル数

fnm = Sheet1.Cells(i, 1)
Set wbk = Workbooks.Open(fnm, ReadOnly:=True)
Set wst1 = wbk.Worksheets(1)
Set wst2 = wbk.Worksheets(2)

ThisWorkbook.Activate
Sheet1.Activate

Cells(i, 2) = wst1.Cells(1, 1)'(i, 1)だと二行目以降うまくいかなかったので変更しました。
Cells(i, 3) = wst2.Cells(2, 2)
Cells(i, 4) = wst2.Cells(3, 2)
Cells(i, 5) = wst2.Cells(4, 2)
Cells(i, 6) = wst2.Cells(5, 2)

wbk.Saved = True'「保存しますか？」という表示が出るときがあるので、強制終了するために追加しました。
wbk.Close

Next i

End Sub
＋＋＋

ワークシートを定義して代入するということを繰り返すようなマクロにすれば良かったのですね。
今回は大変勉強になりました。

本当にありがとうございましたっ！

回答No.8

ai0011_200

解答番号No.6で紹介があるDir関数を使うようにすれば、ファイル名の
取得も可能です。
ただDir関数で取得できるファイル名はパス無しなのでファイルを
Openする時はパスを付ける必要があります。
-----------------------------------------------
　　　StrFolder = "C:\Sample\"
　　　fnm = Dir(StrFolder & "*.xls")

　　　Do While fnm <> ""
　　　　　i = i + 1
　　　　　Cells(i, 1).Value = fnm
　　　　　Set wbk = Workbooks.Open(StrFolder &fnm, ReadOnly:=True)
　　　　　　・
　　　　　　・
　　　　　　・
　　　　　fnm = Dir()
　　　Loop
-----------------------------------------------

それと業務データが入力されたファイルのシート名が固定されていない場合は、
「Worksheets("Sheet1")」を「Worksheets(1)」というように変えることで
対応できます。

それと話は変わりますが、質問するカテゴリ違いですよ。
Windowsカテゴリ内のその他カテゴリではなく、本来はソフトウエアカテゴリ内の
MS Officeカテゴリに質問する内容かと。
正しいカテゴリで質問した方が、解答も付きやすいと思いますよ。

お礼

回答ありがとうございました！
回答No.7との組み合わせでうまく目的の作業を行うことが出来ました。

回答No.6

noname#99913

ファイル名を取得するコマンドはあります。ただ、それを使ったのは私もずいぶん昔のことになるので、今回はお手軽にすませて悪いのですが、下のURLをご紹介します。

http://can-chan.com/vba/filemei-itiransakusei.html

このやり方では名前順になるかどうかは分かりません。もしならなかったら、セルに書き込ませたあとに並べ替えをするのがいいでしょう。

お礼

いえいえ、ご紹介ありがとうございます。

ただ、取得したファイル名の列（例えば、A列）を
「='[A1]Sheet1'!$A$!」
のようにしてもファイル名が指定できないのですが、
こういった場合の指定方法をご存知でしょうか？

もしご存知でしたらお教えください。

回答No.5

noname#99913

たしかに、INDIRECT関数は参照先を開いておかないといけないので、ファイルが300もあると大変なことになります。

ただ、マクロでやる場合も、ファイル名は手作業で入力しなければいけません。ファイル名に規則性があり、計算式でファイル名を生成できれば別ですが。あるいは、名前順or順不同でよければ。

まあ、DOSコマンドでファイル名をテキスト化しておき、それを利用するという手もありますけどね。

お礼

何度も丁寧なご回答ありがとうございます。
質問を重ねてしまい申し訳ないのですが、
ファイル名に規則性はある程度しかないので、
計算式でファイル名指定は難しいと考えています。

ただ、マクロ等で「フォルダ内全てのファイルを対象」というのが
できた記憶がございましたので、最初にこういったマクロは
組めませんでしょうか、と質問した次第です。
（記憶違いかもしれませんが・・・）

ですので、「フォルダ内全てのファイルを対象」で「名前順」で
リスト化 or
「DOSコマンドでファイル名をテキスト化」（←これはできます）
を利用してやる方法をご教授頂ければ、大変うれしく思います。

何度も申し訳ありませんが、よろしくお願いいたします。

回答No.4

noname#99913

=INDIRECT("[091119.xls]Date!A1")

ただし、「091119」を開いておく必要があります。これはINDIRECT関数の仕様です。

もし、単純に他の表の値を参照したいだけなら、次の式ですみます。この場合は、「091119」を開いておく必要はありません。

=[091119.xls]Date!A1

お礼

ご回答ありがとうございました。

ただ、やはり関数だと逐一値の参照の式を入力してやらないと
だめなのですね。
（参照したいファイル数が多いと大変ですね・・・）

やはり、一度にデータをまとめるのは関数では難しいようですね。

重ねて御礼申し上げます。

回答No.3

noname#99913

説明不足でした。

A列のファイル名が拡張子まで入力してあれば、「.xlsx」入りません。また、エクセルのバージョンが2003以前の場合は「.xls」にしてください。

お礼

ご回答ありがとうございました。

INDIRECT関数ははじめてみたのですが、
ご回答から察するに、これは前もってA列にファイル名が
入力されていないと使えないのでしょうか？

例えば、新規のエクセルシートに、
ファイル名：091119.xls
シート名：Date
データの位置：1行A列
のデータを指定したい場合、以下の関数はどのように用いるのでしょうか？

=INDIRECT("["&$A1&".xlsx]Sheet1!"&ADDRESS(ROW(),COLUMN()-1))

重ね重ねの質問で申し訳ありませんが、ご回答お待ちしております。

回答No.2

noname#99913

次の式を試してください。

A1に次の式を入力し、下へコピー
=INDIRECT("["&$A1&".xlsx]Sheet1!"&ADDRESS(ROW(),COLUMN()-1))

B1に次の式を入力し、右へコピー後、コピーしたものを下へコピー
=INDIRECT("["&$A1&".xlsx]Sheet2!"&ADDRESS(ROW(),COLUMN()-1))

回答No.1

noname#99913

関数ではいけないのでしょうか？

補足

上記のことができればもちろん関数でも構いません。

ただこういった複雑なことはマクロを使わないとできない
と思っておりましたので。