エクセルでのデータ整理の件で以下の質問があります。
以下のことができるようなマクロは組めませんでしょうか?
同一フォルダ(仮に、「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ベストアンサー
- 回答日時:
そのやり方だと、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
- 回答日時:
解答番号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.6
- 回答日時:
ファイル名を取得するコマンドはあります。
ただ、それを使ったのは私もずいぶん昔のことになるので、今回はお手軽にすませて悪いのですが、下のURLをご紹介します。http://can-chan.com/vba/filemei-itiransakusei.html
このやり方では名前順になるかどうかは分かりません。もしならなかったら、セルに書き込ませたあとに並べ替えをするのがいいでしょう。
いえいえ、ご紹介ありがとうございます。
ただ、取得したファイル名の列(例えば、A列)を
「='[A1]Sheet1'!$A$!」
のようにしてもファイル名が指定できないのですが、
こういった場合の指定方法をご存知でしょうか?
もしご存知でしたらお教えください。
No.5
- 回答日時:
たしかに、INDIRECT関数は参照先を開いておかないといけないので、ファイルが300もあると大変なことになります。
ただ、マクロでやる場合も、ファイル名は手作業で入力しなければいけません。ファイル名に規則性があり、計算式でファイル名を生成できれば別ですが。あるいは、名前順or順不同でよければ。
まあ、DOSコマンドでファイル名をテキスト化しておき、それを利用するという手もありますけどね。
何度も丁寧なご回答ありがとうございます。
質問を重ねてしまい申し訳ないのですが、
ファイル名に規則性はある程度しかないので、
計算式でファイル名指定は難しいと考えています。
ただ、マクロ等で「フォルダ内全てのファイルを対象」というのが
できた記憶がございましたので、最初にこういったマクロは
組めませんでしょうか、と質問した次第です。
(記憶違いかもしれませんが・・・)
ですので、「フォルダ内全てのファイルを対象」で「名前順」で
リスト化 or
「DOSコマンドでファイル名をテキスト化」(←これはできます)
を利用してやる方法をご教授頂ければ、大変うれしく思います。
何度も申し訳ありませんが、よろしくお願いいたします。
No.4
- 回答日時:
=INDIRECT("[091119.xls]Date!A1")
ただし、「091119」を開いておく必要があります。これはINDIRECT関数の仕様です。
もし、単純に他の表の値を参照したいだけなら、次の式ですみます。この場合は、「091119」を開いておく必要はありません。
=[091119.xls]Date!A1
ご回答ありがとうございました。
ただ、やはり関数だと逐一値の参照の式を入力してやらないと
だめなのですね。
(参照したいファイル数が多いと大変ですね・・・)
やはり、一度にデータをまとめるのは関数では難しいようですね。
重ねて御礼申し上げます。
No.3
- 回答日時:
説明不足でした。
A列のファイル名が拡張子まで入力してあれば、「.xlsx」入りません。また、エクセルのバージョンが2003以前の場合は「.xls」にしてください。
ご回答ありがとうございました。
INDIRECT関数ははじめてみたのですが、
ご回答から察するに、これは前もってA列にファイル名が
入力されていないと使えないのでしょうか?
例えば、新規のエクセルシートに、
ファイル名:091119.xls
シート名:Date
データの位置:1行A列
のデータを指定したい場合、以下の関数はどのように用いるのでしょうか?
=INDIRECT("["&$A1&".xlsx]Sheet1!"&ADDRESS(ROW(),COLUMN()-1))
重ね重ねの質問で申し訳ありませんが、ご回答お待ちしております。
No.2
- 回答日時:
次の式を試してください。
A1に次の式を入力し、下へコピー
=INDIRECT("["&$A1&".xlsx]Sheet1!"&ADDRESS(ROW(),COLUMN()-1))
B1に次の式を入力し、右へコピー後、コピーしたものを下へコピー
=INDIRECT("["&$A1&".xlsx]Sheet2!"&ADDRESS(ROW(),COLUMN()-1))
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける (再質問) 4 2022/09/14 22:51
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける 3 2022/09/10 07:55
- Excel(エクセル) 【VBA】指定フォルダに格納中のテキストファイルをエクセルで処理し結果のエクセルを新規フォルダに保存 1 2022/03/25 14:19
- Excel(エクセル) フォルダ内のエクセルファイルを開かずにデータ採取する関数式 2 2022/12/22 22:15
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) 複数ファイルのデータの統合について 12 2022/05/14 12:03
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- その他(プログラミング・Web制作) Pythonで、データファイルと列名ファイルを1つのファイルにしたいです。 1 2023/07/27 20:29
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
onedriveの中身を削除したらパ...
-
同期センターのマークが出てま...
-
Winmerge ファイルの中身は同じ...
-
ファイルの先頭行を全て削除したい
-
Windows10 フォルダ内の自動並...
-
クラリスワークスのファイルを...
-
.pkgというファイルについて。
-
バッチファイルで変数の中身を...
-
mac ファイルの詳細情報を編集...
-
Excellにて→ファイルエラー:デ...
-
たくさんのフォルダを一つのフ...
-
共有フォルダ内のファイル使用...
-
コマンドプロンプトによるフォ...
-
mrl→midファイルへ
-
office365 ファイルを削除したい
-
エクセルファイルの完全消去に...
-
outlookuの添付ファイルが開け...
-
exFATに欠点はありますか?
-
Kshでエラー(no closing quote...
-
USBメモリ、外付けUSB HDDへの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Winmerge ファイルの中身は同じ...
-
onedriveの中身を削除したらパ...
-
同期センターのマークが出てま...
-
バッチファイルで変数の中身を...
-
コマンドプロンプトによるフォ...
-
Windows10 フォルダ内の自動並...
-
教えてください!困ってます。
-
ファイルの先頭行を全て削除したい
-
emlをtxtに変換するには?
-
ファイルの単位
-
Windows11 でのファイル削除
-
クラリスワークスのファイルを...
-
Excellにて→ファイルエラー:デ...
-
office365 ファイルを削除したい
-
macのdmgファイル(動画)をMP4...
-
mac ファイルの詳細情報を編集...
-
ファイル操作(削除など…)の履...
-
linuxのエディタgeditは複数フ...
-
BAKファイルを非表示にする方法
-
mac 表示できなくなりました。
おすすめ情報