Excel(2019)別ブックの読み取り

みなさんこんにちは、「ITユニプラ」編集部です。

今回は、エクセルから別のエクセルのデータを参照する方法をご紹介します。管理するデータが増えるとエクセルファイルを分けたり、過去に作成したエクセルのデータを参照したりといったことがあり、そんなときに多用している機能です。

特に、

Excelデータを分析する場合、他のブック（別のExcelファイル）から値を持ってきたいことがあるかと思います。
その場合、他のブックと同期が取れる様に、直接他のブックの値を「参照」することがよいです。
単純に他のブックから値をコピーして貼り付けても良いですが、コピー元のブックの値の変更があった場合、整合性が取れなくなります。
ブックの「参照」をすることで、値の変更があっても影響がでません。

今回はそういったシーンでご利用いただける機能となります。

Excel他ブックの値を取得するケース

複数のデータを集めて分析する際、値だけをコピーすると出所の元値が変わっても対応できず、計算された値が正しい値なのか、間違って入力された値なのかわからなくなることがあります。

このように出力された値の根拠を残すため、出所のExcelブックから参照して値を登録するケースがあります。そして、実現する方法としては以下の方法があります。(今回は例として参照先ブック「ExcelTest.xlsx」のシート「Sheet1」のA1からA5までのデータを読み取り、「parentExcel.xlsm」のシート「Sheet1」のA1からA5のセルに反映させます)

目次『Excel他ブックの値を取得する4つの方法』

1. 『数式で参照先セルを指定』
2. 『Workbooksを利用したデータ取得(VBA(Visual Basic for Applications))』
3. 『Excel.Applicationを利用したデータ取得(VBA)』
4. 『ExecuteExcel4Macroを利用したデータ取得(VBA)』
5. こんな方法もあります！

まずは、値の取得の違いの違いについて説明させていただきます。

1.『数式で参照先セルを指定』

数式の中で直接別ブックの値を指定する方法です

使い方

①参照先のエクセル(ブックを開いておく)

②反映させるセルに数式(＝)を入力後、ウィンドウを参照先ブックへ切り替える

③「ExcelTest.xlsx」にて参照先セルを範囲で指定

④「parentExcel.xlsm」に「ExcelTest.xlsx」の値が反映される

3.『Excel.Applicationを利用したデータ取得(VBA)』

VBAを利用する点、WorkBooksを利用する点は同じですが、別ブックを開く際に別のプロセスを利用して処理を早める手法です。

プログラム上でプロセスを終了させないと、プロセスが残り続けてしまう点に気を付ける必要があります

使い方

①「開発」メニューの中の「Visual Basic」項目を選択し、エディター画面を表示
(先ほどの”2.『Workbooksを利用したデータ取得(VBA)』”と同様に、ホーム画面の「その他…」から、オプション項目を開き「開発メニュー」表示させます)

②Visual Basic Editorを開いたら、標準モジュールを挿入します

③標準モジュールに以下のsubファンクションを記述
Sub TEST2()
Dim exlApp As Excel.Application
Dim wb As Workbook
Dim Wbook1, Wbook2
Set exlApp = New Excel.Application
Set Wbook1 = ThisWorkbook
Set Wbook2 = exlApp.Workbooks.Open("C:\workspace\ExcelTest.xlsx")
‘C:\workspace\ExcelTest.xlsxは参照先Excelブックが置かれているファイルパスを指定
For I = 1 To 5
Cells(1, I) = Wbook2.Worksheets("Sheet1").Cells(1, I)
Next I
Wbook2.Close False
Set exlApp = Nothing
End Sub

④「sub/ユーザーフォーム」を実行

「sub/ユーザーフォーム」を実行すると、1.『数式で参照先セルを指定』の④と同じ結果になります

この方法の場合、以下の効果、影響があります。

WorkBooks.openを利用するが、新しいExcelプロセスをバックグラウンド(裏側)で開くため、他のユーザーには開いたことが見えない。(ブックを開かずにデータを取得しているように見える)
2つのプロセスで複数のブックを開くことでマルチスレッドを活かした負荷分散が行われるため、１．『数式で参照先セルを指定』より処理が少し早くなる。
プログラム上でプロセスを終了 ”Set exl = Nothing” させないと、プロセスが残り続けてしまう。(プロセスが残っている場合、タスクマネージャから確認出来ます)

4.ExecuteExcel4Macroを利用したデータ取得(VBA)

古いExcelで利用されていた「Excel4.0」マクロを利用して別ブックを参照する方法です。「Excel4.0」マクロの利用はセキュリティの観点から推奨されていませんが、処理速度を速める必要がある場合はこちらが有効です。

使い方

①詳細設定より「Excel4.0」マクロの有効化、ホーム画面の「その他…」から、オプション項目を開き「トラスト センターの設定」を選択します

「トラスト センター」の「マクロの設定」より「VBA マクロを有効Excel 4.0マクロを有効にする」にチェックを入れます

②「開発」メニューの中の「Visual Basic」項目を選択し、エディター画面を表示
(先ほどの”2.『Workbooksを利用したデータ取得(VBA)』”と同様に、ホーム画面の「その他…」から、オプション項目を開き「開発メニュー」表示させます)

③Visual Basic Editorを開いたら、標準モジュールを挿入

④標準モジュールに以下のsubファンクションを記述
Sub TEST3()
Dim L, I As Long

For I = 1 To 5
Cells(1, I) = ExecuteExcel4Macro("'C:\workspace\[ExcelTest.xlsx]sheet1'!R1C" & I)
‘C:\workspace\は参照先Excelブックが置かれているパスを指定。[ExcelTest.xlsx]は [別ブックのファイル名]を指定
Next I
End Sub

⑤「sub/ユーザーフォーム」を実行

「sub/ユーザーフォーム」を実行すると、1.『数式で参照先セルを指定』の④と同じ結果になります

参照する際に別ブックを開かないため処理が早くなります。ただ30年も前のマクロの為、互換性がいつまで保たれるかわからないため、新しい環境では動かなくなるリスクがあります。

5.こんな方法もあります！

上記のような仕組みを構築することで、ブック間の連携は可能です。しかし元の値が削除されてしまい無効な値（#REF!）が表示されたりとファイル間連携の問題や(になりやすい)、VBAのスキル、属人化、Excelバージョンにおける非互換など今後の運用に課題が残ります。

弊社ではそんなお客様の声を基に、Excelから必要な情報を簡単に抽出できる業務効率化サービスをご用意しました。既に業務でご利用のExcelをベースにご利用いただけるサービスとなっております。

業務効率化サービス「サクッと収集」
業務でご利用のExcelをテンプレートとし、複数のExcelファイルの情報を集めることが可能です。
「サクッと収集」は1ヶ月単位でご利用いただけるサービスとなっております。詳細は以下のバナーより。