Excel VBAで開かずに別のファイル（ブック）からデータを取得する裏技

Excel VBAで他のブックからデータを取得する方法をお探しですね。

広告

Excelで別ファイルのデータを「開かずに」取得するVBA裏技

Excelで集計表を作っていると、「別のファイルのデータを使いたいけど、毎回ファイルを開くのが面倒…」「参照元のファイルが重くて、開くたびに時間がかかる…」なんてことありますよね。

特に、日付入りのファイル名が毎日変わったり、複数のファイルから同じ範囲のデータを集めたいときは、普通の外部参照やVLOOKUPだけでは管理が大変になりがちです。

この記事では、Excel VBAを使って「画面に表示せずに別ファイルからデータを取得する方法」を紹介します。

実務でも使いやすい「裏側でこっそり読み込む方法」を中心に、わかりやすく解説していきますね。

「開かずに別ブックから取得する」ってどういうこと?

「開かずに別のブックからデータを取得する」と聞くと、ファイルを一切開かずに中身だけを魔法のように読み取る方法を想像するかもしれません。

でも実は、実務で一番安定して使いやすいのは、「画面には表示しないけど、裏側でExcelを起動してブックを開く」という方法なんです。

つまり、見た目には何も開いていないように見えるけど、実際にはExcelがバックグラウンドで対象ファイルを開いて、必要な値だけをこっそり取得している、というわけです。

この方法のメリット

普通に`Workbooks.Open`で画面にブックを表示して開くよりも、次のような利点があります。

– 処理中の画面のちらつきがない
– 作業の邪魔にならない
– 複数ファイルを扱う処理にも応用しやすい

外部参照の数式をセルに入れておく方法だと、リンク切れや更新エラー、ファイル名が変わったときの対応が大変ですよね。

でもVBAなら、ファイルパスやシート名を自動で組み立てられるので、日付別ファイルや月別ファイルにも柔軟に対応できます。

他の方法との比較

「完全に開かない」方法として、`ExecuteExcel4Macro`やADOを使う方法もあります。

ただ、`ExecuteExcel4Macro`は古い仕組みで、シート名やセル参照の扱いにクセがあって、複数セルや範囲が変わるデータの取得には向きません。

ADOも表形式のデータ取得には便利ですが、Excelのセル書式や数式の結果、複雑なシート構造には対応しにくいんです。

なので、初心者から中級者の方が安定性と使いやすさを重視するなら、「非表示のExcelで読み取る方法」が一番おすすめです。

別ブックを非表示で開いて値を取得する基本コード

それでは、実際のコードを見てみましょう。

ここでは、指定した別ブックの「Sheet1」にある「A1セル」の値を取得する基本的なコードを紹介します。

コードのポイント

– 今マクロを実行しているExcelとは別に、新しいExcelアプリケーションを作成する
– そのExcelを非表示のまま使う
– 読み取り専用で開くことで、参照元ファイルを誤って編集するリスクを減らす
– 処理が終わったら、必ず開いたブックとExcelを閉じる

“`vb
Sub 別ブックから値を取得する()

Dim xlApp As Object
Dim srcBook As Object
Dim srcPath As String
Dim v As Variant

On Error GoTo ErrHandler

‘ 参照元ファイルのパスを指定
srcPath = ThisWorkbook.Path & “\Book1.xlsx”

‘ 新しいExcelアプリケーションを作成(非表示)
Set xlApp = CreateObject(“Excel.Application”)
xlApp.Visible = False ‘ 画面に表示しない
xlApp.DisplayAlerts = False ‘ 警告メッセージを表示しない

‘ ブックを読み取り専用で開く
Set srcBook = xlApp.Workbooks.Open( _
Filename:=srcPath, _
UpdateLinks:=False, _
ReadOnly:=True)

‘ 値を取得
v = srcBook.Worksheets(“Sheet1”).Range(“A1”).Value

‘ このブックに値を貼り付け
ThisWorkbook.Worksheets(“Sheet1”).Range(“A1”).Value = v

ExitHandler:
‘ 後片付け(エラーがあっても必ず実行される)
On Error Resume Next
If Not srcBook Is Nothing Then srcBook.Close SaveChanges:=False
If Not xlApp Is Nothing Then xlApp.Quit
Set srcBook = Nothing
Set xlApp = Nothing
On Error GoTo 0
Exit Sub

ErrHandler:
MsgBox “データ取得中にエラーが発生しました。

” & vbCrLf & Err.Description
Resume ExitHandler

End Sub
“`

