複数のエクセルを操作する方法【 開く・操作する・閉じるを行うVBA 】



Left Caption

豆父ちゃん

複数のエクセルファイルを同時に扱う業務の効率をもっと上げたい。なにかいい方法はないの?

このように考えている方におすすめの方法が、複数のエクセルファイルを 開く・操作する・閉じる処理を行うVBAです。

デスクワークでは、複数のエクセルファイルを同時に扱う業務がいっぱいですよね。

 

  • 似たような内容のExcelデータを見比べる
  • 提出用のエクセルファイルと管理用のエクセルファイルを見比べる
  • エクセルファイル内にSheetがいっぱいあるとき、2つ開く

複数のエクセルファイルを扱う業務長時間行っていると作業ミスが発生してしまう恐れがあります。こんなときにおすすめなのが、複数のエクセルファイルを同時に操作するVBAです。

 

複数のエクセルファイルを同時に操作するVBAのメリット

 

  • 複数のエクセルファイルを参照できる
  • 複数のエクセルファイルから必要事項を転記できる
  • 複数のエクセルファイルに転記できる

今回の記事では、登録しているエクセルファイルを自動で開いて操作するVBAを紹介します。



VBAでブックを開く方法

ブックとはエクセルファイルそのもののこと。ブックを開くVBAは、VBAを組んだブックから他のブックを自動的に開くというプログラムです。

ブックを開くプログラムの基本構造

 

WorkBook.Open Filename:="〇〇〇"

注意として〇〇〇には「Bookの名前」ではなく「Bookの場所」を入力します。

 

ブックの場所はプロパティで調べることができます。

 

上記で調べたブックの場所をVBAに記載

 

たった一文のプログラムで「ブログ.xls」というエクセルファイルを開くVBAが完成。

簡単に他のエクセルファイルを開くことができるものの、開く操作には注意が必要です。

注意事項開きたいブックが存在しているのか確認が必要

開きたいブックが既に開かれているか確認が必要

大人数で扱うエクセルファイルを開こうとしている場合、上記2つの注意事項を確認するVBAが必要になります。

 

エクセルファイルの存在を確認するプログラム

Dir関数

