SQLでExcelの集計を簡単・楽しく - ADODB.Connectionオブジェクト

ここからは実際にExcelからSQLを使用するコードに沿って、コードの内容を紹介していきます。

ベースとなるコードは下記のとおりです。
今回は「SQLを使うための準備」欄です。

ADODB.Connectionオブジェクト

各種データベースへ接続するためのオブジェクト。
Microsoft ActiveX Data Objects X.X Libraryへの参照設定を行った上で、下記コードでオブジェクトを作成する。

Dim myCON As ADODB.Connection
Set myCON = New ADODB.Connection

メソッド・プロパティ

ConnectionStringプロパティに一括して設定文字列を登録する方法もあるが、個人的にはProviderプロパティ・Propertiesプロパティに分けて入力するほうが理路整然としていて好き。

名称	役割
Providerプロパティ	接続するデータベースに合わせて使用するOLEDBプロバイダを指定する
Propertiesプロパティ	引数に設定する要素を指定することで、各種要素を登録可能

Providerプロパティ

接続するデータベースに合わせて使用するOLEDBプロバイダを指定する。
Excelへ接続する場合は下記のいづれか。

データベース	プロバイダ
Excel 2003以前	Microsoft.Jet.OLEDB.4.0
Excel 2007以降	Microsoft.ACE.OLEDB.12.0

下記のように指定

myCON.Provider = "Microsoft.ACE.OLEDB.12.0"

Propertiesプロパティ

引数に設定する要素を指定することで、各種要素を登録可能。

プロパティ	内容
Data Source	接続するデータベースのフルパスを指定
Extended Properties	Excel固有のプロパティを設定する

下記のように指定

myCON.Properties("Extended Properties")= "Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=16"
myCON.Properties("Data Source") = ThisWorkbook.FullName

Excelの場合は「Data Source」プロパティにSQLで操作したいブックのフルパスを指定する。
フルパスを指定する必要があるので、未保存のブックに対しては使用することができない。

Extended Properties

Excelに接続する際に様々な設定を指定する

ISAM

接続するExcelのバージョンによって設定する。
基本、Accessに対しての翻訳機能であるProvider="Microsoft.ACE.OLEDB"に対して、"Excel"という方言を指定している。

Excelバージョン	ISAM
Excel 2003 以前	Excel 8.0
Excel 2007 以降	Excel 12.0
CSV	Text

HDR

データベースの1行目がヘッダーなのかデータなのかを指定する。

指定	機能
YES	1行目をヘッダーとして扱う
NO	1行目をデータとして扱う

IMEX(IMport EXport mode)

モード	機能
0	Export mode(書き込み)
1	Import mode(読み込み専用)
2	Linked mode (変更/更新 読み取り、変更、変更の保存)。

抽出・集計系のSQLを実行する場合は「1」
更新系のSQLを実行する場合は「0」

MAXSCANROWS

ADOでExcelを操作する場合、各列のデータ型判定を最初の8行で判定する。
MAXSCANROWSを設定することで判定するための行数を変更できる。

Openメソッド

上記までの設定でデータベースへ接続する。

myCon.Open

以下はベースとなるコードの最下段の処理です。

Closeメソッド

データベースへの接続を切断する。
Closeを行わないと、データベースへ接続されたままになる。

Close後はConnectionオブジェクトを開放する必要がある。
開放を行わないと不要なオブジェクトがメモリを圧迫する。
Connection オブジェクトにNothingを代入することで開放される。

myCon.Close
set myCon = nothing

次回は「SQL文の指示を実行」欄を紹介していきます。

www.excellovers.com