Office TANAKA - Excel VBA Tips[ブックを開かないでセルのデータを読む]

このコンテンツは、かなり昔に書きました。正確な時期は覚えていませんが、ワークシートの大きさが65,536行より小さいブックに関して触れていましたので、おそらく1998年前後に書いたのではないかと。22年前かぁ…ｗ内容もちょっとアレですし、書き足したいこともありますので、全面的に書き直します。ちなみに今は、2020年9月です。

なお、本稿と"ほぼ"同じ内容をYouTubeの動画で公開しています。本稿とは違い、100個のブックから読み込んで所要時間を計測していますので、興味のある方はご覧ください。本稿は、1つのブックから読み込むという基本的な方法と、動画では伝えきれなかった"制約"に関して詳しく解説します。

Excel 4.0マクロで読み込む

原則としてExcelのVBAは、Excel上に読み込んだブックを扱います。Excel上に開いていないブックは、原則として操作できません。この原則は、しっかり認識してください。本稿では「開いていないブック」からデータを読み込む方法を解説しますが、後述するように、いろいろと制約もあります。さて、開いていないブックからセルのデータを読み込むには、いろいろな方法がありますが、ここでは2つの方法を詳解します。ひとつは、Excel 4.0時代のマクロを使うやり方です。

はじめてExcelにVBAが実装されたのは、1994年に発売されたExcel 5.0です。その前のExcel 4.0には、VBAではないマクロ言語がありました。VBAなどという立派な名前はありません。単なる"マクロ言語"です。ちなみに、VBAとはまったく異なっていて、ワークシートのようなところで、セルのようなものに、ワークシート関数のような命令を記述するような仕組みでした。Excel 4.0マクロは現在でも使えますが、使う人は(おそらく)誰もいないでしょう。4.0マクロとVBAは、まったく別のマクロ言語ですが、VBAには、その4.0マクロを実行する命令が用意されています。それが、ExecuteExcel4Macroメソッドです。4.0マクロの詳しい解説は割愛しますが、次のようにすると、開いていないブックのセルデータを読み込めます。

Sub Macro1()
    MsgBox ExecuteExcel4Macro("'C:\Work\[Sample.xlsx]Sheet1'!R2C1")
End Sub

上記のコードは、C:\Workフォルダに保存されているSample.xlsxブックの、Sheet1のセルA2を読み込めという命令です。ちなみに、Sample.xlsxのSheet1は次のようになっています。

さて、上記マクロMacro1を実行すると、次のような結果になります。

「44075」というのは、セルA2に入力されている「2020/9/1」のシリアル値です。ExecuteExcel4Macroを使ってセルの値を読み込むとき、セルの指定は上記のように「R1C1形式」で指定しなければなりません。「A1形式」は使えませんので注意が必要です。この仕組みを使って、Sample.xlsxの全データを読み込んでセルに代入してみましょう。なお、Sample.xlsxのSheet1には、セルE123までデータが入力されているとします。

Sub Macro2()
    Dim i As Long, j As Long
    For i = 2 To 123
        For j = 1 To 5
            Cells(i, j) = ExecuteExcel4Macro("'C:\Work\[Sample.xlsx]Sheet1'!R" & i & "C" & j)
        Next j
    Next i
End Sub

とりあえず代入できました。A列にはあらかじめ、日付の表示形式を設定しておけばいいでしょう。このやり方の難点は、ほとんどの人が使い慣れていない「R1C1形式」でセルを指定しなければいけないということと、けっこう時間がかかるということです。今回は、5列×123行＝615個のセルでしたが、大量に読み込むときには、それなりの覚悟が必要です。また、取得できるのは純粋なセルの値ですから、日付や桁区切りなどの表示形式は反映されません。

セルに計算式を代入する方法

セルの中に「=A1」のような参照式を代入すると、セルA1の値を参照できます。もちろん「=Sheet1!A1」とシート名を付ければ、アクティブシートではない別シートのセルも参照可能です。さらに、Excel上で同時に開いているブックでしたら「=[Sample.xlsx]Sheet1!A1」と書くことで参照できます。これは、ご存じですよね。

では、このとき、Excel上に開いていた「Sample.xlsx」を閉じたらどうなるでしょう。セルに代入した参照式「=[Sample.xlsx]Sheet1!A1」は自動的に「='C:\Work\[Sample.xlsx]Sheet1'!A1」のように変化します。そう！Excelは、開いていないブックのセルを、参照式で参照することができるんです。

今回のテーマは、開いていないブックからセルのデータを読み込むことです。だったらセルに、そのブックへの参照式を入力してやればいいんです。

Sub Macro3()
    Range("A2") = "='C:\Work\[Sample.xlsx]Sheet1'!A2"
End Sub

A列には、あらかじめ日付の表示形式を設定しています。

今回のSample.xlsxでは、読み込みたいデータの範囲が「セルA2:E123」と分かっています。なので、同じ大きさのセル範囲に、この数式を代入してやれば、開いていないブックからセルのデータを読み込めます。もちろん、For Nextで1セルずつ読み込むなどという超低速な方法は使いません。ひとかたまりのセル範囲なのですから、複数セルへの一括代入で済みます。

Sub Macro4()
    Range("A2:E123") = "='C:\Work\[Sample.xlsx]Sheet1'!A2"
End Sub

これ、やってみれば分かりますが、Excel 4.0マクロで読み込むのとは、比べものにならないくらい高速です。もし、セルに代入した参照式を、すべて"値"にしたいのでしたら、次のようにすればいいでしょう。

Sub Macro5()
    With Range("A2:E123")
        .Value = "='C:\Work\[Sample.xlsx]Sheet1'!A2"
        .Value = .Value
    End With
End Sub

いろいろな制約

本稿でご紹介したやり方には、いくつかの制約や、難しいポイントなどがあります。まず、4.0マクロ方式ですが、使い慣れないR1C1形式で複数セルを指定しなければならないという点に加え、元データが「空欄セル」だったとき0を返すというルールがあります。

【元データ】

【4.0マクロで読み込んだ結果】

何も値が入力されていない空欄セルと、数値の0という値が入力されているセルは、言うまでもなく意味が違います。「だったら、読み込んだあとで数値の0を空欄("")にしちゃえば？」という発想も危険です。上図のE列をご覧ください。元データのセルE5とセルE9には、数値の0が値として入力されています。しかし、読み込んだ結果では、その0が、もともと入力されていた数値の0なのか、空欄による0なのか区別できませんね。

ふたつめにご紹介した、セルに参照式を入れちゃう方式にも問題があります。上記のところどころに強調の書体で表しましたが、今回は元データの大きさが「E列の123行目まで」と分かっていました。どうですか？実務では、データの大きさがあらかじめ分かっていることなど希でしょう。Sample.xlsxをExcel上に読み込んでしまえば簡単です。どーとでもなります。しかし、Excelに開いていない状態では、元データの大きさを調べる術がありません。

さらに、どちらの方式にしても、正確なシート名があらかじめ分かっていなければなりません。分かっているのなら、いいです。でも、シート名が分からないのでしたらアウトです。Excel上にSample.xlsxを開いちゃえば、シート名を調べることなど簡単です。でも、開いていないブックのシート名を調べる方法はありません。

などのように「開いていないブックからデータを読み込む」には、さまざまな制約などがあります。それらを理解した上でチャレンジしてください。