Excel ファイルを指定し、指定されたファイル内にシートを統合するVBA

Question

Excelで、ファイル内にシートを統合するVBAを書いてみたいと思うのですが、ネットなどでは同一のファイル内にVBEを書き実行するものがほとんどのようにお見受けします。
すなわち、VBAのあるファイル（マクロファイル）にシートが５枚あれば５つドラッグしてくる、１０枚あらえば１０枚、１００枚あれば１００枚ドラッグするということになります。

スタート（統合）ボタンなどを付けることを考えると、どのみちマクロ用に準備というか統合用のシートは必要なので、そこでパスとファイル名を指定すれば、当該Excelファイル内のシートを全部統合してくれるというVBAが理想的なのですが、どなたかそういうコードをご教授いただけないでしょうか。

よろしくお願い申し上げます。

tatsumaru77 · Accepted Answer

No6,7です。
以下のマクロを標準モジュールに登録してください。
Option Explicit

Public Sub シート統合()
    Dim ws As Worksheet
    Dim maxrow As Long
    Dim wrow As Long
    Set ws = Worksheets("統合管理")
    maxrow = ws.Cells((Rows.Count), 1).End(xlUp).Row
    For wrow = 2 To maxrow
        Call bind_sheets(ws.Cells(wrow, "A").Value, ws.Cells(wrow, "B").Value)
    Next
    MsgBox ("完了")