コードの説明

`CreateObject(“Excel.Application”)`で別のExcelを起動して、`Visible = False`にすることで画面に表示しない状態にしています。

`Workbooks.Open`では`ReadOnly:=True`を指定しているので、参照元ブックを読み取り専用で開きます。

データを取得する部分は、普通のVBAと同じように`srcBook.Worksheets(“Sheet1”).Range(“A1”).Value`と書くだけです。

簡単ですよね!

注意点:ブックを明示的に指定しよう

ここで大事なのは、**取得先と取得元を明確に分けて書くこと**です。

たとえば、`Worksheets(“Sheet1”)`のようにブックを省略すると、どのExcelのどのブックを指しているのか分かりにくくなって、思わぬシートに書き込んでしまう原因になります。

特に非表示で別Excelを開いている場合、画面で状態を確認しにくいので、`ThisWorkbook.Worksheets(…)`や`srcBook.Worksheets(…)`のように、**ブック名を省略しない書き方**を習慣にすると安全です。

複数セル・可変ファイル名・最終行取得に応用する方法

実務では、A1セルだけを取得するよりも、もっと複雑なケースが多いですよね。

ここでは、よくある応用パターンを紹介します。

複数セルをまとめて取得する

A1:B10のような範囲をまとめて転記したい場合、1セルずつFor文でコピーするより、**Range同士で一括代入する方が断然速い**です。

Excel VBAでは、セル範囲のValueを配列のようにまとめて扱えるので、大量データの転記では処理時間に大きな差が出ます。

たとえば、別ブックのSheet1にあるA1:B10を、マクロ実行ブックのSheet1へ転記する場合は、基本コードのデータ取得部分を次のように置き換えるだけです。

“`vb
‘ 範囲をまとめて一括転記
ThisWorkbook.Worksheets(“Sheet1”).Range(“A1:B10”).Value = _
srcBook.Worksheets(“Sheet1”).Range(“A1:B10”).Value
“`

たったこれだけで、10行×2列=20セルのデータが一気に転記されます!

日付でファイル名が変わる場合

日付によってファイル名が変わる場合は、`Format`関数を使ってファイル名を自動で組み立てると便利です。

たとえば「参照元ファイル_240522.xlsx」のような形式なら、次のように書けます。

“`vb
‘ 今日の日付でファイル名を作成
srcPath = ThisWorkbook.Path & “\参照元ファイル_” & Format(Date, “yymmdd”) & “.xlsx”
“`

前日分を取得したいなら`Date – 1`、特定のセルに入力された日付を使いたいなら`Range(“A1”).Value`を元に組み立てるなど、運用に合わせて調整できます。

“`vb
‘ 前日のファイルを開く
srcPath = ThisWorkbook.Path & “\参照元ファイル_” & Format(Date – 1, “yymmdd”) & “.xlsx”

‘ セルの日付を使う
srcPath = ThisWorkbook.Path & “\参照元ファイル_” & Format(Range(“A1”).Value, “yymmdd”) & “.xlsx”
“`

最終行まで自動で取得する

取得元の行数が毎回変わる場合は、**最終行を取得してから範囲を決める**方法が便利です。

A列に必ずデータが入っている表なら、次のように最終行を求められます。

