経緯

訳あって複数のExcelファイルから値を抽出して1ファイルにまとめるマクロを組むことになった。
「ブックを開いて値を取得する」を繰り返す処理だと時間が掛かるので、ブックを開かずに値を取得する方法が無いか調査した。

外部ブックから値を取得する関数

前提条件: マクロ内に空のシートをvba_wkという名前で作っておくこと

Function getOtherBookValue(ByVal dirPath As String, ByVal fileName As String, ByVal sheetName As String, ByVal cellAddress As String)
    
    Dim sheet As Worksheet
    Set sheet = Worksheets("vba_wk")
    
    '数式を使って、別ブックを参照
    sheet.Cells(1, 1) = "='" & dirPath & "\[" & fileName & "]" & sheetName & " '!" & cellAddress

    getOtherBookValue = sheet.Cells(1, 1).Value '数式を値に変換して返却
    sheet.Cells(1, 1).Delete '後片付け

End Function

使用例

Sub test()
    ' test.xlsx A2セルの値を現在開いているシートのA1セルに書き込む
    ActiveSheet.Range("A1").Value = getOtherBookValue("C:\test", "test.xlsx", "Sheet1", "A2")
End Sub

参考