複数のエクセルファイルを1つのブックにまとめるVBA【 設定方法を紹介】

 

 

こんな疑問にお答えします。

 

結論から言うと、「他のエクセルファイルを開く・シートをコピーする・元のエクセルファイルに貼り付ける」処理をするマクロを導入する！です。

 

VBAを使えば、エクセルファイル数やシート数がいくつあっても一瞬で１つのエクセルファイルにデータを集約することができます。

 

このような悩みを抱えている方は必見です。

複数のエクセルファイルを1つのブックにまとめるVBAは上記のような悩みを解決することができます。

 

私の職場では、シートをコピーして別のブックに貼り付ける！業務を何時間もかけて手作業で処理をしていましたが、

VBAを導入してからは完全自動化。

 

 

今まで当たり前と思い込んでいた作業がとんでもなく効率の悪い方法だったと全員が認識し、

今ではシートのコピペ作業はVBAを使って一瞬で処理することが当たり前になっています。

 

この記事では、複数のエクセルファイルを１つのまとめるVBAの特徴、導入方法について詳しくご紹介しています。

コピペして使えるサンプルVBAを掲載しているので是非使ってみてください。

 

複数のエクセルファイルを1つのブックにまとめるVBAとは？

 

エクセルファイルを１つにまとめるとは、指定したブックのシートをコピーして、１つのブックに集約する！ということです。

 

VBAが設定されたエクセルファイルがこちら

 

「設定」シートに、「実行ボタン」と「エクセルファイルの保存場所を指定する一覧」が表示されています。

 

「実行ボタン」でVBAを実行した結果がこちら

 

超過勤務整理表(伊藤博文).xlsmというエクセルファイルに保存されている情報を一瞬で集約することができます。

 

 

VBAの流れは以下のようになります。

大きく分けて５つの項目で構成されています。

 

完成したVBAの動きを見ると複雑で難しいと感じるかもしれませんが、項目を分けて考えてみると複雑なVBAは一切使われていません。

 

 

今回紹介する複数のエクセルファイルを一つにまとめるVBAには2つの種類があります。

 

１つのブックを指定してシートを集約するVBAの解説

１つのブックを指定してシートを集約するVBAとはどういうものかをご紹介していきます。

下記の動画では、１つのブックを指定してシートを集約するVBAの動作を確認することができます。

 

指定したブックにある全てのシートをVBAが設定されたブックに集約することができます。

 

｢ブックを開く → シートをコピー → VBAが設定されたブックに貼り付け｣といった単純作業を繰り返しを行う。

 

１つのブックを指定してシートを集約するVBAの導入方法

１つのブックを指定してシートを集約するVBAの導入方法を詳しく説明していきます。

手順通り設定すればVBAを使ってシートを集約することができるはずです。是非試してみてください。

 

導入方法① シートの設定

 

上図のように｢設定｣シートを作成します。

 

B3セル～B20セルの背景色を白、それ以外は黄色に設定。

これは、「ブックの場所を記載するセルがB3セル～B20セルである」ということをわかりやすくするためです。

 

今回はB3セル～B20セルの情報を読み取るプログラムになっているので、ブックの場所を記載するセルを勝手に変更しないでください。

 

シートの名前を「設定」にしてください。

シートを区別するVBAが設定されているので、シート名を「設定」にしなければ正常に動作しません。

 

シートを区別するVBAってなに？どうやって設定するの？

と疑問の方は下記のシート名を区別するVBAについて解説されていつ記事を参考にしてください。

 

 

 

 

導入方法② VBAの作成

下記で紹介しているサンプルVBAを標準モジュールにそのまま貼り付けてください。

 

これでボタンにマクロの登録をするとき、｢テスト｣というマクロが表示されるようになります。

 

導入方法③ ボタンの作成

「ボタン」は「開発」タブの中にあります。

 

VBAを導入する場合「ボタン」コマンドの使用頻度が高いので、私はショートカットを作成していつでも使えるようにしています。

 

下記の記事では、使用頻度の高いコマンドのショートカットの作り方について詳しく解説しています。

 

 

ボタンを作り、｢右クリック｣ → ｢マクロの登録｣ → ｢テスト｣ → ｢OK｣で、

ブックを指定してシートを集約するVBAをボタンに設定することができます。

 

 

表示している「ボタン」のテキストを「実行」に変更する見栄えがよくなります。

｢ボタン｣の上で､｢右クリック｣→｢テキストの編集｣でテキストを変更することが可能です。

 

 

１つのブックを指定してシートを集約するVBAは下記のサンプルVBAをコピーしてすぐに使うことができます。

 

