VBAと日々のこぼれ話

  1. VBA

【VBA】ExcelにSQLを発行してデータを取得したい

ExcelにSQLを発行

以前書いたADO接続を利用して、ExcelにSQLを発行してデータ取得する方法です。

例えば下記の表から『インフルエンザワクチン補助の合計金額』を求める場合、このくらいの条件ならエクセル関数のSUMIF関数で簡単に集計できます。

エクセル関数:=SUMIF(C:C,”インフルエンザ”,F:F)

あるいは、『ワクチン毎の補助金額の合計』といった簡単な集計ならピボットテーブル作ってあげても良いかもしれません。

しかし、条件が複雑になってくれば来るほどExcelシートにクエリを発行してあげるほうが楽になる場合があります。

そこで今回の記事のタイトルです。

※表が予防接種の補助額なのは、最近インフルエンザの予防接種を受けてきたので頭に残っていただけで深い意味はありません。

【スポンサーリンク】



サンプルコード

前提事項
・接続データベースは、Excelファイル(.xlsx)
・レジストリの設定は、規定値
・コードを利用するソフトは、Office2007以降(Microsoft.ACE.OLEDB.12.0利用)

(追記)サンプルコードの「IMEX=1;」は後の説明のために記載しています。
サンプルのクエリを発行するには「IMEX=1;」があるとエラーになるので記述を外してください。

【スポンサーリンク】



クエリのFROM句について

Excelの場合、取得したいシート名を[任意のシート名$セル範囲]で定義します。

『$』は省略不可です。
『$』以降に設定がなければシート全体を表します。
シート内の範囲指定を行いたい場合は、『[予防$A1:E20]』というように『$』以降にセルの範囲を設定します。

Connectionオブジェクトの接続情報の設定方法

ADO接続する場合、サンプルコードのようにConnectionオブジェクトのConnectionStringプロパティやOpenメソッドの引数等に接続情報を設定します。

接続情報の厄介な部分として、プロバイダ固有のプロパティもあり、更に接続したいデータベースによって設定内容が全く異なったり微妙に異なったりして、混乱を招きます。

例)Access等へのADO接続は、Excelとプロバイダは同じであるのに「Extended Properties」は使用しません。

3種類の接続情報を記載した理由

技術書やサイトによってこの部分はいくつかのバリエーションがある部分になります。

結果的に同じ接続情報になるのですが、日本語の技術書・サイト(VBA関連)だとこの3パターンをよく見かけるので挙げてみました。

日本の技術書(VBA関連)では②が主流の様です。また①と②は各々の書き換えのように紹介されることが多いですが、接続情報をConnectionオブジェクトへ渡すタイミング等が違います。私の好みは③なのですが、マイナーな部類のようです。

Microsoft Doc では、①と③が例として挙げられています。

(参考)
Creating the Connection String /Microsoft Doc
https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/creating-the-connection-string

余談ですが、ConnectStringへの設定は以下のサイトに沢山サンプルがあるので参考になります。
(英語のサイトです)
https://www.connectionstrings.com/

【スポンサーリンク】



Extended Properties(拡張プロパティ)について

色々と調べた結果、プロバイダ固有(※)でかつ、接続データベースがISAM形式の時のみ使用するプロパティです。パラメータごとに「;」で区切ります。
※JETデータベースエンジン固有

ISAMフォーマット(必須)

接続しようとしている(ISAM形式で管理されている)データベースのISAMフォーマットの設定部分です。

サンプルコード内では「Excel 12.0 Xml」の部分にあたります。

詳細や何を指定するかの一覧表は、下記の記事にまとめてみました。

HDR(オプション)

ヘッダーが存在するかの設定です。

どの行をヘッダにするのかは、接続データベース(接続エンジン)によって設定個所も異なり、規定値が設定されています。(規定値は大体一緒のようです)

Excelでは、HDR=YESにすると規定値では先頭行がフィールド名になります。

HDR=NOとすると列フィールドに自動的に名前が割り当てられます。(「A列,B列・・・」が「F1,F2・・・」)

シートをテーブルとみなしてクエリを発行する作業を行う際には、Excelシートの先頭データ行に見出しを付けて「HDR=YES」とした方がわかりやすいです。

IMEX(オプション)

接続のモードの設定です。

これだけだと正直「使う意味ある?」と思いますが、この設定で接続モードを明示することで別のレジストリの設定項目が有効(優先)になることを狙っています。

どういう時に使うのかというと、データベースに接続(読込)した際にどのようにデータ型を判断しているのかというところから話が始まります。

接続するデータベースはExcelだとしましょう。

ExcelにADO接続した際、データをデータ列毎に8行(読み込む行数はレジストリの設定による。Excelの規定値が8行)読み込み、最も多いデータ型(※)をそのデータ列のデータ型と判断します。

ところが、この方法だとデータが欠けてしまう場合があります。例えば「数値型」として判断されたデータ列に文字型データが設定されている場合、文字型データは読み込めずに「Null扱い」されます。

しかし、往々にしてデータが読み込めないのは困ります。

そんな時に「IMEX=1(インポートモード)」を設定すると、データ型の判断がレジストリの設定に則って行われます。Excelだとレジストリの設定の規定値が「Text」なのでデータに接続(読込)時に全データをテキスト型で読み込み、データが欠けることを防げます。

と、こんな風に使ってあげます。

が「IMEX=1」を指定してデータの欠落を回避するのにも良し悪しがあります。勘の良い方ならお気づきかもしれませんが、こうやって回避してしまうとクエリでSUM等といった数値型を相手にする関数を発行できなくなります。

正直、個人的に「IMEX」については殆ど使ったことがないオプションです。

データ接続前というかデータ作成時にきっちり型を整えておけば気にしなくて良い部分なので、そちらでカバーしてしまう部分です。

なので、この際にと色々調べたので詳細は別記事を現在作成中。

(参考)
Initializing the Microsoft Excel Driver
https://msdn.microsoft.com/en-us/library/office/ff844939.aspx

【スポンサーリンク】



VBAの最近記事

  1. 【VBA】(VBAを始めたばかりの人が陥りがちな落とし穴) Excelシートの最終行を取得…

  2. 【VBA】QRコードを作成したい

  3. 【VBA】CSVを1行ずつ処理したい

  4. Excelシートの最終行を取得したい その1

  5. 【VBA】未だに苦手としていること

関連記事

【スポンサーリンク】




PAGE TOP