以下は、ネット検索で見つけたサンプルコードです。
同じフォルダ内の全ブックのSheet1のA1:B10をThisWorkbookのSheet1に統合しています。
Sub test2()
Dim MyFile As String, MyPath As String
Dim SumFile() As Variant, i As Long
MyPath = ThisWorkbook.Path & "\"
MyFile = Dir(MyPath, vbNormal)
Do Until MyFile = ""
If MyFile <> ThisWorkbook.Name Then
ReDim Preserve SumFile(i)
'A1からB10の値を変数に代入
SumFile(i) = "'" & MyPath & "[" & MyFile & "]Sheet1'!R1C1:R10C2"
i = i + 1
End If
MyFile = Dir
Loop
If i = 0 Then MsgBox "データが有りません": Exit Sub
Worksheets("Sheet1").Range("A1").Consolidate Sources:=SumFile()
End Sub
質問1
Sheet1だけでなく全シートのA1:B10をThisWorkbookのSheet1に統合するためにはどう書き換えればよいのでしょうか?
質問2
上記コードではなぜ、ブックを開かずにデータがとれるのでしょうか?
No.3ベストアンサー
- 回答日時:
merlionXX さんは ヘルプなどのドキュメントをご自分で調べる方だと思います。
十分なスキルがありますので、基本的なことは説明しません。
が、何故かデバッグの方法を教えてくれる人やドキュメントはなかなか見ませ
んね、、、
良い機会なので、デバッグ のアドバイス です。
1. On Error ~ ステートメント は コメントアウト しておく
2. VBE の ローカルウインドウ を表示
3. 再度実行し、エラー 停止時の変数を ローカルウインドウ で チェック
この様にして、#1 補足欄のコードを実行し、エラー停止時の変数の状況をチェック
します。すると、オブジェクト変数 wb が Nothing になっていました。
これが原因で wb.Close に失敗しています。
次に wb が何故 Nothing になってしまったかを検証します。
今度はプロシージャを 「ステップイン」で一行ずつ実行してみます。
F8 キーを押すことで順次コードを実行していきますので、これでプログラムの流れ
がわかります。
すると、MyFile = Dir(MyPath, vbNormal) で返されたファイル名が
ThisWorkbook と一致した場合、
If MyFile <> ThisWorkbook.Name Then ~ End If
内に書かれた Set wb = ~ をすり抜けて wb.Close に飛んでいることに気付くと
思います。
このような手順で行ってみて下さい。
この回答への補足
わかりました!
ReDim Preserve SumFile(i)の位置が悪いんですね?
For Nextの中にもっていかないと配列のワクが広がらないからインデックスエラーなんですね?
デバックの方法までご丁寧にありがとうございます。
#2でご教示のコードを動かしたところ、やはり同じところがエラーになりました。「インデックスが有効範囲に無い」というエラーです。
SumFile(i) = "'" & MyPath & "[" & MyFile & "]" & sn & "'!R1C1:R10C2" '←ここでエラー!!
ご教示のF8で順次コードを実行して見ていくと、最初のブックの2枚目のシートにきたところでエラーが出ます。でも変数にはすべて値が入っておりなぜかわかりません。(゜〇゜;)?
どこが悪いのでしょうか?
No.4
- 回答日時:
> For Nextの中にもっていかないと配列のワクが広がらないから
> インデックスエラーなんですね?
ああ、、見落としてましたね。
こちらでは、1 ブック 1シートの手抜きテストだったものです
から、、すみません。その通りです。完全にスルーしてました。
(^^;)
あと、掲示板で質問するときは、エラーメッセージの内容か、
エラー番号を教えてもらえると話が通りやすくなりますよ。
No.2
- 回答日時:
wb.Close の位置が違います。
それから、コードで直した方が良いと思う点が幾つかありました。ご参考
下さい。
Sub test2()
Dim MyFile As String
Dim MyPath As String
Dim sn As String
Dim SumFile() As Variant
Dim i As Long
Dim wb As Workbook
Dim sh As Worksheet '<-- ココ
MyPath = ThisWorkbook.Path & "\"
MyFile = Dir(MyPath & "*.xls", vbNormal) '<-- ココ
Do Until MyFile = ""
If MyFile <> ThisWorkbook.Name Then
ReDim Preserve SumFile(i)
'選択したファイルをLink更新なしで開く
Set wb = Workbooks.Open(MyPath & "\" & MyFile, UpdateLinks:=0)
For Each sh In wb.Worksheets
sn = sh.Name
SumFile(i) = "'" & MyPath & "[" & MyFile & "]" & sn & "'!R1C1:R10C2"
i = i + 1
Next
'選択したファイルを閉じる
wb.Close SaveChanges:=False '<-- ココ
End If
MyFile = Dir
Loop
Set wb = Nothing '<-- ココ
If i = 0 Then MsgBox "データが有りません": Exit Sub
Worksheets("Sheet1").Range("A1").Consolidate Sources:=SumFile()
End Sub
No.1
- 回答日時:
こんにちは。
KenKen_SP です。【質問1 について】
> SumFile(i) = "'" & MyPath & "[" & MyFile & "]Sheet1'!R1C1:R10C2"
この部分が肝です。全シートを対象にするなら、Sheet1 の部分をループ
処理で書換えてやれば良いのです。ただ、下記の質問2に関連しますが、
ブックに含まれるシート名のリストを得るためには、結局ブックを開く
ことになりますね、、
【質問2 について】
厳密に言えば、例外なくどんなファイルでも開かずにデータを得ることは
不可能です。
しかし、ユーザーに「開く動作」を意識させないことは可能です。
簡単な例で言えば、Workbooks.Open の動作を Application.ScreenUpdating
で画面描写を停止させてしまう方法がありますね。「開く動作」を意識させ
ずに、シートのデータを得る方法は、他にもあります。
・Excel のリンクを使う 例)='C:\[test.xls]Sheet1'!$A$1
・ExecuteExcel4Macro メソッド(Excel4.0Macro)を利用する
・DAO や ADO でブックに接続する
・バイナリデータを直接解析する
いずれの場合も、何らかの形でファイルは開いています。
Consolidate メソッドが内部でどのように問い合わせをしているのか、その
方法はわかりませんが、同様に「開く」を意識させないように実装されて
いるだけに過ぎません。
で、つまるところ実現したいことは、、
「 Workbooks.Open を使わずに、シートのデータを取得したい」
ということですか?
この回答への補足
「開かずに」が重点ではありません。
実は、Sheet1をループ処理するためにWorkbooks.Open で以下のようにしてみたのですがエラーになってしまいました。それで困って質問したのです。
Sub test2()
Dim MyFile As String, MyPath As String, sn As String
Dim SumFile() As Variant, i As Long
Dim wb As Workbook
MyPath = ThisWorkbook.Path & "\"
MyFile = Dir(MyPath, vbNormal)
Do Until MyFile = ""
If MyFile <> ThisWorkbook.Name Then
ReDim Preserve SumFile(i)
Set wb = Workbooks.Open(MyPath & "\" & MyFile, UpdateLinks:=0) '選択したファイルをLink更新なしで開く
For Each sh In wb.Worksheets
sn = sh.Name
SumFile(i) = "'" & MyPath & "[" & MyFile & "]" & sn & "'!R1C1:R10C2" '←ここでエラー!!
i = i + 1
Next
End If
wb.Close '選択したファイルを閉じる
MyFile = Dir
Loop
If i = 0 Then MsgBox "データが有りません": Exit Sub
Worksheets("Sheet1").Range("A1").Consolidate Sources:=SumFile()
End Sub
こんにちは。KenKen_SPさん。いつもお世話になります。
長くなりますので、補足の欄に書かせていただきました。
よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) フォルダ内の全ブックのシート名を変更したい 7 2022/09/22 21:34
- Visual Basic(VBA) InputBoxでキャンセルボタンを押したらファイル自体を閉じたい 3 2022/07/23 17:52
- Visual Basic(VBA) 【変更】ファイルを閉じてダイアログで保存した時、更新したシートだけの処理の実行をする 5 2022/03/26 18:31
- Visual Basic(VBA) VBAのユーザーフォームのテキストボックスに入力制限をしたい 6 2022/11/15 08:28
- Visual Basic(VBA) ExcelVBAに関する質問 3 2023/02/17 10:47
- その他(Microsoft Office) マクロVBAについて 1 2022/09/06 18:12
- Visual Basic(VBA) ExcelVBAでDo Until loopのネスト、IF文を使って一致する物と一致しない物としたい 11 2022/12/24 17:46
- Visual Basic(VBA) VBAが止まります。 1 2022/09/02 14:51
- Visual Basic(VBA) VBAが止まります。 2 2022/09/02 14:02
- Visual Basic(VBA) あるフォルダーのファイルを違う親フォルダーのサブフォルダーに移したい 11 2023/02/15 19:00
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
◾️Excel VBA 統合について Consolidateメソッドを使用する際、Excelブックを
Visual Basic(VBA)
-
Consolidateメソッドの引数のSource部を自動取得をしたい
Excel(エクセル)
-
【Excel VBA】 Excelの統合機能の、統合元範囲の記述に関して教えてください
Excel(エクセル)
-
-
4
Consolidateの範囲
Visual Basic(VBA)
-
5
エクセルVBA 串刺し計算の際、シートをインデックスで指定できますか。
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのクイックアクセスツ...
-
エクセルでCtrl+Tでテーブルの...
-
DATE関数で現在の年齢を出した...
-
Excelで表を作ったところに文字...
-
エクセルのクイックアクセスツ...
-
在庫管理表に使うエクセルの関...
-
ユーザー定義関数をアドイン登...
-
職場の人から聞かれており、こ...
-
REGEXREPLACE関数について、
-
エクセルでバーコード作成し使...
-
下記マクロでMsgBox "空白です...
-
Excel関数-文字列で自動作成さ...
-
エクセルで表
-
【マクロ】for next構文について
-
エクセルの関数について教えて...
-
Excelデータをコピペして、ペー...
-
エクセルで特定の範囲内から小...
-
PDFの請求明細をエクセルにしたい
-
Excelで50個のセルに同じ文字を...
-
エクセルで会社の従業員のデー...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報