![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
エクセル上で、
Aフォルダ内にファイルBook1~数十個があり、Book1にはシート名「sh1」、Book2にはシート名「st2」のみがぞれぞれあります。シート内のデータ数はバラバラで何行のデータがあるか不明ですが、列数は同一です。
このファイルすべてを開かずに、今開いている、「加工.xls」のsheet1にまとめたいと思っています。(sh1の下にsh2、その下にsh3・・・を繰り返して、「加工.xls」のsheet1に貼り付ける。行間は空けず一覧表にする。フォルダ内のファイルが無くなったら終了する。)こんな感じのをマクロでやりたいと思っています。
ファイルを開かないで行う方法は、何とか過去の質問を調べてApplication.ExecuteExcel4Macroを使ってやろうとしていますが、応用が利きませんでした。開いていないファイルの最終行をどう取得選択してsheet1に持ってくればよいか分からず悩んでいます。
よろしくお願いいたします。
No.8ベストアンサー
- 回答日時:
>すると、tan2のデータが表示されなく、tan1のデータの後6行目に#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 と同じ意味です。これで最終行以降は(正確には最終行+1行以降は)データファイルの処理をせず、次のデータファイルの処理に移るようにするというのが流れです。
ご回答ありがとうございました。今回ので、tan2まで読み込むことに成功しました!
ただ、テストとして、tan2までしかデータを用意しないでいるので、tan3.xlsは存在しないからか、tan2データの次の行に#REF!と入ってしまい、データの型が一致しませんとエラーが出てしまいます。ファイルが無かったら処理を終わるにはどう設定したらよいでしょうか?
No.10
- 回答日時:
>ファイルが無かったら処理を終わるにはどう設定したらよいでしょうか?
今回の場合だと本来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で得られた結果をそのまま利用できます。)
今回は、そこまでは考慮しないこととします。
この回答への補足
それと、頂いた分を挿入して実行しても、型が一致しませんとのエラーが IF ixc=1 And Cells(ixr,ixc)=0 Thenのところでやはり出てしまっていました。
この0を"0"としたところエラーが出なくなり、正常に実行できました!ありがとうございました。
ありがとうございます。おかげさまでやりたいことができました!
>本来For bkno = 1 To 2に変更するのが正しいと思いますし
それが、フォルダ内のファイル数がいつも決まった数とは限らないため、ここも変数でと思っている部分です。
別のシートにフォルダ内にあるファイル名を表示させるマクロも別に作ったりしてあって、それを並び替えて、bkmeiはそちらを参照して、順番通り呼び出そうとも考えたりしたのですが、力不足でまだ及びません。頂いたサンプルで考えてみようと思います。
とりあえず、一連の流れが整ってきましたので、(長くなってしまってすみません)この辺でポイント発行させて頂きたいと思います。
また分からないところがありましたら、ぜひよろしくお願い致します。最後までおつきあい頂きましてありがとうございました。
No.9
- 回答日時:
WWolfです。
質問等のやり取りをみていますと”rebellion”さんとの方法で解決に進んでいそうなので、このQNo.1120528では深く説明は控えさせていただきます。
Select~From文のことはSQLを調べてみれば理解できると思いますよ。
また、今後何か詰まったときに具体的な質問としてまた聞いてください。
はい、ありがとうございます!
解決までもう一歩です。
またの機会がありましたら、よろしくお願いいたします。
皆さん親切で助かります。
No.7
- 回答日時:
>tan2のデータがtan1のデータ最終行(5行目)の一行空いて下(7行目からtan2データ)に入りました。
一行空いているので6行目からにしたいです。今はIf ixc = 1 And Cells(ixr, ixc) = 0 Then が存在する状態ですね?
でしたらwix = wix + 1をNext ixrの上に移動してみてください。
>wix = wix + 1をNext ixrの上に移動
しました。すると、tan2のデータが表示されなく、tan1のデータの後6行目に#REF!と入ります。
dtend = True
の意味ってなんですか?
No.6
- 回答日時:
>その上に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のデータ最終行(5行目)の一行空いて下(7行目からtan2データ)に入りました。一行空いているので6行目からにしたいです。
wix = wix + 1をNext ixcの上に持ってくれば6行目から入りますが7行目は#REF!になり以下は空白です。(tan2は7行データがあるはず。)どうしてでしょうか?
No.5
- 回答日時:
>tan1.xlsのtan1には5行ぐらいデータが入っています。
全部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行くらいまでのデータなんでしょうか?
今はテストのためデータの少ないもので試しています。
実際はBook40ファイルくらい、シート内データ行600くらいづつになると思います。
>エラーの件ですが、
ご指示のとおりやってみましたところ、データが無いところは0が入っています。ただ、いったんtan1.xlsの正しいデータが表示された後、 その上にtan2.xlsのデータが上書きしてしまいます。tan1のデータが終わった後からtan2のデータを続けたいです。
データ型のエラーが出た原因が分かりました。
tan1.xlsの見た目に騙されていましたが開くと保存の際、ファイルの種類の初期値が.txt形式になっていました。(元々他機からインポートしてくるデータの為)コレをエクセル形式で保存し直したらエラーが出なくなりました。この場合、ファイル名は確かにtan1.xlsなのですが、中身がtan1.txtのままだとこのマクロは使えませんか?40件あまりをエクセルに変換しなくてはならなくなるなら、結局開いて貼るにした方がよいかもしれないですね。(泣)
また、Ifのところもコメント化を外して実行したところtan1はデータも表示され0が消え良好ですが、やはりtan2のデータが一行目のみに上書きされて、二行目以下には何も起こりませんでした。コレを直すにはどうしたらよいでしょうか?
No.4
- 回答日時:
>速度を重視して「開かず」と思った
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には5行ぐらいデータが入っています。
No.3
- 回答日時:
質問者さんは勉強家そうなので直ソースを書くよりヒント形式で答えたいと思います。
その方が今後質問者さんの為にもなると思いますし。まず、データ->外部データ取り込み->新しいデータベースクエリ->ExcelFiles* を選びます。
ブック選択のフォームが出てくるので指定したいファイルを選びます。
すると多分「このデータそーすには・・・」と出ると思いますがそのままOK
オプションで”システムテーブル”にチェック
すると”使用可能なテーブルと列”のリストにシートが出てきます。
後はウィザード指示に従って進んでください。
次におおよそこの機能が理解できればマクロ記録を使いそのマクロを見て研究してください。(SQLなども)
結構使えますよ!!
では、また何かわからないことがあれば・・・
ご回答ありがとうございました!
非常に興味深いですね!エクセルにこんな機能が付いているとは知りませんでした!
マクロ登録してみたらたくさんコード文ができて全部は必要ないんじゃないかなと思いましたが、一カ所
.CommandText = Array( _
"SELECT `TAN1$`.`ああああ `" & Chr(13) & "" & Chr(10) & "FROM ・・・
という行があって、ああああの部分が実際にtan1のセルデータの中身の情報が表示されていました。(タイトル列として出てるのかな)この部分の表記をエクセルデータへの加工なしに、汎用化するにはどうすればよいのでしょう?
No.2
- 回答日時:
ブック内にあるデータ形式にもよりますが、もし質問者様のエクセルが外部データ参照の機能をアドイン出来る又はインストールできるなら、それから最終を取得することもデータを取得することも出来ますよ。
ご回答ありがとうございます。
ブック内のデータは文字列です。
外部データ参照機能を追加インストールできました。
ヘルプを見た限りでは、他のアプリのデータを参照するような事を書いてありましたが、エクセル同士でも使うものなのですか?
どのように使えばよいか、よかったら教えてください。
自分でも調べてみます!
No.1
- 回答日時:
>このファイルすべてを開かずに、
なぜ開かずに作業するのでしょう?
開く方がかなり早いと思いますが?
「開く」「コピー」「加工ブックに貼付け」「閉じる」の繰り返しの方がすっきりすると思うのですが?
特に開かずにという条件を付けるのは、どういう意図なんでしょうか?
>開いていないファイルの最終行をどう取得選択して
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」
の部分でエラーが出ました。
「実行時エラー13型が一致しません」となります。
どうしてでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【マクロ】同じフォルダ内にある複数ブックから1つのブック内の1シートにデータを集めたい 6 2022/09/28 18:16
- Visual Basic(VBA) 集めたシートのシート名を変更したい。 下記のコードでサブフォルダにあるファイルのSheet3を集めて 6 2022/08/23 10:38
- Excel(エクセル) 【マクロ】マクロが保存されているエクセルとは、別のエクセルブックの全シートの非表示列を再表示したい 1 2022/12/24 20:48
- Excel(エクセル) フォルダ内の全ブックのシート名を変更したい 7 2022/09/22 21:34
- Excel(エクセル) 【マクロ】マクロが保存されているエクセルとは、別のエクセルのオートフィルターのしぼりをクリアーしたい 2 2022/12/24 08:36
- Excel(エクセル) EXCEL マクロで 同じフォルダ内の複数ファイルの複数行全体を選択して1つのファイルに集約 4 2022/09/27 18:41
- Visual Basic(VBA) 複数ファイルのデータの統合について 12 2022/05/14 12:03
- Excel(エクセル) Excel、同じフォルダ内のExcelファイルの特定シートのみを1つのファイルに集約したい 8 2022/09/07 15:12
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/08/03 11:27
- Visual Basic(VBA) VBAが止まります。 2 2022/09/02 14:02
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの指数を無効にしたい
-
複数のCSVファイルを横に並べて...
-
VBAでCSVの1行目だけを書き換え...
-
excelインポート時の「実行時エ...
-
datファイル→csvファイル→datフ...
-
複数のCSVファイルからのデータ...
-
破損したExcelファイルの内容を...
-
同じ形のCSVファイルを複数同時...
-
エクセルのcsvで、12e3を入れる...
-
膨大な量のcsvファイル(ファイ...
-
CSVファイルをエクセルに取込む...
-
CSVファイルの結合(重複データ...
-
複数のcsvファイルを1つのEXCEL...
-
エクセルファイルのコンペア
-
EXCELLの動きが遅い
-
大量のCSVデータを1つのエ...
-
Outlook2003からOutlook2007へ...
-
CSVデータから重複したデータを...
-
EXCELにcsv形式の外部データを...
-
エクセル終了時の保存確認メッ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルの指数を無効にしたい
-
複数のCSVファイルを横に並べて...
-
excelインポート時の「実行時エ...
-
大量のCSVデータを1つのエ...
-
VBAでCSVの1行目だけを書き換え...
-
複数のcsvファイルを1つのEXCEL...
-
Excel VBAを使った複数のCSVフ...
-
CSVファイルの結合(重複データ...
-
EXCELLの動きが遅い
-
「ほかのアプリケーションを無...
-
datファイル→csvファイル→datフ...
-
EXCELにcsv形式の外部データを...
-
二つのCSVファイルを照らし合わ...
-
EXCELで外部データの取り込みが...
-
【VBA初心者】同じフォルダ内の...
-
【エクセル VBA】CSVファイルの...
-
Accessにエクセルからデータを...
-
VBAで一定期間の名簿を検索、抽...
-
同じ形のCSVファイルを複数同時...
-
複数個のascファイルを1つ...
おすすめ情報