【ExcelVBA】ブックを開かないでセルのデータを抽出

2019.07.02ExcelVBA,ブックの操作

ExecuteExcel4Macro メソッドを使用すれば、ブックを開くことなく、セルのデータを読み込むことが可能です。

ちょっとしたセルのデータを抽出するだけなのに、多数のブックを開いたり、同じく巨大なブックを開いたりする場面で、ExecuteExcel4Macro を使用すれば、ブックを開かないで済むので処理速度が飛躍的に速くなります。覚えておいて損はありません。

サンプル1

「ブック：C:\Book1.xlsx」「シート：Sheet1」「セル：B3」 のデータを抽出する。

Sub Sample1()
	MsgBox ExecuteExcel4Macro("'C:\[Book1.xlsx]Sheet1'!R3C2")
End Sub

セル位置はR1C1形式で指定します。そのため、行列ともに数字で位置を指定します。Rには行番号、Cには列番号を指定します。
例えば R5C6 ならば、5行目のF列 と解釈します。
「R」は「Row(行)」、「C」は「Column(列)」を意味します。

サンプル2

「ブック：E:\TEST\SAMPLE.xlsx」「シート：Sheet1」「セル：G10」 のデータを抽出する。

Sub Sample2()

  Dim WB_PATH As String
  Dim WB_NAME As String
  Dim WS_NAME As String
  
  Dim ROW As Long
  Dim COL As Long
  
  WB_PATH = "E:\TEST\"    'ブックパス(パスの終わりは\)
  WB_NAME = "SAMPLE.xlsx" 'ブック名
  WS_NAME = "Sheet1"      'シート名
  
  ROW = 10 '行番号
  COL = 7  '列番号
  
  Dim STR As String
  
  STR = "'" & WB_PATH & "[" & WB_NAME & "]" & WS_NAME & "'!R" & ROW & "C" & COL & ""
  
  MsgBox ExecuteExcel4Macro(STR)

End Sub

「¥¥」から始まる共有フォルダ内のブックからセルのデータを抽出したいとき

「¥¥」から始まる共有フォルダ内のブックからセルのデータを抽出しようとすると、サンプル1とサンプル2の方法では、エラーが発生します。

回避方法は、ExecuteExcel4Macro実行前に、ブックパスをカレントディレクトリに指定しておきます。
そして、ExecuteExcel4Macro の引数にブックパス以外の情報を与えれば、エラーが回避できます。

尚、共有フォルダにそもそもドライブレターが割り当てられているのなら、ドライブレターから始まるパスを指定することで、エラーは発生しません。なので、ドライブレターがあるのなら、下記のサンプル3でやる必要はなく、サンプル2で事足ります。

「ブック：\\192.168.0.77\technical\SAMPLE.xlsx」「シート：Sheet1」「セル：G10」 のデータを抽出する。

Sub Sample3()

  Dim WB_PATH As String
  Dim WB_NAME As String
  Dim WS_NAME As String
  
  Dim ROW As Long
  Dim COL As Long
  
  WB_PATH = "\\192.168.0.77\technical\"  'ブックパス(パスの終わりは\)
  WB_NAME = "SAMPLE.xlsx" 'ブック名
  WS_NAME = "Sheet1"      'シート名
  
  ROW = 10 '行番号
  COL = 7  '列番号
  
  Dim STR As String
  
  STR = "'[" & WB_NAME & "]" & WS_NAME & "'!R" & ROW & "C" & COL & ""
	
  CreateObject("WScript.Shell").CurrentDirectory = WB_PATH
  MsgBox ExecuteExcel4Macro(STR)
  CreateObject("WScript.Shell").CurrentDirectory = ThisWorkbook.Path

End Sub

シート名が常にあいまいという問題点がある場合

ExecuteExcel4Macro を使用するには「ブック名」「シート名」「セルの位置」が事前に判明している必要があります。
この中でもシート名だけは、実際にブックを開いてみないとよくわからない場面が、実務上ではよくあります。

参考程度ですが、ADOXオブジェクトを使用して、ブックを開かないでシート名を抽出する方法を、以下のリンク先に書いておきました。

詳しくは記事：ブックを開かないでワークシート名を抽出