(Excel VBA)閉じているブックからデータを取得する｜日々の覚書…日常のことも少しだけ

仕事でExcelを使うことが多くなってきたのですが

会社ではVBAを毛嫌いされているので

様々な関数を駆使して色々作っています。

しかし今回久々にVBAでプログラムを組んでみようと思い

色々、調べて実装してみました。

閉じているExcelファイルからデータを取得する

やりたいことは

Ａのファイルを開いたら

Ｂのファイルと比較して

差分をＡのファイルで修正する。

その際、Ｂのファイルは開かないまま。

というのが理想です。

何はともあれ

まず、Excelファイルを開かないまま

データを取得できるのかです。

調べてみたところ・・・ありました。

【ExecuteExcel4Macro】というコマンドを使うようです。

Excel 4.0のマクロを使う仕様になっていますね。

使い方だと

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

上記はCドライブにあるファイル名：Book1.xlsを開かないでSheet1のセルA1を読み込み方法です。

どうも、このコマンドは

ファイル名の前後に【 [ 】と【 ] 】をつけなくてはならず、

また、セルの番地はR1C1で指定しないといけないみたいです。

あと、空欄の場合は「0」を返します。

これを踏まえて、

実装してみたのがこちらです。

Sub Sample2()
    Dim OpenFileName As String '調べたいファイルのフルパス+ファイル名
    Dim Target As String '調べたいファイルのシート名確認用
    Dim SheetName As String '調べたいファイルのシート名
    Dim Number As String '調べたいファイルのデータ
    
    'ファイル名を作成
    OpenFileName = Sheets("sheet1").Cells(1, 2).Value & "\" & Sheets("sheet1").Cells(2, 2).Value & ".xlsx"
    
    'ファイルが存在するか
    If Not Dir(OpenFileName) <> "" Then
        MsgBox ("ファイルがありません。" & vbCrLf & "ファイルの場所・名前を確認して修正して下さい。"), vbExclamation
        OpenFileName = ""
        
        Exit Sub
    Else
        '[]をつけたファイル名を再作成
        OpenFileName = Sheets("sheet1").Cells(1, 2).Value & "\[" & Sheets("sheet1").Cells(2, 2).Value & ".xlsx]"

        'ファイルパス+シート名を作成
        SheetName = Sheets("sheet1").Cells(3, 2).Value

        'ワークシート名が正しいかどうか、まず読み込んでみる
        Target = "'" & OpenFileName & SheetName & "'!"

        'Errorステートメントでエラー処理ルーチンを有効にする
        On Error Resume Next 'Errorステートメントでエラー処理ルーチンを有効にする
        Number = ExecuteExcel4Macro(Target & "R1C1")
        If Err <> 0 Then
          MsgBox ("ワークシート [ " & SheetName & " ] を読めませんでした。" & vbCrLf & "シート名を確認して下さい。"), vbExclamation
          OpenFileName = ""
          SheetName = ""
          ScheduleNumber = ""
          Target = ""
          Exit Sub
        End If
        'Errorステートメントでエラー処理ルーチンを無効に戻す
        On Error GoTo 0

        'エラーがなかったら表示する
        MsgBox(Number)
    End If
End Sub

運用としてVBコードはいじられたくないので

開くファイルのシートのB1に読み込みたいファイルの場所の絶対パス

B2にファイル名、B3にシート名を記載するようにします。

こうすれば使う人が変更・修正も可能になるためです。

あとは、実際にそのファイルがあるかをチェックして

問題なければデータを表示するようにしています。