“`vb
Dim lastRow As Long

With srcBook.Worksheets(“Sheet1”)
‘ A列の最終行を取得
lastRow = .Cells(.Rows.Count, “A”).End(-4162).Row

‘ 1行目から最終行までをまとめて転記
ThisWorkbook.Worksheets(“Sheet1”).Range(“A1:C” & lastRow).Value = _
.Range(“A1:C” & lastRow).Value
End With
“`

ここで使っている`-4162`は、Excel定数`xlUp`の値です。

Object型で遅延バインディングしている場合、環境によっては`xlUp`が認識されないことがあるので、数値で書くと参照設定に依存しにくくなります。

(もちろん、普通のExcel VBA内なら`xlUp`と書いても大丈夫です)

**重要なポイント**は、最終行取得でも必ず対象ブックと対象シートを明示して、マクロ実行中のブックと参照元ブックを混同しないことです。

INDIRECT関数や外部参照よりVBAが向いている場面

別ブックの値を参照するだけなら、セルに外部参照の数式を入れる方法もありますよね。

たとえば、こんな感じです。

“`
=’C:\Data\[Book1.xlsx]Sheet1′!A1
“`

外部参照の問題点

この方法は簡単なんですが、次のような場面では不安定になりやすいんです。

– 参照元ファイル名が変わる
– 保存場所が変わる
– リンク更新が失敗する
– 参照元ファイルを開かないとエラーになる

また、INDIRECT関数でファイル名を可変にしたいと考える人も多いですが、**INDIRECT関数は基本的に閉じている別ブックを参照できません**。

そのため、閉じたブックから安定して取得したい場合は、VBAで制御した方が確実です。

VBAが特に向いているケース

VBAが特に力を発揮するのは、こんな場面です。

– **複数ファイルから同じ範囲を集計する処理**
– **日付や部門名でファイル名が変わる処理**
– **取得後に値として貼り付けたい処理**

数式リンクのままだと、後から参照元ファイルを移動したときにリンク切れが起こることがあります。

でもVBAで取得して値として保存すれば、集計結果を独立したデータとして扱えるんです。

業務用の集計表では、参照元の変更に引きずられない「値の転記」が求められることも多いので、この方法は実用性がとても高いですよ。

注意点とエラー対策

非表示で別ブックを開く方法には、いくつか注意点があります。

一番大事なこと:必ず後片付けをする

特に注意したいのは、**エラーで処理が止まったときに`xlApp.Quit`が実行されないと、見えないExcelが裏側に残ってしまう**可能性があることです。

そのため、サンプルコードのようにエラー処理を入れて、最後に必ずブックを閉じてExcelを終了する構成にしておくことが大切です。

主な注意点まとめ

– **取得元ブックは原則として読み取り専用で開く**
→ 誤って編集してしまうリスクを防ぐ

– **`ThisWorkbook`と`srcBook`を明示して、対象ブックを取り違えない**
→ どのブックのどのシートか、常に明確にする

– **エラー時でも`Close`と`Quit`が実行されるようにする**
→ 裏側にExcelが残らないようにする

– **最初は`Visible = True`で動作確認し、安定してから`False`にする**
→ 何が起きているか見ながらデバッグする

– **書き込み処理を行う場合は、事前にバックアップを取る**
→ 万が一のときに備える

まとめ

この方法は、「Excel VBAで開かずに別のファイル(ブック)からデータを取得する」ための強力な入口になります。

厳密には裏側でブックを開いているんですが、ユーザーの操作画面を邪魔せず、外部参照より柔軟に、INDIRECT関数より安定してデータを扱えるのが大きな利点です。

**おすすめの学習ステップ**

1. まずは1セルの取得から試してみる
2. 次に範囲転記にチャレンジ
3. 最終行取得を覚える
4. 複数ファイル処理へ広げていく

この順番で進めていくと、日々の集計作業を大きく効率化できますよ。

ぜひ試してみてくださいね!

広告