複数のエクセルファイルを同時に操作するVBA【サンプルを使って紹介】
豆父ちゃん
こんな疑問を抱えながら作業をしている方のために、複数のエクセルファイルを操作するVBAを紹介します。
私が仕事で導入しているのは、別々のエクセルファイルで管理しているスケジュール表をいっぺんに操作する！というもの。どのような処理が可能なのかは下記の記事で詳しく紹介しています。
目次
複数のエクセルファイルを操作するVBAとはどういうものか
上記の動画では、仕事でよく使われる｢勤務表」と｢超過勤務整理簿｣の内容を一致させる！というサンプルを使って紹介しています。
別のエクセルファイルを操作する原理
別のエクセルファイルを操作するには、別のエクセルファイルを 開く → 保存 → 閉じる という処理を行います。
- 別のエクセルファイルを開く
- 元となるエクセルファイルと開いたエクセルファイルでやりたい処理を行う(コピペなど)
- 処理が終わったエクセルファイルを上書き保存
- エクセルファイルを閉じる
- 次に別のエクセルファイルを開く
- 2~5を繰り返す
上記の流れで複数のエクセルファイルを操作するVBAを作っていきます。
サンプルVBAの紹介
勤務表のエクセルファイルにVBAを記載し、超過勤務整理簿のエクセルファイルを操作する！というサンプルになります。
勤務表の変更内容を超過勤務整理簿に反映させるというものです。上記のサンプルのでは｢伊藤博文｣、｢黒田清隆｣、｢桂太郎｣3人分の超過勤務整理簿を操作しています。
| 1 | Sub エクセル操作() |
| 2 | Dim Abook As Workbook |
| 3 | Set Abook = ActiveWorkbook |
| 4 | Dim Ash As Worksheet |
| 5 | Set Ash = Abook.Worksheets(“設定") |
| 6 | For k = 7 To 19 |
| 7 | Dim Po As String |
| 8 | If Ash.Cells(k, 4) = “" Then |
| 9 | Exit Sub |
| 10 | Else |
| 11 | Po = Ash.Cells(k, 4) |
| 12 | End If |
| 13 | Workbooks.Open Filename:=Po |
| 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 = 2 To 13 |
| 21 | For j = 8 To 38 |
| 22 | Bbook.Worksheets(i).Cells(j + 5, 6) = Abook.Worksheets(i).Cells(k, j) |
| 23 | Next j |
| 24 | Next i |
| 25 | Application.DisplayAlerts = False |
| 26 | Bbook.Save |
| 27 | Bbook.Close |
| 28 | Application.DisplayAlerts = True |
| 29 | Next k |
| 28 | End Sub |
上記は勤務表エクセルファイルで使われている別のエクセルファイルを操作するVBAです。このVBAを参考にしながら説明していきます。
操作したいエクセルファイルの場所を取得
上記で紹介しているVBAの6行目から12行目と29行目で別のエクセルファイルの場所を取得しています。
| 6 | For k = 7 To 19 |
| 7 | Dim Po As String |
| 8 | If Ash.Cells(k, 4) = “" Then |
| 9 | Exit Sub |
| 10 | Else |
| 11 | Po = Ash.Cells(k, 4) |
| 12 | End If |
| 29 | Next k |
8行目と9行目は勤務表の設定Sheetの4列目のセルが空欄のときプログラムを終了させる！というもの。操作したいエクセルの登録は7行目から19行目まで可能で、登録してあるエクセルがなくなった時点でプログラムを終了させている。
11行目で変数Poに操作したいエクセルの場所を格納。
6行目と29行目のFor～Nextで繰り返し処理を行い、複数のエクセルファイルの操作を可能にしている。
エクセルファイルを開く・保存・閉じる方法
上記で紹介しているVBAの13行目から17行目で別のエクセルファイルを開く、25行目から28行目で保存、閉じるの処理をしています。
| 13 | Workbooks.Open Filename:=Po |
| 14 | If Err.Number > 0 Then |
| 15 | MsgBox “ファイルはすでに開かれています。" |
| 16 | Exit Sub |
| 17 | End If |
| 25 | Application.DisplayAlerts = False |
| 26 | Bbook.Save |
| 27 | Bbook.Close |
| 28 | Application.DisplayAlerts = True |
13行目は操作したいエクセルファイルの場所を格納したPoを開く！というもの。
14行目から17行目は操作したいエクセルファイルが既に開かれていて、｢読み取り専用｣になったときプログラムを終了させる！というもの。｢読み取り専用｣になった場合、変更した内容を上書き保存することができないのでプログラムを終了させています。
25行目はアラートを非表示にする！というもの。保存や閉じる操作をしたとき、｢保存しますか？｣ ｢閉じますか？｣などのメッセージの表示を防ぐためです。
26行目と27行目は操作が終わったエクセルの保存を行い、閉じる！というもの。なぜBbookになっているのか疑問に思うかもしれませんが、Bbookについては次のBookとSheetの設定方法で説明しています。
28行目は先ほど非表示にしたアラートを表示させる！というもの。
BookとSheetの設定方法
上記で紹介しているVBAの2行目から5行目と18行目から19行目でBookとSheetの設定を行っています。
| 2 | Dim Abook As Workbook |
| 3 | Set Abook = ActiveWorkbook |
| 4 | Dim Ash As Worksheet |
| 5 | Set Ash = Abook.Worksheets(“設定") |
| 18 | Dim Bbook As Workbook |
| 19 | Set Bbook = ActiveWorkbook |
| 22 | Bbook.Worksheets(i).Cells(j + 5, 6) = Abook.Worksheets(i).Cells(k, j) |
2行目から5行目で勤務表のエクセルファイルのBookとSheetの設定を行っている。
勤務表エクセルファイル … Abook
勤務表エクセルファイルの設定Sheet … Ash
18行目から19行目で開いたエクセルファイル(超過勤務整理簿)のBookの設定を行っている。
BookとSheetの設定をしっかり行えば、複数のエクセルファイルを同時に操作することが可能になります。他のエクセルファイルを操作する基本的なやり方を下記の記事で詳しく紹介しています。
複数のエクセルファイルを操作する方法
上記で紹介しているVBAの20行目から24行目で複数のえエクセルファイルの操作を行っています。
| 20 | For i = 2 To 13 |
| 21 | For j = 3 To 38 |
| 22 | Bbook.Worksheets(i).Cells(j + 5, 6) = Abook.Worksheets(i).Cells(k, j) |
| 23 | Next j |
| 24 | Next i |
For～Nextの繰り返し処理を使ってBbookにAbookの情報を反映させています。
変数 i … Sheetの繰り返し(1月～12月)
変数 j … 反映したいセルの場所の繰り返し
反映させるセルの場所を操作することで様々な情報を複数のエクセルファイルの反映させることができます。
For～Nextの基本的な使い方を下記の記事で詳しく紹介しています。
複数のエクセルファイルを操作するVBAは作業効率を格段にアップしてくれる
デスクワークでよく発生する問題が、サーバー内で同じようなエクセルファイルの種類がどんどん増えてしまう！と言うことです。
転勤などの理由で担当者が変わりサーバー内のデータの使い方が変わってしまうのは仕方のないことですが、いろんなエクセルファイルを操作するということは作業効率低下に繋がります。
それを防いでくれるのが、複数のエクセルファイルを同時に操作するVBA！です。
エクセルの保存場所、ファイル名、シートの設定をしっかり行えば簡単にリンクを作ることができます。
いろんなエクセルファイルを開いたり閉じたりして作業の効率が悪い！と感じている方におすすめのVBAです。
VBA初心者が効率化マクロを導入する方法
VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！
このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。
こんな問題を解決する方法がこちら
- マクロの開発を外注に依頼する
- マクロが設定されたエクセルファイルをダウンロードする
- 参考書やネットでVBAスキルを身に付ける
VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。
完成されたマクロを使ってエクセル業務を処理する
↓
作業効率が２倍以上アップ！！
↓
便利すぎて自分でもマクロを設定したいと考え始める
↓
参考書やネットの情報でVBAスキルを身に付ける
↓
マクロ開発を代行できる程のスキルを習得
VBAは
他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
という特徴があります。
毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。
マクロの開発を外注に依頼する
マクロの開発を外注に依頼する導入方法は
VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法
です。
「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」
こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。
ガッツポーズの人
デメリットは、費用がかかることだけ。
開発内容、依頼先によってかかる費用は大きく異なります。
私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。
疑問がある人
ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。
開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、
費用が安い！スピード納期！！
でやらせてもらっています。
ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。
ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。
マクロが設定されたエクセルファイルをダウンロードする
VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。
たとえば、
A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、
『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。
実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら
ガッツポーズの人
『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、
VBA初心者でもマクロを導入できる、費用が安い、
というメリットがあります。
しかし
VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい
というデメリットもあります。
ガッツポーズの人
個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。
今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。
参考書やネットでVBAスキルを身に付ける
先程も紹介しましたが、
VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
プログラミングです。
言い換えると、
情報量が豊富なので自分で勉強できちゃう
ということです。
私のVBAスキルはほとんど独学で身に付けたもので、
考えた処理のほとんどをVBAで表現できるレベルにまで到達した！
と思っています。
私の作ったマクロをプロのプログラマーが見れば、
お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！
と感じるはずです。
でも、
私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？
独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、
業務内容に合ったマクロを開発できるようになれます。
独学でVBAスキルを身に付けるコツは、
エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる
です。
とは言っても、全くの知識0の方は何をすればいいかわからないですよね。
VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。
VBAの知識0の方でもすいすい読み進められる内容になっています。
やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。
実際に私はこの参考書を読んでからVBAの勉強を始めました。
ひらめく人
VBAの魅力を発見した後は、
ひたすら自動化したい処理を実現するコードを書くだけ
です。
【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。
実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると
自然といろんなマクロが作れるようになっているはずです。
VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。