End Sub
Private Sub bind_sheets(ByVal folder_name As String, ByVal book_name As String)
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Long
    Dim maxrow As Long
    Dim maxcol As Long
    Dim row2 As Long
    Dim pos As Long
    Dim newname As String
    Set wb1 = Workbooks.Open(folder_name & "\" & book_name)
    Set wb2 = Workbooks.Add
    Set ws2 = wb2.Worksheets(1)
    ws2.Name = "統合"
    row2 = 1
    For i = 1 To wb1.Worksheets.Count
        Set ws1 = wb1.Worksheets(i)
        maxrow = ws1.Cells((Rows.Count), 1).End(xlUp).Row
        maxcol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
        ws2.Cells(row2, 1).Resize(maxrow, maxcol).Value = ws1.Cells(1, 1).Resize(maxrow, maxcol).Value
        row2 = row2 + maxrow
    Next
    pos = InStrRev(book_name, ".")
    newname = "案件" & left(book_name, pos - 1) & "統合" & right(book_name, Len(book_name) - pos + 1)
    Application.DisplayAlerts = False
    wb2.SaveAs (folder_name & "\" & newname)
    Application.DisplayAlerts = True
    wb2.Close
    wb1.Close
End Sub

添付資料のような状態で定義されているものとします。
①シート名：統合管理
②A列　フォルダ名
③B列　ブック名

ブックを格納しているフォルダの中に統合したブックが作成されます。
例では、
d:\goo\data下に案件○○統合.xlsx
d:\goo\data2下に案件△△統合.xlsx
が作成されます。

統合元の各シートの最大行数はA列の行数で決定しています。
統合元の各シートの最大列数は１行の列数で決定しています。

tatsumaru77 · Answer

No6です。
補足ありがとうございました。
以下のようにされたいということでしょうか。

A.xlsx内にSheet1,Sheet2,Sheet3
B.xlsx内にSheet1,Sheet2,Sheet3
があった場合、
①A.xlsx内のSheet1,Sheet2,Sheet3をシート名：統合　にまとめ
案件A統合.xlsxの名称で保存する。
②B.xlsx内のSheet1,Sheet2,Sheet3をシート名：統合　にまとめ
案件B統合.xlsxの名称で保存する。

①の場合、A.xlsx内のSheet1,Sheet2,Sheet3は全て同じレイアウトであることが前提になります。（レイアウトが異なるものを統合できなくはないが、統合する意味がないです）
同じレイアウトの場合、見出し行は、最初のSheet1だけ付加し、Sheet2,Sheet3はデータ行のみを統合するのが通常の考えかたです。
そうすると、見出し行が何行あり、データ行は何行から開始しているのか
の情報も必要になります。
１つのファイル内の各シートが同じレイアウトだとしても、見出し行に関する情報は、ファイルごとに異なるのではないでしょうか。
そうすると、そのような情報も、統合用のシートには必要ではないでしょうか。
現在、統合用のシートで提示されているのは、以下の情報のみです。
１．パス名（例　c:\data）
２．ファイル名(例　A.xlsx)

tatsumaru77 · Answer

>「スタート（統合）ボタンなどを付けることを考えると、どのみちマクロ用に準備というか統合用のシートは必要なので、そこでパスとファイル名を指定すれば、当該Excelファイル内のシートを全部統合してくれる」

具体的にどのようなレイアウトになりますか。正確なレイアウトが判る画像を提示していただけませんでしょうか。

また、統合時にシート名はどうするのでしょうか。
例として
A.xlsx内にSheet1,Sheet2,Sheet3
B.xlsx内にSheet1,Sheet2,Sheet3
があったとき、
統合先のシート名は、どのようにしたいのでしょうか。

初心者ですが何か。 · Answer

http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_consolidate.html

せめてどのようなデータ群になっている物をどのように纏めたいのかは欲しいかも。
で、実際５～１００シートと幅が広いのでしょうか？

rexfan · Answer

少量であれば、わざわざパスとファイル名をシートに指定しなくても
シートを統合したいエクセルをActiveにしておいてＶＢＡを実行すれば
出来ます。

fujillin · Answer

こんにちは

＞同一のファイル内にVBEを書き実行するものがほとんどのようにお見受けします。
他のブックの処理をしたいということと解釈しました。

多分、ご覧になっているコードではセル範囲を指定するのに
　Worksheets(ｎ).Range(～～)
のような記述を用いているのではないかと想像します。

実はこれは、
　Workbook("book名").Worksheets(ｎ).Range(～～)
を省略した描き方で、ブックを省略した場合には、ActiveWorkbookと見做されます。
シートオブジェクトも省略して
　Range(～～)
と記述すれば、ActiveSheetのセル範囲と見做されるという仕組みになっています。
つまり、他のブックを操作したいときはブックオブジェクトを省略せずに指定してあげれば良いということです。

コードにいちいちブックオブジェクトを記述していると長くなりがちなので、With構文を利用して省略するという方法もあります。
https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/with-statement

実際には、他のブックが閉じている状態であれば、対象ブックを開く処理から記述する必要があります。
以下のサイトに、「複数ブックの、特定のシートを統合する」例の説明がありますので、「他のブックを操作する」ことに関する述方法のご参考になると思います。
https://www.moug.net/tech/exvba/0060003.html

上記はご質問の内容とは少し異なる内容ですが、ブック内のシートを統合するものは既にお持ちでしょうから、それを、同じ内容のまま「他ブックを操作」できるように修正することで、ご質問の内容を実現できるものと思います。

銀鱗 · Answer

＞そんな風に読めましたか？

ごめんね。

＞＞どなたかそういうコードをご教授いただけないでしょうか。

これがそう見えちゃうんだな。

・・・本題・・・

＞VBAの書かれているファイルにシートを持ってこないと、統合できないかと思うので質問した次第です。

ええと……「マクロを置く場所」について調べると解決するような気がするんです。
たぶんこの知識が不足していることから生じている疑問と思います。
チョット遠回りかもしれませんが、自身で調べることで理解を深めることができます。（いじわるじゃないんです）
もう少しだけ調べてみましょう。

銀鱗 · Answer

ファイル名を取得する方法を調べてみましたか。
普通にインターネット上にありますよ。
そういったものを参考にしましょう。
そのうえでそのコードが理解できないのであれば、解説を求める質問をすると良いと思います。

・・・

(´・ω・`) ここは「代わりにマクロを作れ！俺様の成果として使ってやるｗｗｗ」のように作業依頼をする場所ではありません。
自力でマクロを作れるようになるアドバイスを貰う場所です。
（勘違いしている人がいるので真似しないようにしましょう）

Excel ファイルを指定し、指定されたファイル内にシートを統合するVBA

No6,7です。

No6です。

>「スタート（統合）ボタンなどを付けることを考えると、どのみちマクロ用に準備というか統合用のシートは必要なので、そこでパスとファイル名を指定すれば、当該Excelファイル内のシートを全部統合してくれる」

少量であれば、わざわざパスとファイル名をシートに指定しなくても

こんにちは

＞そんな風に読めましたか？

ファイル名を取得する方法を調べてみましたか。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング