複数のエクセルファイルを1つのブックにまとめるVBA【 コピーして使えるサンプル付 】
疑問がある人
こんな疑問にお答えします。
結論から言うと、他のエクセルファイルを開く・シートをコピーする・元のエクセルファイルに貼り付けるという処理をするVBAを導入するです。
この処理をするVBAを使えば、エクセルファイルがいくつあっても一瞬で１つのエクセルファイルにデータを集約することができます。
私の職場では、シートをコピーして別のブックに貼り付ける！という作業を定期的に行っていました。内容的に簡単な作業で昔から決められた業務だったので、全員がなんとなく行っているという状況でした。
作業する人
開発したVBAを導入すると、今まで当たり前と思い込んでいた作業がとんどでもなく効率の悪い方法だったと全員が認識することに！今ではシートのコピペ作業はVBAを使って一瞬で処理することが当たり前になっています。
この記事では、複数のエクセルファイルを１つのまとめるVBAの特徴、導入方法について詳しくご紹介しています。コピペして使えるサンプルVBAを掲載しているので是非使ってみてください。
目次
複数のエクセルファイルを1つのブックにまとめるVBAとは？
エクセルファイルを１つにまとめるとは、指定したブックのシートをコピーして、１つのブックに集約する！ということです。
エクセルファイルを１つにまとめるVBAの流れ
- 指定したブックを開く
- ブック内の全シートをコピー
- VBAが設定されているブックに張り付け
- 指定したブックを閉じる
- 1～4の繰り返し
大きく分けて５つの工程で作らています。完成したVBAの動きを見ると複雑で難しいと感じるかと思います。しかし、工程を分けて考えてみると複雑なVBAは一切使われていません。
ひらめく人
今回紹介する複数のエクセルファイルを一つにまとめるVBAには2つの種類があります。
１つ目・・・１つのブックを指定してシートを集約するVBA
→ピンポイントで情報を集約できる
２つ目・・・フォルダを指定してフォルダ内にある全てのブックを集約するVBA
→まとめて多くの情報を集約できる
ひらめく人
１つのブックを指定してシートを集約するVBAとは
１つのブックを指定してシートを集約するVBAとはどういうものかをご紹介していきます。下記の動画では実際のVBAの動きを確認することができます。
指定したブックにある全てのシートをVBAが設定されたブックに集約することができます。｢ブックを開く → シートをコピー → VBAが設定されたブックに貼り付け｣といった単純作業を繰り返し行います。
コピペするシートが一つや二つの場合、時間をかけてVBAを設定する必要はありませんが、コピペしたいシートの数が多くなるとVBAを使って自動化してしまうのがおすすめです。一度VBAを設定すると今後ずっと使い続けることができるので、シートにコピペ作業が多い！という方におすすめのVBAです。
１つのブックを指定してシートを集約するVBAの導入方法
導入方法① シートの設定
上図のように｢設定｣シートを作成します。
B3セル～B20セルの背景色を白、それ以外は黄色にしています。これは、「ブックの場所を記載するセルがB3セル～B20セルである」ということをわかりやすくするためです。ブックを指定してシートを集約するVBAにはB3セル～B20セルの情報を読み取るプログラムになっているので、ブックの場所を記載するセルを勝手に変更してはいけません。
シートの名前を「設定」にしてください。シートを区別するVBAが設定されているためシート名を「設定」にしなければ正常に動作しません。
導入方法② VBAの作成
下記で紹介しているサンプルVBAを標準モジュールにそのまま貼り付けてください。
これでボタンにマクロの登録をするとき、｢テスト｣というマクロが表示されるようになります。
導入方法③ ボタンの作成
「ボタン」は「開発」タブの中にあります。
VBAを導入する場合「ボタン」コマンドの使用頻度が高いので、私はショートカットを作成していつでも使えるようにしています。使用頻度の高いコマンドのショートカットの作り方は下記の記事で詳しく紹介しています。
ボタンを作り、｢右クリック｣ → ｢マクロの登録｣ → ｢テスト｣ → ｢OK｣でブックを指定してシートを集約するVBAをボタンに設定することができます。
表示している「ボタン」のテキストを「実行」に変更します。｢ボタン｣の上で､｢右クリック｣→｢テキストの編集｣でテキストを変更することができます。
１つのブックを指定してシートを集約するVBAは下記のサンプルVBAでコピーしてすぐに使うことができます。是非試してみてください。
サンプルVBA(１つのブックを指定してシートを集約するVBA)
| 1 | Sub テスト() |
| 2 | Application.ScreenUpdating = False |
| 3 | Dim Abook As Workbook |
| 4 | Set Abook = ActiveWorkbook |
| 5 | Set Ash = Abook.Worksheets(“設定") |
| 6 | For k = 3 To 20 |
| 7 | Dim exfile As String |
| 8 | If Ash.Cells(k, 2) = “" Then |
| 9 | Exit Sub |
| 10 | Else |
| 11 | exfile = Ash.Cells(k, 2) |
| 12 | End If |
| 13 | Workbooks.Open Filename:=exfile |
| 14 | If Err.Number > 0 Then |
| 15 | MsgBox “ファイルはすでに開かれています。" |
| 16 | Exit Sub |
| 17 | End If |
| 18 | Dim Bbook As Workbook |
| 19 | Set Bbook = ActiveWorkbook |
| 20 | For i = 1 To Bbook.Worksheets.Count |
| 21 | Bbook.Worksheets(i).Copy After:=Abook.ActiveSheet |
| 22 | Abook.ActiveSheet.Name = Abook.ActiveSheet.Name & “(" & Bbook.Name & “)" |
| 23 | Next i |
| 24 | Bbook.Close |
| 25 | Next k |
| 26 | End Sub |
上記サンプルVBAでは、他のエクセルファイルを操作するVBAが使われています。このVBAに関してもっと詳しく知りたい！という方は下記の記事を参考にしてください。
ガッツポーズの人
１つのブックを指定してシートを集約するVBAの使い方
使い方① ブックの指定
上図のようにB3セル～B20セルに、集約したいシートが保存されているブックの保存場所を記入します。最後に記入するブックの名前には、拡張子までしっかり記載することに注意が必要です。
使い方② 実行ボタンをクリック
ブックを指定してシートを集約するVBAが登録されたボタンをクリックしてください。
ガッツポーズの人
実行結果(ブックを指定してシートを集約するVBA)
ボタンをクリックして、ブックを指定してシートを集約するVBAを実行すると上図のようにシートが自動的に作成されます。
私は、このVBAを使って約50シート分の集約を行ったことがあります。VBAの処理が完了するまで1分程度時間がかかりましたが、問題なく集約することができました。多くのシートをコピペする作業をしている方におすすめのVBAです。
ブックが保存されているフォルダを指定してシートを集約するVBAとは
ブックが保存されているフォルダを指定してシートを集約するVBAとはどういうものかをご紹介していきます。下記の動画では実際のVBAの動きを確認することができます。
ブックが保存されているフォルダを指定してシートを集約するVBAは、上記で紹介しているブックを指定してシートを集約するVBAと処理内容はほとんど変わりません。
違う点は、フォルダ内の全てのブックを指定することができるので、いっきにシートを集約することが可能！ということです。
ガッツポーズの人
ブックが保存されているフォルダを指定してシートを集約するVBAの導入方法
導入方法① シートの設定
上図のように｢設定｣シートを作成します。
B3セル～B20セルの背景色を白、それ以外は黄色にしています。これは、「フォルダの場所を記載するセルがB3セル～B20セルである」ということをわかりやすくするためです。フォルダを指定してブック内のシートを集約するVBAにはB3セル～B20セルの情報を読み取るプログラムになっているので、ブックの場所を記載するセルを勝手に変更してはいけません。
シートの名前を「設定」にしてください。シートを区別するVBAが設定されているためシート名を「設定」にしなければ正常に動作しません。
導入方法② VBAの作成
下記で紹介しているサンプルVBAを標準モジュールにそのまま貼り付けてください。
これでボタンにマクロの登録をするとき、｢テスト｣というマクロが表示されるようになります。
導入方法③ ボタンの作成
「ボタン」は「開発」タブの中にあります。
ボタンを作り、｢右クリック｣ → ｢マクロの登録｣ → ｢テスト｣ → ｢OK｣でブックが保存されているフォルダを指定してシートを集約するVBAをボタンに設定することができます。
ブックが保存されているフォルダを指定してシートを集約するVBAは下記のサンプルVBAでコピーしてすぐに使うことができます。是非試してみてください。
サンプルVBA(ブックが保存されているフォルダを指定してシートを集約するVBA)
| 1 | Sub テスト() |
| 2 | Application.ScreenUpdating = False |
| 3 | Dim Abook As Workbook |
| 4 | Set Abook = ActiveWorkbook |
| 5 | Dim Ash As Worksheet |
| 6 | Set Ash = Abook.Worksheets(“設定") |
| 7 | For k = 3 To 20 |
| 8 | Dim exfile, exname As String |
| 9 | If Ash.Cells(k, 2) = “" Then |
| 10 | Exit Sub |
| 11 | Else |
| 12 | exfile = Ash.Cells(k, 2) |
| 13 | End If |
| 14 | exname = Dir(exfile & “\*.xlsm*") |
| 15 | Do Until exname = “" |
| 16 | Workbooks.Open Filename:=exfile & “\" & exname |
| 17 | If Err.Number > 0 Then |
| 18 | MsgBox “ファイルはすでに開かれています。" |
| 19 | Exit Sub |
| 20 | End If |
| 21 | Dim Bbook As Workbook |
| 22 | Set Bbook = ActiveWorkbook |
| 23 | For i = 1 To Bbook.Worksheets.Count |
| 24 | Bbook.Worksheets(i).Copy After:=Abook.ActiveSheet |
| 25 | Abook.ActiveSheet.Name = Abook.ActiveSheet.Name & “(" & Bbook.Name & “)" |
| 26 | Next i |
| 27 | Bbook.Close |
| 28 | exname = Dir |
| 29 | Loop |
| 30 | Next k |
| 31 | 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のように、VBAには仕事の効率をアップさせることができる機能がいろいろあります。VBAのプログラミングスキルを身に着けることで、今まで何時間もかかかっていた作業をほんの数分で処理することができるようになります。
VBAを基本から学びたい！という方におすすめなのが、オンラインプログラミング学習サービス【1st Step】です。
【1st Step】は初心者のためのプログラミング学習サービスで、C言語・java・VBAの３つのプログラミングの基礎をオンラインで勉強することができます。
- 基礎からプログラミングを勉強したい
- まとまった勉強時間の確保が難しい
- 途中で挫折したくない
上記のように考えている方に【1st Step】はおすすめです。
下記の記事では、オンラインプログラミング学習サービス【1st Step】の特徴や私の体験など詳しく紹介しています。
これからVBAの勉強を始めようと考えている方は是非参考にしてみてください。