複数のエクセルファイルを同時に操作するVBA【サンプルを使って紹介】

Left Caption

豆父ちゃん

同じような編集を複数のエクセルファイルに行っていて、効率がすごい悪い。複数のエクセルファイルを同時で処理する方法はないの?

こんな疑問を抱えながら作業をしている方のために、複数のエクセルファイルを操作するVBAを紹介します。

私が仕事で導入しているのは、別々のエクセルファイルで管理しているスケジュール表をいっぺんに操作する!というもの。どのような処理が可能なのかは下記の記事で詳しく紹介しています。

実際の仕事で使われているVBAを一つ一つ詳しく紹介していきます。



複数のエクセルファイルを操作するVBAとはどういうものか

上記の動画では、仕事でよく使われる「勤務表」と「超過勤務整理簿」の内容を一致させる!というサンプルを使って紹介しています。

 

別のエクセルファイルを操作する原理

別のエクセルファイルを操作するには、別のエクセルファイルを 開く → 保存 → 閉じる という処理を行います。

  1. 別のエクセルファイルを開く
  2. 元となるエクセルファイルと開いたエクセルファイルでやりたい処理を行う(コピペなど)
  3. 処理が終わったエクセルファイルを上書き保存
  4. エクセルファイルを閉じる
  5. 次に別のエクセルファイルを開く
  6. 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の設定を行っている。

開いたエクセルファイル(超過勤務表) … Bbook

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です。