別ブックを参照するには
別ブックの情報を参照するにはWorkbookクラスオブジェクトを利用します。Workbookオブジェクトを使って別ブックを扱う際に一つ注意があります。
それは、既にそのブックが開いている場合にVBAから開こうとすると「既に開いてるので開けない」というエラーになる点です。Excelブックで同じ名前のブックを開こうとして開けなかったことがあると思いますが、それと同じです。
これを回避するにはExcelクラスオブジェクトを新規に作り、そちらで読み取り専用で開く必要があります。言い方を変えれば、Excelをもう１つ起動して読み取り専用で開くということです。こうすれば同一ブックの二重起動によるエラーを回避できます。
以下では３つのサンプルを紹介します。
- １つ目は、新規にExcelを起動＋別ブック参照の方法。ほとんどの場合はこの方法で問題ありません。
- ２つ目は、現ブックで別ブックを参照する方法。別ブックが開いていないことが前提になります。
- ３つ目は、１つ目と２つ目のハイブリッドで、事前に別ブックが開いているか確認し、新規Excelを起動するかどうかを判定して別ブックを参照します。コードは長くなりますがより確実にしたい場合はこれを使ってください。
1. 新規にExcelを起動して別ブックを参照するサンプル
新規にExcelを起動して別ブックの一番左のシートの入力セル範囲をRangeオブジェクトで取得するコードです。
15行目と、18行目から22行目の部分はRangeオブジェクトの取得方法の例として２つのサンプルを書いています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Sub ReferOtherBook() Dim ex As New Excel.Application '// 処理用Excel Dim wb As Workbook '// ワークブック Dim sPath '// ブックファイルパス Dim r As Range '// 取得対象のセル範囲 Dim sht As Worksheet '// 参照シート '// 開くブックを指定 sPath = "C:\web\Book1.xlsx" '// 読み取り専用で開く Set wb = ex.Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True) '// 一番左のシートの入力セル範囲を取得 Set r = wb.Worksheets(1).UsedRange '// 各シートのA1セルを取得 For Each sht In wb.Worksheets Set r = sht.Range("A1") Debug.Print r.value Next '// ブックを閉じる Call wb.Close '// Excelアプリケーションを閉じる Call ex.Application.Quit End Sub |
コード説明
上の注意点の通り、2行目で別のExcelを起動して、12行目で別Excel上で別ブックを読み取り専用（ReadOnly:=True）で開いています。
15行目以降で開いた別ブックのセルの取得を行っています。この部分を変えることでどのセルを参照するのかを変えることが出来ます。
15行目では一番左のシートの入力セル範囲をUsedRangeプロパティで取得しています。
18行目から22行目では各シートのA1セルの値を表示します。
28行目のExcelアプリケーションの終了のコードが書かれていないとタスク上にExcelが残りっぱなしになり、タスクマネージャから終了するかPC再起動しない限りプロセスが消えません。
2. 閉じている前提の別ブックを開くサンプル
別ブックが閉じていて新規のExcelで開く必要がない場合のサンプルです。なので、開こうとしているブックが既に開いている場合はエラーになります。
処理の内容自体は上のコードと同じです。
違いは新規にExcelアプリケーションを起動しているかどうかです。具体的には上のコードの２行目のexというExcelアプリケーション変数を使っていない点です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Sub ReferOtherBook1() Dim wb As Workbook Dim sPath '// ブックファイルパス Dim r As Range '// 取得対象のセル範囲 Dim sht As Worksheet '// 参照シート '// 開くブックを指定 sPath = "C:\web\Book1.xlsx" '// 読み取り専用で開く Set wb = Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True) '// 一番左のシートの入力セル範囲を取得 Set r = wb.Worksheets(1).UsedRange '// 各シートのA1セルを取得 For Each sht In wb.Worksheets Set r = sht.Range("A1") Debug.Print r.value Next '// ブックを閉じる Call wb.Close End Sub |
3. 別ブックが開いているかどうかを確認するサンプル
別ブックが開いているかどうかが不明な場合は、事前に開いているかどうかをチェックした上で、開いていれば新規Excelアプリケーションを使い、開いてなければ使わない、という分岐処理を入れます。
ブックが開いているかどうかについては「ブックが開かれているかチェックする」で書いています。このページで紹介しているブックオープン判定関数（IsBookOpened）を使って対応すると以下のようになります。
13行目のIsBookOpened関数でブックが開いているかを確認します。
16行目でブックが開かれている場合は新規Excelアプリケーションを起動してそちらでブックを開き、開かれていない場合は現ブックで開きます。
後処理の38行目から40行目では新規Excelアプリケーションがある場合は終了させるようにしています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
Sub ReferOtherBook3() Dim ex As Excel.Application '// 処理用Excel Dim wb As Workbook Dim sPath '// ブックファイルパス Dim r As Range '// 取得対象のセル範囲 Dim sht As Worksheet '// 参照シート Dim bFlg As Boolean '// 開くブックを指定 sPath = "C:\web\Book1.xlsx" '// 既に開かれているか確認 bFlg = IsBookOpened(sPath) '// 開かれている場合 If bFlg = True Then Set ex = New Excel.Application '// 新規Excelで読み取り専用で開く Set wb = ex.Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True) Else '// 現ブックで読み取り専用で開く Set wb = Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True) End If '// 一番左のシートの入力セル範囲を取得 Set r = wb.Worksheets(1).UsedRange '// 各シートのA1セルを取得 For Each sht In wb.Worksheets Set r = sht.Range("A1") Debug.Print r.value Next '// ブックを閉じる Call wb.Close If bFlg = True Then Call ex.Application.Quit End If End Sub '// ブックオープン判定関数 Function IsBookOpened(a_sFilePath) As Boolean On Error Resume Next '// 保存済みのブックか判定 Open a_sFilePath For Append As #1 Close #1 If Err.Number > 0 Then '// 既に開かれている場合 IsBookOpened = True Else '// 開かれていない場合 IsBookOpened = False End If End Function |