ExcelにおいてSQLクエリを使ったデータ操作の方法には、VBAからADODBクラスを使う方法と、「データの取得と変換」から'OLE DB'や'Microsoft Query'を使う方法があります。
VBAを使ってSQLクエリを実行する場合、一般的には実行したいクエリごとにプロシージャを作成してから「マクロの実行」からプロシージャを実行する方法で実現されることが多いですが、新しいクエリごとにプロシージャを作り直すのは手間です。
そこでSQLクエリの実行をワークシート関数として実行できるVBAスクリプトを作成してみましたので、その利用方法について解説します。
また、VBAを使わないで「データの取得と変換」機能によってSQLクエリを実行する方法についても、後半で解説をしています。
使用したExcelのバージョンは下記のとおりです。
「Microsoft 365 バージョン 2005 (ビルド 12827.20336 クイック実行)」
https://docs.microsoft.com/ja-jp/officeupdates/current-channel#version-2005-june-09
VBA関数からスピルで結果を取得する方法
作成したVBAスクリプトは下記になります。
https://gist.github.com/rai-suta/9338e633711436231efa9fd3a293d6c6
上記スクリプトを実行させるためには、VBEの参照設定から下記のライブラリ参照を追加する必要があります。1
- Microsoft Scripting Runtime
- Microsoft VBScript Regular Expressions 5.5
- Microsoft ActiveX Data Objects 2.8 Library
Public Function QUERY_OLEDB(source As String, query As String, Optional extendedProperties As String = "HDR=YES;IMEX=1;", Optional showProperty As String = "False")
' source
' ADODB.Connection の取得に使用するデータソース
' query
' ADODB.Recordset の取得に使用するクエリ
' extendedProperties
' ADODB.Connection の取得に使用するExtended Parameters
' - HDR=Yes : 最初の行は列名を含む
' - IMEX=1 : Import mode(1), 使用するISAMドライバによって、下記のレジストリ設定値による動作が異なる
' [Access Connectivity Engine\Engines\Excel] "ImportMixedTypes"="Text"
' [Access Connectivity Engine\Engines\Text] "ImportMixedTypes"="Majority Types"
' showProperty
' - True : クエリ実行時のADODBが持つ情報を出力する
引数は4つありますが、最低限必要な引数は下記の2つです。
- 第一引数 source : ADODB.Connection の取得に使用するデータソースへのパス
- 第二引数 query : ADODB.Recordset の取得に使用するクエリ
ワークシート関数としてセルに入力して使うことができますが、長いパスや複雑なクエリを指定すると、セルの入力文字数制限である255文字にすぐ到達してしまうため、その場合は各パラメータをセル参照にして利用します。
次に、下記３種類のファイル(accdb, csv, xlsx)から、QUERY_OLEDBを使ってインポートする例を説明します。
D:\Work\FEH_00200521_200619140543.accdb
D:\Work\FEH_00200521_200619140543.csv
D:\Work\FEH_00200521_200619140543.xlsx
CSVファイルから読み込む場合
データソースにはCSVファイルが保存されたディレクトリパスを設定し、クエリのFROM句にてCSVファイル名を指定します。
下記の例ではディレクトリパスではなくファイルパスを渡していますが、CSVファイルパスが渡された場合にはQUERY_OLEDBが内部でディレクトリパスに変換してからADODBへパラメータを渡しています。
Accessファイルの場合
データソースにAccessファイルへのパスを設定し、クエリのFROM句にてデータベース内のテーブル名を指定します。
Excelファイルの場合
データソースにExcelブックファイルへのパスを設定し、クエリのFROM句にてワークシート名とセル範囲を$記号でつなげて指定します。
[シート名$A1:Z99]
テーブルのセル範囲がA1から始まる場合には、セル範囲指定を省略可能です。
[シート名$]
また、パスへ空文字列を設定した場合は、自身のブックを参照するクエリを作成するので、同じブック内のシート名をFROM句に設定してクエリを実行することもできます。
再計算を抑止する使い方
QUERY_OLEDBは自身のセル計算が実行されるたびにクエリ処理を実行するため、入力パラメータ変更によって意図しないクエリ処理が実行される可能性があります。
これを防止するためにQUERY_OLEDBは各パラメータを"#A1"または"#R1C1"形式の文字列でセル参照を指定できるようにしています。
文字列で引数を指定した場合は、Excelは他セルへの参照を認識しないため、参照先セルの内容を変更してもセルの再計算は実行されません。
この状態でクエリの再実行を行う場合は、QUERY_OLEDBが入力されたセルをF2キーで編集状態にしてEnterで確定することで、セルの再計算によりクエリを実行できます。
VBA関数のまとめ
このようにVBA関数からスピルでテーブルを取得する方法は、ワークシートの変更のみでクエリを実行できるという手軽さがありますが以下の欠点もあります。
- セルの再計算が意図せずに実行された場合に、再計算前のスピルのデータが失われる可能性がある。
- スピルの結果はテーブル形式に変換することができない。
これら欠点を回避したい場合、別の方法として「データの取得と変換」機能を使ってSQLクエリを実行する方法をおすすめします。
「データの取得と変換」の'OLE DB'を使ってSQLクエリを実行する方法
Excelの「データの取得と変換 - OLE DBから」の機能を使って他のデータソースに対してSQLクエリでデータをインポートすることができます。
この機能を使うにはリボンメニューから機能を選択するもしくは、キーボードからAlt+A P N O Bの順にキーを入力することで、「OLE DBからインポート」ダイアログを開くことができます。
CSVファイルへの接続を作成する場合は、下記のようにパラメータを設定します。1
- 接続文字列：
provider=Microsoft.ACE.OLEDB.16.0;data source=ディレクトリパス;mode=Read;extended properties="Text;HDR=Yes;IMEX=1" - SQLステートメント:
SELECT * FROM [CSVファイル名]
ユーザー名、パスワードを求められる場合、特に設定が無ければユーザー名のみAdminと入力します。
クエリを正常に実行できると、取得されるテーブルのプレビューが表示されます。これをワークシートに読み込むには「読み込み」ボタンのプルダウンメニューから「読み込み先...」を選択します。
新規ワークシートに読み込むように設定して「OK」ボタンを押します。
クエリの実行結果がワークシートに読み込まれると同時に、「クエリと接続」の欄へ新しく作成したクエリをの設定が保存されます。
このとき読み込まれたテーブルは、メニューからクエリを明示的に更新するまでワークシート内に保存しておくことができます。
下記へAccessファイル、Excelファイルへ接続するための設定文字列を列挙します。1
-
接続文字列：
provider=Microsoft.ACE.OLEDB.16.0;data source=Accessファイルパス;mode=Read -
SQLステートメント:
SELECT * FROM [テーブル名] -
接続文字列：
provider=Microsoft.ACE.OLEDB.16.0;data source=Excelファイルパス;mode=Read;extended properties="Excel 12.0;HDR=Yes;IMEX=1" -
SQLステートメント:
SELECT * FROM [ワークシート名$]