使い方 Dir(“Bookの場所")
内容 ブックの場所が存在した場合、ブックの場所を返しブックの場所が存在しない場合、空欄返す

Dirはエクセルファイルが存在するかしないかを確認するときによく使われる関数です。

サンプル

1 Sub test()
2 If Dir(“C:\Users\〇〇\Desktop\Book1.xls") <> “" Then
3 MsgBox “ファイルが存在します"
4 Else
5 MsgBox “ファイルが存在しません"
6 End If
7 End Sub

Dir関数の動作確認は上記のプログラムでやってみて下さい。

下記の記事ではDir関数を応用して複数のエクセルファイルを1つにまとめるVBAを紹介しています。

 

開きたいブックが既に開かれているか確認するプログラム

既に開いているブックを開こうとするとエラーとなりプログラムの動作が止まってしまいます。

なので、エラーを一旦無視するプログラムを記載します。

エラーを一旦無視するプログラム

On Error Resume Next

このプログラムの記載以降エラーは無視されます。

エラーを無視できたとはいえ既に開いているブックを開くことはできないので注意が必要です。このエラーを無視するプログラムはいろんな場面で使えるので是非覚えていた方がいいでしょう。

ブックを開くプログラムの後にエラーが発生していたかどうかを確認するプログラムを記載します。

プログラムは下記のようになります。

 

1 On Error Resume Next
2 WorkBook.Open Filename:="C:\Users\〇〇\Desktop\Book1.xls"
3 IF Err.Number>0 Then
4 MagBox"ファイルは既に開かれています"
5 Exit Sub
6 EndIf

これで開きたいブックが既に開かれているか確認することができます。

上記で紹介した2つの確認するプログラムを記載することで「ブックを開く」という操作でエラーが発生し動作が停止してしまうことはないでしょう。

 

複数のブックを操作

しっかりとブックの宣言をすることで、同時に複数のブックを操作することが可能になります。

 

基本的な構造

 

Book1を開く

 ↓
すぐBook1の宣言をする

 ↓
Book2を開く

 ↓

すぐにBook2の宣言をする

 

これを続けることでBookをいくつ開いても同時に操作することができます。今回は3つのブックを同時に操作する例を紹介します。

プログラムは下記のようになります。

 

1 “Desktop\Book1.xls"
2 Dim ABook As Workbook
3 Set Abook=ActiveWorkbook
4 Workbooks.Open Filename:="Desktop\Book2.xls"
5 Dim Book As Workbook
6 Set Bbook=ActiveWorkbook

開いた直後のBookはActiveWorkbookになるので「開く」プログラムの直後すぐに宣言します。

ブックの内訳は下記のようになります。

ブック名 プログラム内の宣言
ブログ.xls(プログラムを記載しているBook) ThisWorkbook
book1.xls ABook
book2.xls BBook

それぞれのブックの宣言を終えると後はやりたいことを記載するだけです。

例として3つのBookのA1セルに"OK"というテキストを記載するプログラムを紹介します。

 

サンプル

1 Sub test()
2 Workbooks.Open Filename:="C:\Users\suzu\Desktop\Book1.xls"
3 Dim Abook As Workbook
4 Set Abook = ActiveWorkbook
5 Workbooks.Open Filename:="C:\Users\suzu\Desktop\Book2.xls"
6 Dim Bbook As Workbook
7 Set Bbook = ActiveWorkbook
8 ThisWorkbook.ActiveSheet.Range(“A1") = “OK"
9 Abook.ActiveSheet.Range(“A1") = “OK"
10 Bbook.ActiveSheet.Range(“A1") = “OK"
11 End Sub

3つのブック操作を同時に行うことができました。またActiveSheetをSheet名に変更することでSheetを選んで操作することもできます。

 

当ブログでは紹介しているVBAをアレンジして提供するVBA開発の受注を行っています。

VBAの導入を考えていて気になる方は下記の記事を参照してください。

ブックを閉じる

ブックを閉じる際に注意すべき点は保存するか保存しないかを明確にする必要があります。

このようなメッセージが表示されプログラムが止まってしまうのを防ぎましょう。

保存しないで閉じるプログラム

1 Sub test()
2 Application.DisplayAlerts = False
3 Workbooks(“Book1.xls").Close
4 Application.DisplayAlerts = True
5 End Sub

2行目でメッセージの表示をOFFにする。

3行目でBookを閉じる。(このときデータは保存されません)

4行目は表示をOFFにしたメッセージをONに戻す

保存してから閉じるプログラム

1 Sub Sample3()
2 Application.DisplayAlerts = False
3 Workbooks(“Book1.xls").Save
4 Workbooks(“Book1.xls").Close
5 Application.DisplayAlerts = True
6 End Sub

さきほど紹介した保存しないで閉じるプログラムとほとんど同じですが、3行目にデータを保存するプログラムが追加されています。

 

BookとSheetの違い

最後にBookとSheetの違いを確認しておきます。

Bookとはエクセルデータそのもののこと。Excelを立ち上げるとファイル名が「Book1」となっていることからも理解できます。

SheetとはBookの中にあるページのこと。Excelを開いたとき下にあるタブに表示されているのがSheetです。

 

本(Book)は複数のページ(Sheet)から成り立っていますが、これと同じことです。

プログラムを記載する際、「Book」や「Sheet」の記載が多く出てくるので、こんがらないよう違いを明確にしておきましょう。

 

別のエクセルファイルを操作するVBAはとても便利

Excel VBAでブックを「開く→操作する→閉じる」までの一連の流れを紹介しました。

複数人が扱うエクセルを開くというプログラムは「保存場所の変更」や「名前の変更」でプログラムが動作しなくなる恐れがあるので注意が必要です。「ブック名を読み取って開く」方法や「フォルダ内のブックを開く」方法など開くというプログラムにはいろいろな方法があります。

他のブックを操作するVBAは多くのエクセル参考書やネットで情報が配信されています。

特に私のおすすめが、たった1秒で仕事が片付くExcel自動化の教科書【増強完全版】というエクセル参考書です。初心者にもわかりやすい内容になっていて、私がVBAの勉強を始めたとき1番最初に購入した本です。