【VBA】複数ブックを開かずにデータ取得する方法（コピペOK）

この記事でできること

• 閉じたExcelブックから指定セルの値をブックを開かずに取得できる
• フォルダ内の全ファイルから指定セルの値を一括取得できる
• 50ファイルでも30秒程度で完了する高速処理

対象: Excel 2016以降 / Microsoft 365、Windows 10/11

完成イメージ（Before / After）

Before（手作業）:

1. フォルダ内の50個のExcelファイルを1つずつ開く
2. 指定セル（例: B2の売上合計）の値をコピー
3. 集計シートに貼り付け
4. ファイルを閉じて次のファイルを開く
5. PCが重くなってフリーズ。1時間のロス

After（マクロ実行）:

1. マクロを実行する
2. ステータスバーに「10 / 50 件取得中…」と進捗が表示される
3. 30秒で50ファイルの指定セルが集計シートに一覧化される
4. ファイルは1つも開かない。PCも軽い

50個のExcelを全部開いて1セルずつコピペしていた時期がある。10個目あたりでPCが重くなり、20個を超えるとExcelが応答なしになる。結局フリーズして強制終了。どこまでコピペしたか分からなくなって、最初からやり直し。1時間のロスだった。

ExecuteExcel4Macroという関数を知ってから状況が変わった。ブックを開かずに指定セルの値を直接取得できる。50ファイルが30秒で完了する。PCも軽いまま。開いて→コピペ→閉じるの繰り返しから解放された。

大量ファイルを開くのに消耗している人に、この高速取得を体験してほしい。まずは最小版で1ファイルから取得して、動くことを確認するところから始めよう。

ブックを開かずにデータを取得する。それだけで50ファイルの集計が30秒で終わる。

実行前の準備

バックアップを取る

この処理は閉じたブックからデータを読み取るだけなので、元ファイルは一切変更されない。読み取り専用の処理で破壊リスクはゼロ。ただし、集計先シート（マクロを実行するブックのSheet1）の既存データは上書きされるため、集計先に大事なデータがある場合はバックアップを取ること。

対象ファイルを閉じておく

ExecuteExcel4Macroは閉じたブック専用の関数。対象ファイルがExcelで開かれている状態で実行するとエラーになる。実行前に、取得対象のExcelファイルをすべて閉じておくこと。

Excelをマクロ有効ブック（.xlsm）で保存する

拡張子が .xlsx のままだとマクロは保存できない。

1. 「ファイル」→「名前を付けて保存」
2. ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
3. 保存

手順（コピペ → 実行まで約5分）

VBE（コードを書く画面）を開く

1. Excelで `Alt + F11` を押す
2. VBE（Visual Basic Editor）が開く

標準モジュールを挿入する

1. VBEのメニュー →「挿入」→「標準モジュール」
2. 白い画面（コードウィンドウ）が表示される

コードを貼り付けて実行する

1. コードウィンドウに、下のコードをそのままコピペする
2. コード内の書き換えポイント（★マーク）を自分の環境に合わせて変更する
3. `Alt + F8` → マクロ名を選んで「実行」

コード（最小版）– ExecuteExcel4Macroで閉じたブックから1セル取得

閉じたExcelブックから指定セルの値を1つ取得する。最もシンプルな形。まずはこれで動くことを確認する。

'============================================================
' ■ 閉じたブックから指定セルの値を取得（最小版）
'   → ExecuteExcel4Macroで開かずに1セル取得
'   → ファイルを開かないので高速・PCに負荷がかからない
'============================================================
Sub GetValueFromClosedBook()

    '--- ★書き換えポイント ---
    Dim filePath As String
    filePath = "C:\Data\"              '← フォルダパス（末尾の \ を忘れずに）

    Dim fileName As String
    fileName = "売上報告.xlsx"          '← ファイル名

    Dim sheetName As String
    sheetName = "Sheet1"               '← シート名

    Dim cellRef As String
    cellRef = "R2C2"                   '← セル参照（R行C列 → R2C2 = B2セル）
    '--- ★ここまで ---

    '--- ExecuteExcel4Macroでデータ取得
    '    書式: "'フォルダパス[ファイル名]シート名'!R行C列"
    Dim formula As String
    formula = "'" & filePath & "[" & fileName & "]" & sheetName & "'!" & cellRef

    Dim result As Variant
    result = ExecuteExcel4Macro(formula)

    '--- 結果を表示
    MsgBox "取得した値: " & result, vbInformation

