次の方法で共有

Office Space: ADO を使用して Excel ワークシートにクエリを実行する

  • [アーティクル]

Office Space

Office Space へようこそ。Office Space は、Microsoft® Office アプリケーションのスクリプト作成に関するヒントとテクニックを紹介するコラムです。毎週火曜日と木曜日に新しいヒントを掲載します。過去のヒントについては、Office Space アーカイブを参照してください。Microsoft Office でのスクリプト作成について質問がある場合は、scripter@microsoft.com (英語のみ) までお送りください。すべての質問に回答することはできないかもしれませんが、可能な限り対応いたします。

ADO を使用して Excel ワークシートにクエリを実行する

約 1 か月前、CBS は、人気ホーム コメディ "Everybody Loves Raymond" の最終回を放送しました。この番組は、Scripting Guys のうち少なくとも 1 人はまったく見たこともないのに、数百年間の視聴率で第 1 位になりました (もちろん、文化的教養が必ずしもこの Scripting Guy の強みではありません。Scripting Guy は、"American Idol" も "Survivor" も見たことがなく、どちらが Nelly で、どちらが P Diddy か、まったくわかりません)。"Raymond" が終わり、CBS はスケジュールを埋めることにむなしさを感じていますが、他の世界にも埋めなければならない空き状況があります。"今" 私たちが本当に好きにならなくてはならないのは誰でしょうか。

個人的には、そのむなしさを埋める候補として Microsoft Excel をお勧めします。いずれにせよ、Excel を嫌いなわけはないでしょう。予算、決算、およびその他財務関係の処理に役立つような従来のスプレッドシート プログラムが必要でしょうか。Excel ならできます。図式やグラフの作成が必要でしょうか。Excel ならできます。組織図の作成や統計の計算はいかがでしょうか。Excel は皆さんを支援します。Excel なら "何でも" できます。

もちろん、信用できない方もいるでしょう。「本当に Excel なら "何でも" できるの」と思っていませんか。「じゃ、Excel は、フラット ファイル データベースのように機能できるの。ADO を使用して Excel ワークシートの情報にアクセスできるの。SQL クエリを Excel に送信して情報を返すことはできるの」などと考えていませんか。

こう考えてみましょう。Office Space コラムはインターネット上で公開されています。言うまでもなく、100% 真実でなかったら、インターネット上で何かを公開することはできません。当然ながら、Excel は、ADO クエリや SQL クエリを使用してアクセスできるフラット ファイル データベースとして機能できます。今回のコラムでは、そのための方法を説明しましょう。

簡単なワークシートを用意して、ADO (ActiveX Data Objects) を使用したデータ アクセス方法について説明しましょう。C:\Scripts\Test.xls という名前の基礎となるワークシートを次に示します。このワークシートは、Name と Number という 2 つの列で構成されます。Excel ワークシートに対してデータベース クエリをうまく機能させるには、ワークシートがこれと同じ形式で設定されていることを確認します。つまり、1 行目をヘッダー行にして、データ自体は 2 行目から始め、すべての行と列を省略しません。コーディングを簡単にするために、ヘッダーに空白を含めないようにします。たとえば、"SocialSecurityNumber" のような列ヘッダーを使用し、"Social Security Number" のような列ヘッダーは使用しません。これだけでとても簡単になります (ちなみに、このことは、Excel だけでなく、"すべての" 種類のデータベースを使用する場合に当てはまります)。

ワークシートは次のようになります。

Microsoft Excel

もちろん、このサンプル ワークシートの作成には多く時間と労力が必要でした。でも、すべて私たちからのサービスです。

ただし、ここで次の大きな疑問が生じます。ADO を使用してどのようにこのデータにアクセスするのでしょう。1 つは、次のようにスクリプトを使用する方法です。

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 

objRecordset.Open "Select * FROM [Sheet1$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop

おそらく、スクリプトの前半部分では、いくつか定数を定義し、2 つのオブジェクト (ADODB.Connection と ADODB.Recordset) を作成していることを説明するぐらいでしょう。これらのオブジェクトは、データに接続したり、データ ソースからデータを取得するのに必要です。これらの大部分は、ADO スクリプト内で手を加えないでそのまま使用する定型コードです。定型コードなので、スクリプトの前半部分については詳しく説明しません。詳細については、「Microsoft Windows 2000 Scripting Guide」の「Working with Databases」(英語) を参照してください。

では、次のコード部分の説明から始めましょう。実際には、この部分で Excel ワークシートへの接続を開始します。

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"

