エクセル 全部のシートを自動的に読み込みたい

質問

どなたが助けてください。

エクセルのファイルが一つのフォルダの中に
沢山あります。（ＣＳＶの場合もあります)

各ファイルには複数のシートが入っていて
シート名はバラバラです。

各シートのに入っているデータは
縦は５００行目まで、横は端から端まで（Ａ列→IV列)です。

この沢山のエクセルファイルの全シートを
一つのシートに自動的に読み込んで
縦てに並べたいのです。

１ファイルの中にある全シート（１枚しかシートがない
ものもあれば５枚あるものもあり もれなく全部)
の５００行目までを
別の新しいエクセルファイルのシートに縦に並べて
次のファイルをまた開いて全シートを
先ほど並べたデータの下にまた並べて・・・・と
繰り返して一つのフォルダの中にあるエクセルファイルの全シート重複せずに並べるような
マクロをどなたか教えて頂けないでしょうか？

ファイル数が大変多く、本当に困っています。
よろしくお願いいたします。

回答No.9

Hayashi_Trek · ベストアンサー

For row = 1 To 500　　　' 最大500行まで検索
を修正して転送する行の範囲を変える。　または
If IsEmpty(srcSt.Cells(row, 1)) Then Exit For
の行を削除してA列が空でも転送するようにするか。

どちらが良いかは、実際のシートを見ているあなたにしか判りません。

お礼

ありがとうございました！
無事に対応することができました。
何度も丁寧に教えていただき
本当に感謝しております。

ありがとうございましたm(__)m

回答No.8

Hayashi_Trek

どのように実行してますか？
ツール→マクロ→マクロ　でSyuuyakuを選んで　実行ボタン
または
Visual Basic Editor の画面で、カーソルをSyuuyakuサブルーチンの中に置いてF5キー

またこのコードは、各シートの１行目から順にデータを転送する。
A列が空の行が有るとそこで転送を終了して次のシートに処理を移す。
ので、各シートのA1が空なら何もしません。

ブレークポイントを　（※F9キーで設定できる）
Workbooks.Open Folder & myFile
For row = 1 To 500
For col = 1 To 256
の各行に設定してマクロを実行して、
ブレークポイントの所まで来るか確認してみてください。
およびmyFileに入っている値の確認。

補足

ありがとうございます！

対象エクセルのシート、
A1が空でした、無条件で（A1が空白でも何か入っていても)読み取ってくれるようにするには
どうしたら良いですか？

もしご面倒でなければ是非教えてください。
よろしくお願いいたします。

回答No.7

Hayashi_Trek

ツール→マクロ→セキュリティで
セキュリティレベルが（高）になってませんか？

（中）または（低）にしてみてください。

補足

中　になっていましたので
低　にして　実行してみたのですが
まったく動きません。

エラー等のメッセージが出るわけでもなく
画面上　全く変化ない状態です。

回答No.6

Hayashi_Trek

検索するフォルダとは別の場所にエクセルファイルを作成し、
そこのThisWorkbookに下記コードを貼り付けて実行してきてください。
検索するフォルダは、変数Folderに設定します。
エクセルファイルのみ検索します。（csvは無視）
インデントのために全角スペース使ってます。（要置換）

Public Sub Syuuyaku()
　　Dim myFile As String
　　Dim Folder As String
　　Dim srcWb As Workbook
　　Dim srcSt As Worksheet
　　Dim dstSt As Worksheet
　　Dim dstRow As Long
　　Dim row As Long
　　Dim col As Long

　　Set dstSt = ThisWorkbook.Sheets(1)
　　dstRow = 0

　　Folder = "D:\Test\"
　　myFile = Dir(Folder & "*.xls")
　　Do While myFile <> ""
　　　　Workbooks.Open Folder & myFile
　　　　Set srcWb = ActiveWorkbook
　　　　For Each srcSt In srcWb.Worksheets
　　　　　　For row = 1 To 500　　　' 最大500行まで検索
　　　　　　　　If IsEmpty(srcSt.Cells(row, 1)) Then Exit For
　　　　　　　　dstRow = dstRow + 1
　　　　　　　　For col = 1 To 256　' 全列
　　　　　　　　　　dstSt.Cells(dstRow, col).Value = srcSt.Cells(row, col).Value
　　　　　　　　Next col
　　　　　　Next row
　　　　Next
　　　　Set srcSt = Nothing
　　　　srcWb.Close False
　　　　Set srcWb = Nothing
　　　　myFile = Dir
　　Loop
End Sub