End Sub

書き換えポイント

R1C1参照の早見表

ExecuteExcel4Macroのパス書式

パスの書き方が通常のファイルパスとは異なる。この書式を間違えるとエラーになる。

'C:\Data\[売上報告.xlsx]Sheet1'!R2C2
 ^^^^^^^^ ^^^^^^^^^^^^^^^^ ^^^^^^  ^^^^
 フォルダ   [ファイル名]     シート名 R行C列

• フォルダパスの末尾は `\`（バックスラッシュ）
• ファイル名は `[` と `]` で囲む
• シート名は `]` の直後に続ける（区切り文字なし）
• 全体をシングルクォート `’` で囲む
• 最後に `!` を付けてR1C1参照を書く

コードの流れ

1. **パス情報の設定**: フォルダパス、ファイル名、シート名、セル参照を変数で定義する
2. **パス文字列の組み立て**: ExecuteExcel4Macro用の書式に変換する
3. **データ取得**: ExecuteExcel4Macroで閉じたブックから値を取得する
4. **結果表示**: MsgBoxで取得した値を表示する

テスト方法: デスクトップに適当な.xlsxファイルを作り、Sheet1のB2セルに値を入れて閉じる。コードの filePath と fileName を書き換えて実行し、MsgBoxに値が表示されれば成功。ファイル一覧の取得（フォルダ内ファイル一覧を自動取得）で対象ファイルを事前に確認しておくと安心。

コード（実務版）– フォルダ内全ファイルをループ＋指定セル範囲一括取得＋進捗表示

実務では1ファイルから1セル取得するだけでは足りない。フォルダ内の全ファイルから複数のセルを一括取得して、集計シートにファイル名と一緒に書き出したい。

以前、50ファイルの月次報告書からB2（売上合計）とC2（担当者）を集計する作業を手作業でやっていた。全ファイルを開いてコピペして閉じるの繰り返し。50ファイルで1時間かかっていた。この実務版コードに切り替えたら30秒で完了した。ファイルを1つも開かないのでPCも軽いまま。

実務版では以下の機能を追加する：

• **Dir()でフォルダ内ループ**: .xlsx/.xlsファイルを自動検出
• **複数セル取得**: 指定した複数のセル参照をループで取得
• **集計シートに書き出し**: ファイル名 + 取得値を行ごとに出力
• **進捗表示**: Application.StatusBarで「○ / ○ 件取得中…」を表示（[進捗表示の詳細](/026)）
• **エラーハンドリング**: 取得失敗時はスキップして次のファイルへ

大量ファイルの統合処理（複数Excelファイルを1つに統合）と違い、この方法はファイルを開かないため圧倒的に軽量。ボタンに割り当てれば（マクロをボタン1つで実行する方法）、ワンクリックで集計が走る。

'============================================================
' ■ フォルダ内全ファイルから指定セルを一括取得（実務版）
'   → Dir()でフォルダ内の.xlsx/.xlsをループ
'   → ExecuteExcel4Macroで各ファイルから指定セルを取得
'   → 集計シートにファイル名＋取得値を書き出し
'   → Application.StatusBarで進捗表示
'   → エラー時はスキップして次のファイルへ
'============================================================
Sub GetValuesFromFolder()

    '--- ★書き換えポイント ---
    Dim folderPath As String
    folderPath = "C:\Data\報告書\"     '← 対象フォルダ（末尾の \ を忘れずに）

    Dim sheetName As String
    sheetName = "Sheet1"               '← 取得元のシート名

    '--- 取得するセルの設定（ヘッダー名とR1C1参照のペア）
    '    必要な列数に合わせて配列のサイズを変更する
    Dim headers(1 To 3) As String
    Dim cellRefs(1 To 3) As String

    headers(1) = "売上合計": cellRefs(1) = "R2C2"   '← B2セル
    headers(2) = "担当者":   cellRefs(2) = "R2C3"   '← C2セル
    headers(3) = "部署":     cellRefs(3) = "R2C4"   '← D2セル
    '--- ★ここまで ---

    '--- 画面更新を停止（高速化）
    Application.ScreenUpdating = False

    '--- エラー時に復帰するためのエラー処理
    On Error GoTo ErrHandler

    '--- 集計シートを準備
    Dim destWs As Worksheet
    Set destWs = ThisWorkbook.Worksheets("Sheet1")
    destWs.Cells.Clear

    '--- ヘッダー行を書き出し
    destWs.Cells(1, 1).Value = "ファイル名"
    Dim h As Long
    For h = LBound(headers) To UBound(headers)
        destWs.Cells(1, h + 1).Value = headers(h)
    Next h

    '--- フォルダ内のファイル数をカウント（進捗表示用）
    Dim totalFiles As Long
    totalFiles = 0
    Dim tmpName As String
    tmpName = Dir(folderPath & "*.xls*")
    Do While tmpName <> ""
        '--- 自分自身を除外
        If tmpName <> ThisWorkbook.Name Then
            totalFiles = totalFiles + 1
        End If
        tmpName = Dir()
    Loop

    '--- ファイルが見つからない場合
    If totalFiles = 0 Then
        Application.ScreenUpdating = True
        MsgBox "対象フォルダに.xlsx/.xlsファイルが見つかりません。" & vbCrLf & _
               "パス: " & folderPath, vbExclamation
        Exit Sub
    End If

    '--- 開始時刻を記録
    Dim startTime As Double
    startTime = Timer

    '--- フォルダ内のファイルをループ
    Dim currentFile As String
    currentFile = Dir(folderPath & "*.xls*")

    Dim row As Long
    row = 2  '← データは2行目から

    Dim fileCount As Long
    fileCount = 0

    Dim errCount As Long
    errCount = 0

    Do While currentFile <> ""

        '--- 自分自身は除外
        If currentFile <> ThisWorkbook.Name Then

            fileCount = fileCount + 1

            '--- ファイル名を書き出し
            destWs.Cells(row, 1).Value = currentFile

            '--- 各セルの値を取得
            Dim c As Long
            For c = LBound(cellRefs) To UBound(cellRefs)

                Dim formula As String
                formula = "'" & folderPath & "[" & currentFile & "]" & sheetName & "'!" & cellRefs(c)

                '--- エラー時はスキップ（ファイルが開いている等）
                On Error Resume Next
                Dim val As Variant
                val = ExecuteExcel4Macro(formula)

                If Err.Number <> 0 Then
                    destWs.Cells(row, c + 1).Value = "取得エラー"
                    errCount = errCount + 1
                    Err.Clear
                Else
                    destWs.Cells(row, c + 1).Value = val
                End If
                On Error GoTo ErrHandler

            Next c

            row = row + 1

            '--- 進捗表示（10件ごとまたは最後のファイル）
            If fileCount Mod 10 = 0 Or fileCount = totalFiles Then
                Dim elapsed As Double
                elapsed = Timer - startTime
                Application.StatusBar = fileCount & " / " & totalFiles & _
                    " 件取得中... 経過: " & Format(elapsed / 86400, "hh:nn:ss")
                DoEvents
            End If

        End If

        currentFile = Dir()
    Loop

    '--- 処理完了
    Dim totalElapsed As String
    totalElapsed = Format((Timer - startTime) / 86400, "hh:nn:ss")

    Application.StatusBar = False
    Application.ScreenUpdating = True

    MsgBox "取得完了" & vbCrLf & _
           "取得ファイル数: " & fileCount & " 件" & vbCrLf & _
           "取得エラー: " & errCount & " 件" & vbCrLf & _
           "経過時間: " & totalElapsed, vbInformation

    Exit Sub

ErrHandler:
    Application.ScreenUpdating = True
    Application.StatusBar = False
    MsgBox "エラーが発生しました。" & vbCrLf & _
           "エラー番号: " & Err.Number & vbCrLf & _
           "内容: " & Err.Description, vbCritical

End Sub

書き換えポイント

取得セル数を変更する場合: 配列サイズ (1 To 3) を (1 To 5) などに変更し、headers(4) と cellRefs(4) を追加する。

コードの流れ

1. **設定**: フォルダパス、シート名、取得セルの定義
2. **集計シート準備**: 既存データをクリアしてヘッダー行を書き出す
3. **ファイル数カウント**: Dir()で対象ファイル数を取得（進捗表示用）
4. **ファイルループ**: Dir()で.xlsx/.xlsファイルを1つずつ処理
5. **セル値取得**: ExecuteExcel4Macroで各ファイルから指定セルの値を取得
6. **エラー処理**: 取得失敗時は「取得エラー」と記入してスキップ（[エラー処理の詳細](/022)）
7. **進捗表示**: 10件ごとにStatusBarを更新＋DoEventsで応答維持
8. **完了処理**: 取得件数・エラー数・経過時間をMsgBoxで表示

ExecuteExcel4Macroはファイルを開かずにデータを取得する。Workbooks.Openで開いて→コピペ→閉じるよりも圧倒的に速い。50ファイルで30秒程度。

補足：ADO方式でSQL風にデータ取得

ExecuteExcel4Macroは1セルずつの取得に適している。一方、「Sheet1のA列が”東京”の行だけ取得したい」「ヘッダー付きの範囲をまとめて取得したい」といったケースではADO方式が有効。

ADO方式はSQLのSELECT文でデータを抽出できるため、条件指定や範囲取得に強い。ただし、ACE OLEDBドライバが必要で、64bit環境での互換性問題が起きる場合がある。初心者にはExecuteExcel4Macro方式を推奨する。

'============================================================
' ■ ADO方式で閉じたブックからデータ取得（参考）
'   → SQL風の条件指定が可能
'   → 複数行・複数列の範囲取得が可能
'   → ACE OLEDBドライバが必要
'============================================================
Sub GetValueByADO()

    '--- ★書き換えポイント ---
    Dim filePath As String
    filePath = "C:\Data\売上報告.xlsx"  '← ファイルのフルパス

    Dim sql As String
    sql = "SELECT * FROM [Sheet1$A1:D100]"  '← SQL文（シート名$範囲）
    '--- ★ここまで ---

    '--- ADO接続オブジェクトを作成
    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")

    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")

    '--- 接続文字列
    Dim connStr As String
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & filePath & ";" & _
              "Extended Properties=""Excel 12.0;HDR=YES"""

    '--- 接続してデータ取得
    On Error GoTo ErrADO
    cn.Open connStr
    rs.Open sql, cn

    '--- 集計シートに書き出し
    Dim destWs As Worksheet
    Set destWs = ThisWorkbook.Worksheets("Sheet1")
    destWs.Cells.Clear

    '--- ヘッダー書き出し
    Dim col As Long
    For col = 0 To rs.Fields.Count - 1
        destWs.Cells(1, col + 1).Value = rs.Fields(col).Name
    Next col

    '--- データ書き出し
    destWs.Cells(2, 1).CopyFromRecordset rs

    '--- 後片付け
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

    MsgBox "ADOでのデータ取得が完了しました。", vbInformation
    Exit Sub

ErrADO:
    If rs.State = 1 Then rs.Close
    If cn.State = 1 Then cn.Close
    Set rs = Nothing
    Set cn = Nothing
    MsgBox "ADOエラー: " & Err.Description, vbCritical

End Sub

ExecuteExcel4Macro方式 vs ADO方式

この記事のメインはExecuteExcel4Macro方式。特定セル（B2の売上合計など）を大量ファイルから一括取得するなら、ExecuteExcel4Macroが最もシンプルで高速。ADO方式は「条件付きで範囲取得したい」場合の選択肢として覚えておくと良い。

よくある落とし穴5選

1. パスの区切りが独自フォーマットで間違える

原因: ExecuteExcel4Macroのパス書式は 'フォルダパス\[ファイル名]シート名'!R行C列 という独自フォーマット。通常のファイルパスの感覚で書くとエラーになる。

自分も最初にハマったのがこのパス書式だった。"C:\Data\売上.xlsx!Sheet1!R2C2" と書いてエラー。角括弧でファイル名を囲むという発想がなかった。

対策: パス書式のテンプレートをコメントに残しておく。コード内の formula 変数で "'" & filePath & "[" & fileName & "]" & sheetName & "'!" & cellRef と分解して組み立てれば間違いにくい。

2. シート名にスペースがあると取得できない

原因: シート名にスペース（半角/全角）が含まれている場合、パス書式の解釈がずれてエラーになることがある。

対策: 可能であればシート名からスペースを除去する。どうしてもスペース付きのシート名を使う場合は、シート名の前後にシングルクォートを追加して試す。最も確実なのはスペースなしのシート名に統一すること。

3. 対象ファイルが開いていると二重アクセスでエラー

原因: ExecuteExcel4Macroは閉じたブック専用。対象ファイルがExcelで開かれている状態で実行すると、値の取得に失敗する。

対策: 実行前に対象フォルダ内のExcelファイルをすべて閉じる。実務版コードでは On Error Resume Next でエラーをキャッチし、「取得エラー」と記録して次のファイルに進む。

4. .xlsxと.xlsで動作が異なる場合がある

原因: 古い.xls形式（Excel 97-2003）と新しい.xlsx形式ではExecuteExcel4Macroの互換性が異なる場合がある。特に.xlsファイルでシート名に日本語が含まれる場合にエラーが起きやすい。

対策: 対象ファイルの拡張子を.xlsxに統一するのが最も安全。混在する場合は、エラーハンドリング付きの実務版コードを使い、エラーが出たファイルは手動で確認する。

5. ExecuteExcel4Macroは1セルずつしか取得できない

原因: ExecuteExcel4Macroの仕様上、1回の呼び出しで取得できるのは1セルのみ。B2からD2の3セルを取得するなら3回呼ぶ必要がある。

対策: 実務版コードのように cellRefs 配列でループすれば、複数セルの取得は簡単。ただし取得セル数が多い（10セル以上 x 100ファイル = 1000回以上）場合はADO方式の方が高速になる場合がある。

FAQ

Q1: ExecuteExcel4Macroのパス書式がわからない

"'フォルダパス\[ファイル名]シート名'!R行C列" のフォーマット。フォルダパスの末尾は \、ファイル名は [] で囲む、シート名は ] の直後に書く。全体をシングルクォート ' で囲み、最後に !R行C列 を付ける。コード内ではパーツごとに変数で組み立てているので、変数を書き換えるだけで済む。

Q2: 複数セルを一度に取得できる？

ExecuteExcel4Macroは1セルずつの取得。複数セルはForループで回す（実務版コード参照）。取得セル数が非常に多い（例: 1ファイルから50セル以上）場合は、補足セクションのADO方式を検討する。ADO方式ならSQL文で範囲を一括取得できる。

Q3: ネットワークドライブ上のファイルからも取得できる？

取得できる。ただしUNCパス（\\サーバー名\共有名\...）を使う場合はパス書式に注意。ネットワーク遅延で取得速度が落ちる場合がある。安定して取得したい場合はローカルにコピーしてから実行するのが確実。

Q4: 対象ファイルが開いているとどうなる？

エラーになる。ExecuteExcel4Macroは閉じたブック専用の関数。実務版コードでは On Error Resume Next でエラーをキャッチし、「取得エラー」と記録して次のファイルに進む仕組みにしている。エラー処理の基本は エラー処理で止まらないマクロを作る方法 を参照。

Q5: 取得した値が数値ではなく文字列になっている

ExecuteExcel4Macroは取得した値をVariant型で返す。数値として扱いたい場合は CDbl(result) や CLng(result) で型変換する。日付の場合は CDate(result) で変換できる。元のセルの書式設定に依存する場合があるため、取得後に型を確認すること。

まとめ

• `ExecuteExcel4Macro` で閉じたExcelブックから指定セルの値を開かずに取得できる
• パス書式は `”‘フォルダパス\[ファイル名]シート名’!R行C列”` という独自フォーマット
• 実務版コードでフォルダ内全ファイルから一括取得＋進捗表示＋エラーハンドリング
• 50ファイルでも30秒程度。ファイルを開かないのでPCに負荷がかからない
• 大量セル取得や条件指定が必要な場合はADO方式を検討する

関連記事

• [フォルダ内ファイル一覧を自動取得](/001) — 取得対象のファイルを事前に確認する
• [複数Excelファイルを1つに統合](/005) — ファイルを開いて統合する方式との比較
• [処理の進捗をステータスバーに表示する方法](/026) — 実務版コードの進捗表示の仕組み

次にやりたくなること

• **[マクロをボタン1つで実行する方法](/013)**: 集計マクロをボタンに割り当てて、ワンクリックで全ファイルからデータ取得
• **[セルの転記を自動化する方法](/016)**: 取得したデータを別シートや別ブックに自動転記する

もっとカスタマイズしたい場合

「取得セル範囲を業務に合わせて変更したい」「ADOで複雑な条件抽出をしたい」「取得したデータを自動でグラフ化したい」など、業務に合わせたカスタマイズが必要な場合は、ココナラで相談できます。

相談時に以下の情報があるとスムーズです：

• Excel のバージョン / OS
• 取得対象ファイルの構成（シート名、取得したいセル位置）
• 出力の見本（集計後にどんな形にしたいか）
• ファイルの数とフォルダ構成（目安）