0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

【Excelマクロ】他のブックのセル値をブックを開かずに取得する

Last updated at Posted at 2023-03-19

経緯

訳あって複数の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

参考

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?