エクセルで複数ファイルのシートから一つのシートへ結合したい

質問

エクセル上で、
Aフォルダ内にファイルBook1～数十個があり、Book1にはシート名「sh1」、Book2にはシート名「st2」のみがぞれぞれあります。シート内のデータ数はバラバラで何行のデータがあるか不明ですが、列数は同一です。
このファイルすべてを開かずに、今開いている、「加工.xls」のsheet1にまとめたいと思っています。（sh1の下にsh2、その下にsh3・・・を繰り返して、「加工.xls」のsheet1に貼り付ける。行間は空けず一覧表にする。フォルダ内のファイルが無くなったら終了する。）こんな感じのをマクロでやりたいと思っています。

ファイルを開かないで行う方法は、何とか過去の質問を調べてApplication.ExecuteExcel4Macroを使ってやろうとしていますが、応用が利きませんでした。開いていないファイルの最終行をどう取得選択してsheet1に持ってくればよいか分からず悩んでいます。
よろしくお願いいたします。

回答No.8

noname#29107 · ベストアンサー

＞すると、tan2のデータが表示されなく、tan1のデータの後６行目に#REF!と入ります。

ごめんなさい。いろいろミスが多かったので、一応テスト済みのものを書きますね。
Sub test1()
mypth = "C:\データのフォルダのフルパス"
wix = 1
For bkno = 1 To 60
　　bkmei = "tan" & bkno & ".xls"
　　shtmei = "Sh1" & bkno
　　dtend = False
　　For ixr = 1 To 300
　　'1行目が見出しなら1→2、300はデータの最大行数に変更
　　　　For ixc = 1 To 8
　　　　　　Cells(wix, ixc) = Application.ExecuteExcel4Macro _
　　　　　　　("'" & mypth & "\[" & bkmei & "]" & shtmei & "'!R" & ixr & "C" & ixc)
　　　　　　If ixc = 1 And Cells(wix, ixc) = 0 Then
'1列目が文字列の項目で、空白が来ると終了させる
　　　　　　　　Rows(wix).ClearContents
　　　　　　　　dtend = True
　　　　　　　　Exit For
　　　　　　End If
　　　　Next ixc
　　　　If dtend Then
　　　　　　Exit For
　　　　End If
　　　　wix = wix + 1
　　Next ixr
Next bkno
End Sub

＞dtend = True
＞の意味ってなんですか？
dtendをデータファイルの最終行を判断するスイッチ（一般的にこのような変数をフラグといいます）にしています。
データファイルの最終行の次の行は、空白になりますよね。これをExecuteExcel4Macroを使用してセルの値を読み出すと帰ってくるのは0（ゼロ）になります。それで、読み込んだデータのA列の値が0なら、最終行であると考えます。
If dtend Then　という部分がありますが、これは
If dtend = True Then　と同じ意味です。これで最終行以降は（正確には最終行＋１行以降は）データファイルの処理をせず、次のデータファイルの処理に移るようにするというのが流れです。

お礼

ご回答ありがとうございました。今回ので、tan2まで読み込むことに成功しました！
ただ、テストとして、tan2までしかデータを用意しないでいるので、tan3.xlsは存在しないからか、tan2データの次の行に＃REF!と入ってしまい、データの型が一致しませんとエラーが出てしまいます。ファイルが無かったら処理を終わるにはどう設定したらよいでしょうか？

回答No.10

noname#29107

