Office TANAKA - Excel VBA Tips[ブックを開かないで読む]

ブックを開かないでセルのデータを読み込むには、Excel 4.0のマクロを使います。
次のコードは、C:\Book1.xlsを開かないでSheet1のセルA1を読み込みます

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

このままでは、C:\Book1.xlsが存在しなかったり、C:\Book1.xlsにSheet1というワークシートが存在しないとエラーになりますから注意してください。

次のコードは、C:\Book1.xlsを開かずにSheet1のセル範囲A1:A20を、アクティブシートのA列に書き込みます。

Sub Sample2()
    Dim i As Long
    For i = 1 To 20
        Cells(i, 1) = ExecuteExcel4Macro("'C:\[Book1.xls]Sheet1'!R" & i & "C1")
    Next i
End Sub

このExcel 4.0マクロでは、読み込むセルのアドレスをR1C1形式で指定します。R1は「1行目」を表しC1は「1列目(A列)」を意味します。セルをA1→A2→A3と読み進むには、R1C1→R2C1→R3C1と、R1C1形式の行数を増加させてやります。列方向に読み進む場合はR1C1→R1C2→R1C3ですね。

では、少しだけ実用的な例をご紹介しましょう。
C:\Book1.xlsのSheet1に、次図のようなデータが入力されていたとします。

このブックを開かずに、指定されたワークシートから[名前]フィールドを探し、見つかったら[名前]フィールドのデータをアクティブシートに書き込みます。また、参考までに、アクティブシートに書き込むのではなく、配列に格納するコードもオマケで書いておきます。
汎用性を高めるために、ブックの指定は[ファイルを開く]ダイアログボックスを使います。ワークシート名は手入力してもらいますが、指定されたワークシートがそのブックに存在するかどうかをチェックしています。

Sub Sample3()
    Dim OpenFileName As String, SheetName As String, Target As String, buf As String
    Dim i As Long, TargetCol As Long, GetNames()
    ''対象ブックを選択します
    OpenFileName = Application.GetOpenFilename("Microsoft Excel ブック,*.xls")
    If OpenFileName = "False" Then Exit Sub
    ''ファイル名に[]を付ける
    OpenFileName = Replace(OpenFileName, Dir(OpenFileName), "[" & Dir(OpenFileName) & "]")
    ''対象ワークシート名を取得
    SheetName = InputBox("読み込むワークシート名を入力してください。")
    If SheetName = "" Then Exit Sub
    Target = "'" & OpenFileName & SheetName & "'!"
    ''ワークシート名が正しいかどうか、まず読み込んでみる
    On Error Resume Next
    buf = ExecuteExcel4Macro(Target & "R1C1")
    If Err <> 0 Then
        MsgBox "ワークシート [ " & SheetName & " ] を読めませんでした。", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    ''[名前]フィールドを探す
    For i = 1 To 256
        If ExecuteExcel4Macro(Target & "R1C" & i) = "名前" Then
            TargetCol = i
            Exit For
        End If
    Next i
    If TargetCol = 0 Then
        MsgBox "[ 名前 ]フィールドが見つかりません。", vbExclamation
        Exit Sub
    End If
    ''データの読み込み
    For i = 2 To 10000                                                 ''(1)
        buf = ExecuteExcel4Macro(Target & "R" & i & "C" & TargetCol)
        If buf = "0" Then Exit For                                     ''(2)
        ''【アクティブシートに出力する】
        ActiveSheet.Cells(i - 1, 1) = buf
        ''【配列に格納する】
        ReDim Preserve GetNames(i - 1)                                 ''(3)
        GetNames(i - 1) = buf
    Next i
    ''配列に格納したデータの確認
    For i = 1 To UBound(GetNames)
        Debug.Print GetNames(i)
    Next i
End Sub

C:\Book1.xlsのデータは、10000行目までを読み込むようにしています(1)。65536などと最終行を指定してもいいのですが、もし、C:\Book1.xlsが古いバージョンのExcelで作成されたブックでしたら、最大行数が異なりますのでエラーになります。お使いの環境に合わせて変更してください。

[名前]フィールドを上から順に取得していき、空欄があったら処理を終了させます(2)。空欄の場合このExcel 4.0マクロは「0」を返します。今回は取得するデータが文字列なので、返り値が「0」かどうかで判定しています。数値データを扱う場合や、間にブランクセルが存在するようなケースでは、条件の判定を変えるか、一度アクティブシートに十分な大きさのデータを取得しておき、アクティブシート上で操作するといいでしょう。

(3)は、データを配列に格納するコードです。配列の要素数が事前にわかりませんので、動的配列としています。配列への格納が不要な場合は削除してください。

サンプルをご用意しました。下記のリンクからダウンロードできます。
サンプルブックのダウンロード「sample.lzh(16037byte)」

sample.lzhには「Book1.xls」「Sample.xls」「readme.txt」が入っています。readme.txtをご覧ください。

セルのデータではなく、閉じているブックからシート名の一覧を取得する仕組みは「閉じたブックのシート一覧」をご覧ください。

ブックを開かないでセルのデータを閲覧するアドイン「簡易ビュワー」を作りました。かなり便利です。ライブラリからダウンロードできます。
ライブラリ「ブックの簡易ビュワー」