補足

ありがとうございます！
実際、不慣れなのでアドバイスいただいたように
貼り付けてやってみたのですが
何も動きませんでした、すみませんが
もうすこし詳しく教えていただけませんか？

＞検索するフォルダとは別の場所にエクセルファイルを作成し、
＞そこのThisWorkbookに下記コードを貼り付けて実行してきてください。

→エクセルを別場所に作成しThisWorkbookに
コード部分をコピペしました。

＞検索するフォルダは、変数Folderに設定します。
→Folder = "D:\Test\"　この""の間のパスを
実際のフォルダのあるパスに書き換えました。

＞インデントのために全角スペース使ってます。（要置換）
→置き換えで全角スペースを全て半角スペースに置き換えました。

あと何をしたら動くようになるのでしょうか？
無知ですみません、ここから前に進めませんm(__)m
よろしくお願いいたします。

回答No.5

norakuma

あ、しまった。
それやると、シート１が消えます。
シート１を追加してください。やる前に。

bookの読み込みについては、本日は時間切れ…。すんません。

補足

norakumaさま

ありがとうございます、
下に書いていただいたコード、そのままエクセルVBAに
貼り付けてみたのですが
やっぱり動きません。
何か私の設定が悪いのでしょうか？
また明日？NORAKUMAさまお待ちしておりますm(__)m

回答No.4

norakuma

＞１ファイルに数シートあった場合全部のシートを読み出したいと思いました。

これだけなら
Sub test1()
Dim SheetCount As Integer
Dim i As Integer
SheetCount = ActiveWorkbook.Worksheets.Count

For i = 2 To SheetCount
Worksheets(i).Select
Range("A1:A500").CurrentRegion.Select
Selection.Copy
Worksheets(1).Select
If i = 2 Then
Range("A1").Select
Else
Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(1).Select

End If
ActiveSheet.Paste
Next i
End Sub

回答No.3

norakuma

こんにちは。

エクセルブックのシートを読む原理は簡単ですよ。
1.シート数をカウント。
ActiveWorkbook.Worksheets.Count

2.最後のシートまで順に読み込み、新しいシートに縦に並べてと
やっていけばOKです。

このとき、シート名を指定するのではなくシートの順番で読み込めば
シート名に左右されずに済みます。

フォルダ内のファイル読み込みについては参考URLが参考になるのではないか？と。

参考URL：

http://www.accessclub.jp/bbs3/0186/superbeg61242.html

補足

ありがとうございます！
参考URLにあったコードを入れて動かしてみたのですが
オペレートする側がEXCELではなくACCESSのようで
実際エクセルのVBAに貼り付けてみましたが
うまく動きませんでした。

もしよろしければコードを教えて頂けないでしょうか？
よろしくお願いいたします。

回答No.2

danchor

なにをなさりたいのかよく見えませんが、どうもエクセルの世界でやる範疇ではないようですね。

もっとよく考え方を整理してデータベース化に進まれては?

補足

エクセルのVBAマクロで
ファイルのデータを読み込むマクロを持っているのですが
それは１ファイルに１シートしかないエクセルファイルを読み込めるものだったので
１ファイルに数シートあった場合
全部のシートを読み出したいと思いました。

もしVBAマクロでの処理をご存知でしたら教えて頂けませんか？
よろしくお願いいたします。

回答No.1

TK1961

まず、ひとつのシートの限界は65,536行ですが、
この文章を読む限り、簡単に越えてしまいそうなのですが・・・？

　ご質問の条件では、１ファイルのシートが平均３枚として４３ファイル。２枚でも６６ファイルで、限界が来てしまいますね。

補足

ありがとうございます。
確かにシート限界は知っています。
なので限界ギリギリのファイル数しか
フォルダには入れないつもりです。

もしVBAマクロで対応出来るのをご存知でしたら
教えて頂けないでしょうか？
よろしくお願いいたします。