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

 

Left Caption

疑問がある人

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

 

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

 

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

 

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

 

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

 

  • 毎日もしくは毎月という頻度でシートのコピペ作業をしている
  • 職員全員の勤怠表の集計を手作業で行っている
  • 単純なコピペ作業を何時間もかけて手作業で行っている

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

 

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

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

 

Right Caption

上を目指す人

作業効率が格段にアップ!他の業務に専念できるようになった

 

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

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

 

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

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

 

広告

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

 

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

 

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

 

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

 

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

 

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

 

Left Caption

ガッツポーズの人

使い方はとてもシンプルで誰でも簡単に扱えます。

 

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

 

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

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

 

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

 

Left Caption

ひらめく人

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

 

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

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

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

 

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

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

Right Caption

作業する人

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

 

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

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

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

 

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

 

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

 

Left Caption

上を目指す人

一度VBAを設定すると今後ずっと使い続けることができます。シートのコピペ作業が多い!という方におすすめです。
実際にVBAを使ってシートを集約した様子がこちら
13シート分の情報を一瞬で集約することができました。
追加されるシートの名称は、「シート名+ファイル名」となる設定になっているのでシート名が被る心配がありません

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

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

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

 

導入方法① シートの設定

 

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

 

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

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

 

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

 

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

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

 

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

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

 

 

Left Caption

ガッツポーズの人

シート名を区別するVBAはエクセル作業の効率をアップさせるVBAには欠かせない技術です。

 

 

導入方法② VBAの作成

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

 

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

 

導入方法③ ボタンの作成

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

 

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

 

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

 

 

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

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

 

 

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

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

 

 

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

 

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

 

 

Left Caption

ガッツポーズの人

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

 

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

 

使い方① ブックの指定

 

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

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

 

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

 

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

 

Left Caption

ガッツポーズの人

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

 

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

 

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

 

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

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

 

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

設定が難しい!すぐに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)

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を組み合わせることによって、フォルダ内の全ファイル名を取得することができます。

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

 

Left Caption

ひらめく人

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

 

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

使い方① フォルダの指定

 

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

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

 

Right Caption

拒否する人

シート情報が多すぎると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の3つのプログラミングの基礎をオンラインで勉強することができます。

 

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

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

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

 

 

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