これもまた、大部分が定型コードです。注意するのは、"Data Source" の部分のみです。この部分では、使用するワークシートへのパスを指定します。

注 : ワークシートへのパスに空白が含まれていたらどうなるでしょう。空白は、テキスト ファイルを操作する ADO スクリプトで問題になる可能性があります。詳細については、Hey, Scripting Guy! のコラムを参照してください。この場合は、まったく問題がないので、次のようにファイル パス全体を空白なども一緒に記述します。

Data Source=C:\Scripts\My Spreadsheet.xls

ちなみに、"Excel 8.0" の部分を、コンピュータで実行している Excel のバージョンに変更するのは我慢してください。この場合、Excel 8.0 は実行しているバージョンの Excel ではなく、Excel へのアクセスに使用される ADO プロバイダを指します。プロバイダを Excel 8.0 のままにしておくことで、すべてがうまくいきます。

話のついでですが、コードの "HDR=Yes" は単にワークシートにヘッダー行があることを示しています。つまり、ワークシートにヘッダー行がなかったら、HDR には No を設定します。ただし、常にヘッダー行が必要であると説明したことと、Scripting Guys は有言実行であることから、これに関しては議論の余地があります。

データ ソースへの接続確立後は、SQL クエリを使用して、そのデータ ソースに格納されている情報を取得できます。ワークシート内のすべての行で構成されるレコードセットを返すには、次のコードを使用します。

objRecordset.Open "Select * FROM [Sheet1$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

ここにも、ADO の定型コードが多数あります。ここでは、実際の SQL クエリ部分にのみ注目します。

Select * FROM [Sheet1$]

これは標準的な SQL クエリです。このクエリでは、データベース (ワークシート) 内のすべてのフィールド (列) を選択します。データベースに接続する場合に各テーブル名を指定するのと同じように、クエリでは各ワークシートの名前を指定することに注意してください。また、ワークシート名が角かっこで囲まれていること、および実際の名前 Sheet1 に $ が付加されていることにも注意してください。ワークシート内のデータにアクセスするために独自の ADO スクリプトを作成する場合は、この両方に注意してください。

返されるレコードセットは、SQL Server などへの接続を確立した場合に返されるのとまったく同じ内容です。そのため、次のコード行を使用すると、ワークシートの各行に対応する、レコードセットの各レコードの Name と Number のレポートを簡単に返すことができます。

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop

このスクリプトを実行すると、次のようなレポートが返されます。

A 1
B 1
C 2
D 2
E 1
F 1

すばらしいですが、少し疑問があります。結局、従来の Excel スクリプトを使用して、これと同じ情報を取得できないのでしょうか。もちろん可能です。つまり、この場合、実際には ADO を使用する理由は特にありません。

それでは、なぜ、わざわざこのコラムを書いたのでしょう (ついでながら、これは私たちがコラムを書くときに必ず起きる疑問です)。正直に言いますと、ワークシートのすべての行をエコー バックするだけなら、ADO を使用しても意味がありません (ただし、Excel スクリプトを作成するよりも ADO スクリプトを作成する方が慣れている場合は除きます)。ところが、Number が 2 の行だけをエコー バックする場合はどうでしょう。標準的な Excel スクリプトを使用すると、各行を検証して Number 列が 2 であるかどうかを確認し、その値に基づいて行をエコーする (またはエコーしない) 必要があります。これは必ずしも難しくありませんが、特に、複数の列の値を参照する必要がある場合は、扱いにくくなることがあります。たとえば、管理スタッフという肩書きで経理部門に勤めているすべてのユーザーを探す場合などがあります。

それに対して、ADO を使用すると、ワークシートの各行を個別に検証する必要なく、これと同じ情報を返すことができます。実際に、ここで説明したのとほとんど同じスクリプトを使用できます。必要なのは、SQL クエリの変更のみです。Number が 2 の行だけが必要なので、次のような SQL クエリを使用します。

objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

これだけで、上記のクエリは次のデータを返します。

C 2
D 2

なぜ ADO を使用するのでしょう。その答えは、SQL クエリを使用すると、ワークシート内の情報のサブセットを簡単に返すことができるからです。そういうわけで、Excel ファイルにアクセスするには ADO を使用することをお勧めします。

Number 列が 2 の行だけを返す完全なスクリプトは次のようになります。

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 

objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop

先に言っておきますが、ADO を使用してできることは、単にデータを取得する以外にもあります。今後のコラムでは、ADO を使用してワークシートにデータを "書き込む" 方法を紹介します。

これで、誰もが Excel を好きになっても不思議ではないでしょう。