他のブックを開かずにセルの値を取得する

非常に便利なのでメモ。

以下サンプル。

Sub Sample()

    ' 日付の名前でシートを作成していく
    Dim sheetName As String
    sheetName = Format(Date, "yyyymmdd")

    ' 現在のパスを取得
    Dim curPath As String
    curPath = ThisWorkbook.Path

    ' シートが存在するかチェックする
    Dim ws As Worksheet
    Dim isExist As Boolean
    isExist = False
    For Each ws In Worksheets
        If ws.Name = sheetName Then
            isExist = True
        End If
    Next ws

    If isExist = False Then
        ' シートを作成する
        ' 「TEMPLATE」のコピーをコピー元の前に作成
        Worksheets("TEMPLATE").Copy Before:=Worksheets("TEMPLATE")
        ActiveSheet.Name = sheetName
    End If

    ' 同じ階層にある他のブックのセルの値を読み込む
    Dim bookName As String
    bookName = "コピー元ブック.xlsx"

    Set ws = Worksheets(sheetName)

    ' コピー元ブック.xlsxのA1のセルの値を取得
    ws.Cells(1, 1).Value = ExecuteExcel4Macro("'" & curPath & "\" & "[" & bookName & "]Sheet1'!R1C1")

End Sub

サンプルの説明をします。
サンプルでは"TEMPLATE"とい元ネタとなるシートをコピーしその日の日付でシートを作成。
その後、同じ階層に存在する"コピー元ブック.xlsx"のA1の値を取得しています。
まず、シートのコピーについて。

Worksheets("TEMPLATE").Copy Before:=Worksheets("TEMPLATE")
ActiveSheet.Name = sheetName

今回は、TEMPLATEシートの前にコピーを作成したかったので
Before:=としています。後ろならばAfter:=と指定してください。
コピー後、シート名を変更しています。

そして、今回の目玉。他のブックを閉じたままセルの値を取得している部分です。

ws.Cells(1, 1).Value = ExecuteExcel4Macro("'" & curPath & "\" & "[" & bookName & "]Sheet1'!R1C1")

非常に便利です。書き方としては

ExecuteExcel4Macro("'パス[ブック名]シート名'!R1C1形式")

セルの指定がR1C1形式であることと、対象のブック、もしくはシートが
存在しない場合にエラーとなってしまうことには注意してください。