複数のエクセルファイルを1つのブックにまとめるVBA【 コピーして使えるサンプル付 】

Left Caption

疑問がある人

複数のエクセルファイルを1つのエクセルファイルにまとめたい。どうやればいいの?

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

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

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

 

私の職場では、シートをコピーして別のブックに貼り付ける!という作業を定期的に行っていました。内容的に簡単な作業で昔から決められた業務だったので、全員がなんとなく行っているという状況でした。

 

Right Caption

作業する人

今行っている仕事が最善の方法かどうか疑うことはとても重要です

開発したVBAを導入すると、今まで当たり前と思い込んでいた作業がとんどでもなく効率の悪い方法だったと全員が認識することに!今ではシートのコピペ作業はVBAを使って一瞬で処理することが当たり前になっています。

この記事では、複数のエクセルファイルを1つのまとめるVBAの特徴、導入方法について詳しくご紹介しています。コピペして使えるサンプルVBAを掲載しているので是非使ってみてください。

 

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

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

エクセルファイルを1つにまとめるVBAの流れ

 

  1. 指定したブックを開く
  2. ブック内の全シートをコピー
  3. VBAが設定されているブックに張り付け
  4. 指定したブックを閉じる
  5. 1~4の繰り返し

大きく分けて5つの工程で作らています。完成したVBAの動きを見ると複雑で難しいと感じるかと思います。しかし、工程を分けて考えてみると複雑なVBAは一切使われていません。

 

Left Caption

ひらめく人

上記のVBAの流れに意識しながらサンプルVBAを確認すると理解しやすいです

 

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

 

1つ目・・・1つのブックを指定してシートを集約するVBA

→ピンポイントで情報を集約できる

 

2つ目・・・フォルダを指定してフォルダ内にある全てのブックを集約するVBA

→まとめて多くの情報を集約できる

 

Left Caption

ひらめく人

業務内容によって2種類のVBAを使い分けると便利です

 

1つのブックを指定してシートを集約するVBAとは

1つのブックを指定してシートを集約するVBAとはどういうものかをご紹介していきます。下記の動画では実際のVBAの動きを確認することができます。

指定したブックにある全てのシートをVBAが設定されたブックに集約することができます。「ブックを開く → シートをコピー → VBAが設定されたブックに貼り付け」といった単純作業を繰り返し行います。

コピペするシートが一つや二つの場合、時間をかけてVBAを設定する必要はありませんが、コピペしたいシートの数が多くなるとVBAを使って自動化してしまうのがおすすめです。一度VBAを設定すると今後ずっと使い続けることができるので、シートにコピペ作業が多い!という方におすすめのVBAです。

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

導入方法① シートの設定

 

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

B3セル~B20セルの背景色を白、それ以外は黄色にしています。これは、「ブックの場所を記載するセルがB3セル~B20セルである」ということをわかりやすくするためです。ブックを指定してシートを集約するVBAにはB3セル~B20セルの情報を読み取るプログラムになっているので、ブックの場所を記載するセルを勝手に変更してはいけません。

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

 

導入方法② VBAの作成

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

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

 

導入方法③ ボタンの作成

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

 

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

 

 

ボタンを作り、「右クリック」 → 「マクロの登録」 → 「テスト」 → 「OK」でブックを指定してシートを集約するVBAをボタンに設定することができます。

 

表示している「ボタン」のテキストを「実行」に変更します。「ボタン」の上で、「右クリック」→「テキストの編集」でテキストを変更することができます。

 

 

1つのブックを指定してシートを集約するVBAは下記のサンプルVBAでコピーしてすぐに使うことができます。是非試してみてください。

 

サンプルVBA(1つのブックを指定してシートを集約する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に関してもっと詳しく知りたい!という方は下記の記事を参考にしてください。

 

 

Left Caption

ガッツポーズの人

これで1つのブックを指定してシートを集約するVBAの導入ができます。簡単に設定できますので試してみてください

 

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

使い方① ブックの指定

 

上図のようにB3セル~B20セルに、集約したいシートが保存されているブックの保存場所を記入します。最後に記入するブックの名前には、拡張子までしっかり記載することに注意が必要です。

 

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

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

 

Left Caption

ガッツポーズの人

ブックの場所を記載してVBAを実行するだけ!とても簡単です

 

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

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

私は、このVBAを使って約50シート分の集約を行ったことがあります。VBAの処理が完了するまで1分程度時間がかかりましたが、問題なく集約することができました。多くのシートをコピペする作業をしている方におすすめのVBAです。

 

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

ブックが保存されているフォルダを指定してシートを集約するVBAとはどういうものかをご紹介していきます。下記の動画では実際のVBAの動きを確認することができます。

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

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

 

Left Caption

ガッツポーズの人

フォルダ単位でまとめてシート情報を集約したい!という方におすすめです。

 

ブックが保存されているフォルダを指定してシートを集約する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を組み合わせることによって、フォルダ内の全ファイル名を取得することができます。この方法は他にもいろんなことに応用することができるので是非使い方をマスターしておきましょう。

 

Left Caption

ひらめく人

1つのブックを指定してシートを集約するVBAブックが保存されているフォルダを指定してシートを集約するVBAを比較すると違いがよくわかります

 

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

使い方① フォルダの指定

 

上図のようにB3セル~B20セルに、集約したいシートが保存されているフォルダの保存場所を記入します。「サンプル」と「サンプル2」というフォルダの2つに保存されている全ブックの全シートをまとめて集約することができます。

 

Right Caption

拒否する人

シート情報が多すぎるとVBAの処理にかなり時間がかかってしまうので注意が必要です。

 

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

 

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

 

 

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

 

ボタンをクリックして、ブックが保存されているフォルダを指定してシートを集約するVBAを実行すると上図のようにシートが自動的に作成されます。

このVBAでは、知らず知らずのうちに大量のシートを集約する処理を実行してしまう恐れがあります。指定するフォルダ内にブックがどれだけ保存されているか確認してから実行するように注意しましょう。

 

下記のサイトでは、1つのブックを指定してシートを集約するVBAブックが保存されているフォルダを指定してシートを集約するVBAの2種類のVBAをダウンロードすることができます。2種類のVBAがあればどんな集約業務にも対応することができるかと思います。

 

<<複数のエクセルファイルを1つのブックにまとめるVBAのダウンロードはこちら

 

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

複数のブックを一つにまとめるVBAを紹介しました。やり方をマスターをすれば、サーバー内にあるありとあらゆるエクセルファイルを一瞬で編集することができるようになります。このマクロは実際の業務でかなり使えるマクロなので自由に扱えるようにしておきましょう。

下記の記事では、VBA参考書の選び方やポイントについて詳しく紹介しています。別シートに情報を自動反映するエクセルマクロもVBA参考書を使って開発したものです。

 

 

仕事の効率アップにつながるマクロを開発してみたい!と考えている方は是非参考書で読んでみて下さい。あなたの考えを表現するためのVBAがきっと見つかると思います。

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

 

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

 

 

エクセル作業の効率化マクロを導入することで、ミスが激減し、大幅な作業時間の削減に繋がります。エクセルVBAの導入を検討している方は参考にしてみてください。

 

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

複数のエクセルファイルを一つにまとめるVBAのように、VBAには仕事の効率をアップさせることができる機能がいろいろあります。VBAのプログラミングスキルを身に着けることで、今まで何時間もかかかっていた作業をほんの数分で処理することができるようになります。

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

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

 

  • 基礎からプログラミングを勉強したい
  • まとまった勉強時間の確保が難しい
  • 途中で挫折したくない

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

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

 

 

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