VBAで現在開いているブックとは別のブックの値を取得したいことがあります。
しかし、この別ブックの値を取得する処理は以下の考慮事項があるので、個人的にはあまり使いたくない機能です。
- ブックを開くのを待つので時間がかかる
- ユーザーが対象のブックを開いていたときに「同じ名前のブックは開けない」という旨のエラーになる
同じ名前のブックを開けないというエラーは、もし既に開かれていればそのブックを使用するというロジックにすればいいだけですが、ブックを閉じる際に元から開いていたなら閉じないなど考慮が必要で面倒くさいです。
上記2点を解消させるために、別ブックの値を取得するときにはブックを開かずに値を取得する方法が良いかと思います。
ブックを開かずに別ブックの値を取得する方法
ブックを開かずに、別のブックの値を取得するには以下の2つの方法があるかと思います。
- ExecuteExcel4Macroを使う
- 新しいExcelアプリケーションを使用して開く
どちらも処理時間は、単純にWorkbooks.Openを使うよりも大幅に短縮することができます。
※以下のサイトを参考にさせていただきました
ブックを開かずにセル値を取得（ExecuteExcel4Macro,Excel.Application）
ただ、注意点があります。
ExecuteExcel4Macroの注意点
ExecuteExcel4Macroは高速であり、色々なサイトでおすすめされている方法ではありますが、Excel 4.0のマクロであるため、今後のExcelでは廃止される可能性はあります。
ただ、現時点(Excel2019)ではマイクロソフトの公式ページにもメソッドとして紹介されているので、使用に関して心配はないかなと考えています。（公式ページにあるということは非推奨ではないはず）
ExecuteExcel4Macro メソッド (Excel)
https://docs.microsoft.com/ja-jp/office/vba/api/excel.application.executeexcel4macro
また、詳しくは後述しますが、1セルずつしか値を取れないので大量のセルの値を取得する場合は不向きです。
新しいExcelアプリケーションを使用して開くの注意点
「新しいExcelアプリケーションを使用して開く」については、WorkBookオブジェクトを取得することができるのですが、バックグラウンドで動いているため、画面表示を操作しようとするとエラーになります。
具体的には、シートをアクティブにしたり、セルを検索したりする処理が使用できません。
（個人的には、あまり画面操作する処理自体が良くないと思っているので、困ったことはあまりないですが）
詳細なロジックについて記載をしていきます。
ExecuteExcel4Macroメソッド
ExecuteExcel4Macroメソッドを使用して、別ブックの値を取得するには以下のように記述します。
Sub test()
MsgBox ExecuteExcel4Macro("'C:\test\[item.xlsx]Sheet1'!R1C1")
End Sub上記コードでは、「C:\test\」というフォルダに有る「item.xlsx」というExcelファイルの「1行目1列目」のセルを取得してきています。
R1C1の部分は、RC形式のセル指定になっていて、Rの後ろの数字が行番号、Cの後ろの数字が列番号を表します。
なので、仮に3行目4列目のセルの値を取得する場合には、以下のようになります。
Sub test()
MsgBox ExecuteExcel4Macro("'C:\test\[item.xlsx]Sheet1'!R3C4")
End Subこの方法の欠点として、複数のセルの値を取得する場合に範囲を指定することができず、1つずつ取得するしかありません。
そのため、別ブックから値を大量に取得したい場合には、処理が遅くなってしまう可能性が高いです。
またセルのアドレスやシートについて、固定値で記載することになります。
「最初のシートから取得する」「使用最終行から取得する」のような動的な指定をしたいときには、使用しないほうがいいと思います。
新しいExcelアプリケーションを使用して開く
これは、WorkBooks.openは使用するので正確にはブックを開いています。
ただ、「Application.ScreenUpdating = False」を使用しなくても、ユーザーにブックを開いたことが見えないので、ブックを開かずに値を取得していると言って良いかと考えています。
Sub test()
Dim exl As Excel.Application
Dim wb As Workbook
Set exl = New Excel.Application
Set wb = exl.Workbooks.Open("c:\test\item.xlsx")
MsgBox wb.Worksheets(1).Cells(1, 1).Value
wb.Close
Set exl = Nothing
End Sub
「C:\test\」というフォルダに有る「item.xlsx」というExcelファイルの「1行目1列目」のセルを取得してきています。
「Application.ScreenUpdating = False」を使用してブックを開くときと比較すると、処理時間が優れていることが上げられます。
これは、Excelがマルチスレッドに対応できていない機能が多いため、複数のブックを開くとCPUの負荷が高くなるのに対して、別のインスタンスでブックを開けば負荷が分散されるためだと考えています。
また、冒頭に記載した「同じブックのエクセルを開けない」という問題もこの方法では問題になりません。
気をつけたい点としては、前述したとおりシートをアクティブにしたりする機能が使えないということと「マクロが終了してもオブジェクトが破棄されない」という点です。
上記コードで「Set exl = Nothing」を実施していますが、これを忘れるとマクロを動かすたびにExcelプロセスが作られてしまいます。
コメントを残す