＞ファイルが無かったら処理を終わるにはどう設定したらよいでしょうか？
今回の場合だと本来For bkno = 1 To 2に変更するのが正しいと思いますし、そうしない場合エラーで落ちるというのは順当だと考えますが、あえてエラーを回避するなら、
For ixr = 1 To 300とFor ixc = 1 To 8の間に以下を挿入します。
If Dir(mypth & "\" & BkMei) = "" Then
　　tmp = MsgBox(mypth & "\" & BkMei & " は存在しません。処理を中止します", vbOKOnly)
　　Exit Sub
End If

Dir関数を使用すれば、条件に合致するファイル名を取得することが出来ますので、for ～ next でbknoを変更する代わりに、直接存在するファイルを指定することも出来ます。
ただ、その場合、必ずしもファイル名が（固定部＋連番の形式の時）連番の順に得られないので、一旦条件に合うファイル名を取り出しておいて、並び替えてやる必要があります。（順不同でよいなら、Dirで得られた結果をそのまま利用できます。）
今回は、そこまでは考慮しないこととします。

お礼

ありがとうございます。おかげさまでやりたいことができました！
＞本来For bkno = 1 To 2に変更するのが正しいと思いますし
それが、フォルダ内のファイル数がいつも決まった数とは限らないため、ここも変数でと思っている部分です。
別のシートにフォルダ内にあるファイル名を表示させるマクロも別に作ったりしてあって、それを並び替えて、bkmeiはそちらを参照して、順番通り呼び出そうとも考えたりしたのですが、力不足でまだ及びません。頂いたサンプルで考えてみようと思います。
　とりあえず、一連の流れが整ってきましたので、（長くなってしまってすみません）この辺でポイント発行させて頂きたいと思います。
　また分からないところがありましたら、ぜひよろしくお願い致します。最後までおつきあい頂きましてありがとうございました。

補足

それと、頂いた分を挿入して実行しても、型が一致しませんとのエラーが　IF　ixc=1　And　Cells(ixr,ixc)=0　Thenのところでやはり出てしまっていました。
この0を"0"としたところエラーが出なくなり、正常に実行できました！ありがとうございました。

回答No.9

WWolf

ＷＷｏｌｆです。
質問等のやり取りをみていますと”rebellion”さんとの方法で解決に進んでいそうなので、このＱNo.1120528では深く説明は控えさせていただきます。
Ｓｅｌｅｃｔ～Ｆｒｏｍ文のことはＳＱＬを調べてみれば理解できると思いますよ。
また、今後何か詰まったときに具体的な質問としてまた聞いてください。

お礼

はい、ありがとうございます！
解決までもう一歩です。
またの機会がありましたら、よろしくお願いいたします。
皆さん親切で助かります。

補足

こちらでお礼、対応させて頂きますね。又よろしくお願い致します！

回答No.7

noname#29107

＞tan2のデータがtan1のデータ最終行（５行目）の一行空いて下（７行目からtan2データ）に入りました。一行空いているので６行目からにしたいです。

今はIf ixc = 1 And Cells(ixr, ixc) = 0 Then　が存在する状態ですね？
でしたらwix = wix + 1をNext ixrの上に移動してみてください。

お礼

＞wix = wix + 1をNext ixrの上に移動
しました。すると、tan2のデータが表示されなく、tan1のデータの後６行目に#REF!と入ります。
dtend = True
の意味ってなんですか？

回答No.6

noname#29107

＞その上にtan2.xlsのデータが上書きしてしまいます。
ごめんなさい。初歩的なミスです。
wix=1
を
For bkno = 1 To 60
の上の行に移動してください。

＞中身がtan1.txtのままだとこのマクロは使えませんか？
直接セルにアドレス指定で読みにいきますから、このマクロでは、無理です。

次のマクロでどうでしょう。これもテストが部分的なので、ちゃんと動作しないかもしれませんが。

Sub testr()
Dim wkRgn As Range, wkRgn2 As Range, wkbk As Workbook, wSht As Worksheet
Dim ShtMei, BkMei
Set wkbk = ActiveWorkbook
Set wSht = ActiveWorkbook.Sheets("Sheet1")
mypth = "C:\Documents and Settings\tminami\My Documents"
BkMei = "data1.xls"
ShtMei = "Sheet1"
Debug.Print "start:"; Format(Now(), "hh:mm:ss")
For bkno = 1 To 60
　　bkmei = "Book" & bkno & ".xls"
　　shtmei = "Sht" & bkno
　　Workbooks.Open Filename:=mypth & "\" & BkMei
　　wkbk.Activate
　　Set wkRgn = Workbooks(BkMei).Sheets(ShtMei).Range("A1").CurrentRegion
　　If bkno = 1 Then
　　　　wkRgn.Copy
　　　　wSht.Paste Destination:=Range("A1")
　　Else
　　　　wkrw = Range("A65536").End(xlUp).Row + 1
　　　　wkRgn.Offset(1, 0).Resize(wkRgn.Rows.Count - 1, wkRgn.Columns.Count).Copy
　　　　'↑1行目がタイトルでなければ、wkRgn.Copyにする。
　　　　wSht.Paste Destination:=Range("A" & wkrw)
　　End If
　　Workbooks(BkMei).Close SaveChanges:=False
Next
Debug.Print "e n d:"; Format(Now(), "hh:mm:ss")
End Sub

お礼

迅速なご回答ありがとうございます。再びコードを作成して頂いてありがとうございます。こちらも後でやってみます。
前回までのがもう少しなので、再びご質問です。

wix=1 を移動しました。tan2のデータがtan1のデータ最終行（５行目）の一行空いて下（７行目からtan2データ）に入りました。一行空いているので６行目からにしたいです。
wix = wix + 1をNext ixcの上に持ってくれば６行目から入りますが７行目は#REF!になり以下は空白です。（tan2は７行データがあるはず。）どうしてでしょうか？

回答No.5

noname#29107

＞tan1.xlsのtan1には５行ぐらいデータが入っています。
全部10行くらいまでのデータなんでしょうか？
でしたら開かずに読んだ方が早いかもしれません。

エラーの件ですが、
For bkno = 1 To 60
の60を2くらいに
　　For ixr = 1 To 300
のを10位に変更して、
　　　　　　If ixc = 1 And Cells(ixr, ixc) = 0 Then
　　　　　　　　Rows(ixr).ClearContents
　　　　　　　　dtend = True
　　　　　　　　Exit For
　　　　　　End If
をコメント化するか、一旦削除して実行してみてください。それでtan1の本来データの入っていない行のデータが加工.xls上でどのように入力されるかを見てください。
私の環境で試した時は、元データが無いとき、加工.xlsでは0が入ったので、0だったら、その行を消すようにしました。もし空白の0以外のデータが入るようなら、ロジックの変更が必要になります。

お礼

お返事ありがとうございました。
＞全部10行くらいまでのデータなんでしょうか？
今はテストのためデータの少ないもので試しています。
実際はBook４０ファイルくらい、シート内データ行６００くらいづつになると思います。
＞エラーの件ですが、
ご指示のとおりやってみましたところ、データが無いところは０が入っています。ただ、いったんtan1.xlsの正しいデータが表示された後、 その上にtan2.xlsのデータが上書きしてしまいます。tan1のデータが終わった後からtan2のデータを続けたいです。

データ型のエラーが出た原因が分かりました。
tan1.xlsの見た目に騙されていましたが開くと保存の際、ファイルの種類の初期値が.txt形式になっていました。（元々他機からインポートしてくるデータの為）コレをエクセル形式で保存し直したらエラーが出なくなりました。この場合、ファイル名は確かにtan1.xlsなのですが、中身がtan1.txtのままだとこのマクロは使えませんか？４０件あまりをエクセルに変換しなくてはならなくなるなら、結局開いて貼るにした方がよいかもしれないですね。（泣）

また、Ifのところもコメント化を外して実行したところtan1はデータも表示され０が消え良好ですが、やはりtan2のデータが一行目のみに上書きされて、二行目以下には何も起こりませんでした。コレを直すにはどうしたらよいでしょうか？

回答No.4

noname#29107

＞速度を重視して「開かず」と思った
ExecuteExcel4Macroで他ファイルを参照する場合、対象となる行数・列数によりますが、不定の行数読み込むような場合は、速度的にかなり不利だと思います。基本的にいくつかの特定のセルを読み出したいという場合に使用するものだと思います。

＞「　If ixc = 1 And Cells(ixr, ixc) = 0 Then」
の部分でエラーが出ました。
A1セルに「#REF !」という値が、入っていませんか？
でしたらbkmei、shtmei（bkmeiが実在のファイルでないとき「ファイルを開く」のダイアログが表示されます。）が、誤ってセットされていると思います。shtmeiが誤っている可能性が高いか思います。
実際に参照されるシート名とそこをコーディングされた部分を示してみてください。

お礼

>いくつかの特定のセルを
なるほど、使い分けた方が良いのですね。
教えて頂いてありがとうございます。
苦戦していますが、
>A1セルに「#REF !」という値
は出ていません。
For bkno = 1 To 60
bkmei = "tan" & bkno & ".xls"
shtmei = "tan" & bkno
このままの段階ですと、目的の場所にきちんとtan1.xlsのtan1の一行目が表示されます。
しかし一行表示後にエラーになり、デバックになりますのでそこで止まってしまう感じです。
tan1.xlsのtan1には５行ぐらいデータが入っています。

回答No.3

WWolf

質問者さんは勉強家そうなので直ソースを書くよりヒント形式で答えたいと思います。その方が今後質問者さんの為にもなると思いますし。
まず、データ-＞外部データ取り込み-＞新しいデータベースクエリ-＞ＥｘｃｅｌＦｉｌｅｓ*　を選びます。
ブック選択のフォームが出てくるので指定したいファイルを選びます。
すると多分「このデータそーすには・・・」と出ると思いますがそのままＯＫ
オプションで”システムテーブル”にチェック
すると”使用可能なテーブルと列”のリストにシートが出てきます。
後はウィザード指示に従って進んでください。
次におおよそこの機能が理解できればマクロ記録を使いそのマクロを見て研究してください。（ＳＱＬなども）
結構使えますよ！！
では、また何かわからないことがあれば・・・

お礼

ご回答ありがとうございました！
非常に興味深いですね！エクセルにこんな機能が付いているとは知りませんでした！
マクロ登録してみたらたくさんコード文ができて全部は必要ないんじゃないかなと思いましたが、一カ所
.CommandText = Array( _
"SELECT `TAN1$`.`ああああ `" & Chr(13) & "" & Chr(10) & "FROM ・・・
という行があって、ああああの部分が実際にtan1のセルデータの中身の情報が表示されていました。（タイトル列として出てるのかな）この部分の表記をエクセルデータへの加工なしに、汎用化するにはどうすればよいのでしょう？

回答No.2

WWolf

ブック内にあるデータ形式にもよりますが、もし質問者様のエクセルが外部データ参照の機能をアドイン出来る又はインストールできるなら、それから最終を取得することもデータを取得することも出来ますよ。

お礼

ご回答ありがとうございます。
ブック内のデータは文字列です。
外部データ参照機能を追加インストールできました。
ヘルプを見た限りでは、他のアプリのデータを参照するような事を書いてありましたが、エクセル同士でも使うものなのですか？
どのように使えばよいか、よかったら教えてください。
自分でも調べてみます！

回答No.1

noname#29107

＞このファイルすべてを開かずに、
なぜ開かずに作業するのでしょう？
開く方がかなり早いと思いますが？
「開く」「コピー」「加工ブックに貼付け」「閉じる」の繰り返しの方がすっきりすると思うのですが？
特に開かずにという条件を付けるのは、どういう意図なんでしょうか？

＞開いていないファイルの最終行をどう取得選択して
ExecuteExcel4Macroを使用するなら、一旦加工ブックにコピーして空白行かどうか判定し、空白行なら次のブックの処理に移るようにするしかないと思います。

ざっくっと書くとこんな感じ。（部分的にしかテストしてませんので、サンプルと考えてください）
Sub test1()
mypth = "C:\Documents and Settings\あなたの名前\My Documents"
For bkno = 1 To 60
　　bkmei = "Book" & bkno & ".xls"
　　shtmei = "Sht" & bkno
　　wix = 1
　　For ixr = 1 To 300
　　'1行目が見出しなら1→2
　　　　For ixc = 1 To 8
　　　　　　Cells(wix, ixc) = Application.ExecuteExcel4Macro _
　　　　　　　("'" & mypth & "\[" & bkmei & "]" & shtmei & "'!R" & ixr & "C" & ixc)
　　　　　　If ixc = 1 And Cells(ixr, ixc) = 0 Then
'1列目が文字列の項目で、空白が来ると終了させる
　　　　　　　　Rows(ixr).ClearContents
　　　　　　　　dtend = True
　　　　　　　　Exit For
　　　　　　End If
　　　　Next ixc
　　　　wix = wix + 1
　　　　If dtend Then
　　　　　　Exit For
　　　　End If
　　Next ixr
Next bkno
End Sub

補足

ご回答頂きましてありがとうございます。
大変助かります。
＞＞このファイルすべてを開かずに、
＞なぜ開かずに作業するのでしょう？
＞開く方がかなり早いと思いますが？
ファイルが複数あるため、逐次開いて閉じると処理速度がかかると思いました。速度を重視して「開かず」と思ったのですが、開いて処理させた方が早いんですか？だとしたら私の思い込みでした。開く方法でも問題ないと思います。
　しかしせっかく、開かない方法でご教授頂きましたので、下記マクロを指定してみました。
頂いたサンプルを
「Sub test1()
mypth = "C:\＊＊＊"
For bkno = 1 To 60
　　bkmei = "＊＊＊" & bkno & ".xls"
　　shtmei = "＊＊＊" & bkno　　　」
＊＊＊の部分だけ直して、実行したところ、
「　If ixc = 1 And Cells(ixr, ixc) = 0 Then」
の部分でエラーが出ました。
「実行時エラー１３型が一致しません」となります。
どうしてでしょうか？