サンプルVBA(１つのブックを指定してシートを集約するVBA)

Sub テスト()
Application.ScreenUpdating = False
Dim Abook As Workbook
Set Abook = ActiveWorkbook
Set Ash = Abook.Worksheets("設定")
For k = 3 To 20
Dim exfile As String
If Ash.Cells(k, 2) = "" Then
Exit Sub
Else
exfile = Ash.Cells(k, 2)
End If
Workbooks.Open Filename:=exfile
If Err.Number > 0 Then
MsgBox "ファイルはすでに開かれています。"
Exit Sub
End If
Dim Bbook As Workbook
Set Bbook = ActiveWorkbook
For i = 1 To Bbook.Worksheets.Count
Bbook.Worksheets(i).Copy After:=Abook.ActiveSheet
Abook.ActiveSheet.Name = Abook.ActiveSheet.Name & "(" & Bbook.Name & ")"
Next i
Bbook.Close
Next k
End Sub

 

上記サンプルVBAでは、他のエクセルファイルを操作するVBAが使われています。

このVBAに関してもっと詳しく知りたい！という方は下記の記事を参考にしてください。

 

 

 

１つのブックを指定してシートを集約するVBAの使い方

 

使い方① ブックの指定

 

上図のようにB3セル～B20セルに、集約したいシートが保存されているブックの保存場所を記入してください。

最後に記入するブックの名前には、拡張子までしっかり記載することに注意が必要です。

 

使い方② 実行ボタンをクリック

 

ブックを指定してシートを集約するVBAが登録されたボタンをクリックしてください。

 

 

実行結果(ブックを指定してシートを集約するVBA)

 

ブックを指定してシートを集約するVBAを実行すると上図のようにシートが自動的に作成されます。

 

このVBAを使って約50シート分の集約を行ったとき、

VBAの処理が完了するまで1分程度時間がかかりましたが問題なく集約することができました。

 

下記のサイトでは、ブックを指定してシートを集約するVBAが設定されたエクセルファイルをダウンロードしてお使いいただくことができいます。

設定が難しい！すぐにVBAを使ってみたい！という方は是非サイトを参考にしてみてください。

 

ブックが保存されているフォルダを指定してシートを集約するVBAの解説

ブックが保存されているフォルダを指定してシートを集約するVBAとはどういうものかをご紹介していきます。

 

下記の動画ではVBAの動作を確認することができます。

 

ブックが保存されているフォルダを指定してシートを集約するVBAは、上記で紹介しているブックを指定してシートを集約するVBAと処理内容はほとんど変わりません。

 

違う点は、

フォルダ内の全てのブックを指定することができるので、いっきにシートを集約することが可能

ということです。

 

 

ブックが保存されているフォルダを指定してシートを集約するVBAの導入方法

導入方法① シートの設定

 

上図のように｢設定｣シートを作成します。

 

B3セル～B20セルの背景色を白、それ以外は黄色に設定。

これは、フォルダの場所を記載するセルがB3セル～B20セルであるということをわかりやすくするためです。

 

フォルダを指定してブック内のシートを集約するVBAはB3セル～B20セルの情報を読み取る設定になっているので、

フォルダの場所を記載するセルを勝手に変更してはいけません。

 

シートの名前を「設定」にしてください。

シートを区別するVBAが設定されているためシート名を「設定」にしなければ正常に動作しません。

 

導入方法② VBAの作成

下記で紹介しているサンプルVBAを標準モジュールにそのまま貼り付けてください。

 

これでボタンにマクロの登録をするとき、｢テスト｣というマクロが表示されるようになります。

 

導入方法③ ボタンの作成

「ボタン」は「開発」タブの中にあります。

 

ボタンを作り、｢右クリック｣ → ｢マクロの登録｣ → ｢テスト｣ → ｢OK｣で

ブックが保存されているフォルダを指定してシートを集約するVBAをボタンに設定することができます。

 

 

ブックが保存されているフォルダを指定してシートを集約するVBAは、下記のサンプルVBAをコピーしてすぐに使うことができます。

 

サンプルVBA(ブックが保存されているフォルダを指定してシートを集約するVBA)

Sub テスト()
Application.ScreenUpdating = False
Dim Abook As Workbook
Set Abook = ActiveWorkbook
Dim Ash As Worksheet
Set Ash = Abook.Worksheets("設定")
For k = 3 To 20
Dim exfile, exname As String
If Ash.Cells(k, 2) = "" Then
Exit Sub
Else
exfile = Ash.Cells(k, 2)
End If
exname = Dir(exfile & "\*.xlsm*")
Do Until exname = ""
Workbooks.Open Filename:=exfile & "\" & exname
If Err.Number > 0 Then
MsgBox "ファイルはすでに開かれています。"
Exit Sub
End If
Dim Bbook As Workbook
Set Bbook = ActiveWorkbook
For i = 1 To Bbook.Worksheets.Count
Bbook.Worksheets(i).Copy After:=Abook.ActiveSheet
Abook.ActiveSheet.Name = Abook.ActiveSheet.Name & "(" & Bbook.Name & ")"
Next i
Bbook.Close
exname = Dir
Loop
Next k
End Sub

 

14行目は関数Dirを使ってフォルダ内のデータを取得しています。

14行目の意味は、変数exfileで指定されたフォルダ内の.xlsmが含まれるデータ全てを取得する！というもの。

拡張子.xlsx形式のエクセルファイルを取得するには14行目の .xlsm を .xlsx に変更する必要があります。

 

また14行目の .xlsm を .xl に変更すると、全てのエクセルファイルのデータを取得することが可能です。

取得するファイル形式に合った拡張子に編集して試してみてください。

 

15行目から29行目はDo〜Loopによって関数Dirでヒットした全てのエクセルファイル名を取得する！というものです。

Do～Loopと関数Dirを組み合わせることによって、フォルダ内の全ファイル名を取得することができます。

この方法は他にもいろんなことに応用することができるので是非使い方をマスターしておきましょう。

 

 

ブックが保存されているフォルダを指定してシートを集約するVBAの使い方

使い方① フォルダの指定

 

上図のようにB3セル～B20セルに、集約したいシートが保存されているフォルダの保存場所を記入します。

｢サンプル｣と｢サンプル2｣という2つのフォルダに保存されている全ブックの全シートをまとめて集約することができます。

 

 

使い方② 実行ボタンをクリック

 

ブックが保存されているフォルダを指定してシートを集約するVBAが登録されたボタンをクリックしてください。

 

 

実行結果(ブックが保存されているフォルダを指定してシートを集約するVBA)

 

ボタンをクリックして、VBAを実行すると上図のようにシートが自動的に作成されます。

 

このVBAでは、知らず知らずのうちに大量のシートを集約する処理を実行してしまう恐れがあります。

指定するフォルダ内にブックがどれだけ保存されているか確認してから実行するように注意しましょう。

 

下記のサイトでは、フォルダ内のブック情報を集約するVBAが設定されたエクセルファイルをダウンロードしてお使いいただくことができいます。

設定が難しい！すぐにVBAを使ってみたい！という方は是非サイトを参考にしてみてください。

 

複数のブックをまとめるマクロは仕事でかなり使える！

複数のブックを一つにまとめるVBAを紹介してきました。

やり方をマスターをすればサーバー内にあるあらゆるエクセルファイルを一瞬で編集できるようになります。

このマクロは実際の業務でかなり使えるマクロなので自由に扱えるようにしておきましょう。

 

下記の記事では、VBA参考書の選び方やポイントについて詳しく紹介しています。

別シートに情報を自動反映するエクセルマクロもVBA参考書を使って開発したものです。

 

 

仕事の効率アップにつながるマクロを開発してみたい！と考えている方は是非参考書で読んでみて下さい。

あなたの考えを表現するためのVBAがきっと見つかると思います。

 

参考書では、シートから別のブックに情報を自動反映させるマクロの作り方も掲載されています。

 

下記の記事では、他のブックを操作するマクロについて詳しく紹介しています。

今回紹介した複数のブックを一つにまとめるVBAと組み合わせることで、エクセル作業の効率化に繋がるマクロをどんどん開発することができるようになります。

 

 

エクセル作業の効率化マクロを導入することで、ミスが激減し大幅な作業時間の削減に繋がります。

エクセルVBAの導入を検討している方は参考にしてみてください。

 

これからVBAの勉強をはじめるには

複数のエクセルファイルを一つにまとめるVBAのように、VBAには仕事の効率をアップさせることができる機能がいろいろあります。

VBAのプログラミングスキルを身に着けることで、今まで何時間もかかかっていた作業をほんの数分で処理することができるようになります。

 

VBAを基本から学びたい！という方におすすめなのが、オンラインプログラミング学習サービス【1st Step】です。

【1st Step】は初心者のためのプログラミング学習サービスで、C言語・java・VBAの３つのプログラミングの基礎をオンラインで勉強することができます。

上記のように考えている方に【1st Step】はおすすめです。

下記の記事では、オンラインプログラミング学習サービス【1st Step】の特徴や私の体験など詳しく紹介しています。

 

 

これからVBAの勉強を始めようと考えている方は是非参考にしてみてください。