Excel VBAからODBCを使ってデータを簡単に取得する
業務アプリケーションのデータベースから ODBC を使ってデータを取得できるようになったので、Excel VBAでの ODBC 利用方法を書いてみたいと思います。
ADOを使う方法
Google 先生に利用方法を聞くと ADO を使う方法を解説しているページを紹介してくれます。例えば次のようなページです。
ADO(ActiveX Data Objects)の使い方の要点 エクセルの神髄
私も最初はこのようなサイトを参考にして ADODB オブジェクトを使ってデータベースからデータを取得していました。
しかし、データベースで使っている Unicode の文字列(簡体字)が化けてしまうという問題が生じ、DSN の設定を変えたり色々試しましたが解決せず諦めました。
なお、データベースはマイナーなIntersystems のCacheです。
クエリを使う方法
Excel のクエリを使ったところ、文字化けしなかったので、現在はこちらの方法を使っています。
データベース
次のようなアクセスのデータベースがあるとします。
テーブル名は「商品マスター」
カラム名は、ID, 商品名, 金額
保存してあるファイルは C:\Test\SampleDB.accdb
DSNの用意
このファイルにアクセスするための DSN を作成します。(作成方法は後述)
VBAのコード
次のコードを用意します。(ご自分の環境で実行する場合は適切なDSN名を指定して、SQL文も適切なものを指定します。Destination にはテーブルの左上の位置を指定します)
Option Explicit
'テーブル作成 データシートに最初のテーブルを作るときだけ使用するPrivate Sub uCreateTableWithQuery() Dim uWS As Worksheet Dim uList As ListObject Set uWS = ActiveSheet Set uList = uWS.ListObjects.Add( _ SourceType:=xlSrcQuery, Source:="ODBC;DSN=NK Test", Destination:=Range("A3")) uList.DisplayName = "TBL_" & uWS.NameEnd Sub
'クエリーテーブル更新Public Sub gRefleshQueryTable() Dim uList As ListObject Dim uSQL As String Set uList = ActiveSheet.ListObjects("TBL_" & ActiveSheet.Name) uSQL = "SELECT * FROM 商品マスター" With uList.QueryTable .CommandText = uSQL .AdjustColumnWidth = False '列幅調整しない Debug.Print uSQL 'ODBCエラー調査用 .Refresh BackgroundQuery:=False End WithEnd Sub
注意: この例ではテーブル名は「TBL_シート名」になります。重複に注意しましょう。
実行
データを読み込みたいシートを開き、uCreateTableWithQuery プロシージャーを実行します。これは最初に一回実行するだけです。
するとアクティブシートに次のようにテーブルが追加されます。
次に、 gRefleshQueryTable プロシージャーを実行します。
すると、上のAccessデータベースの内容が読み込まれます。
gRefleshQueryTable を実行するたびに、Access からデータが読み込まれます。
次のように更新ボタンを用意して gRefleshQueryTable を呼び出すようにすれば、簡単にデータのリフレッシュが可能になります。
クエリの破棄
作ったテーブルを破棄したい場合にはテーブル範囲のセルを選択して、「すべてクリア」を実行します。関連付けられたクエリを削除するか聞かれるので「はい」を選択すれば、クエリごと削除できます。
かなりSQLやテーブルをいじった場合には、列並びや更新がおかしくなり、破棄が必要になるときがあります。
uCreateTableWithQuery を実行すれば簡単にテーブルを再作成できるので、うまく行かなければ削除しましょう。
(先日もサブクエリを使ったSQLを色々試すうちにORDER BYが効かなくなりしばし悩みましたが、テーブルを削除したら直りました。データベースで同じSQLを直接実行すると問題なくソートされていました)
ただし、当然ですが書式等は設定し直しになります。
クエリテーブルを使うメリット
クエリテーブルを使うとSQL文を作成するだけで、上記のように非常に短いコードでデータを読み込むことができます。
SQLに列を追加すれば、自動的にテーブルも拡張されます。
テーブル列の並びを変えてもある程度は大丈夫です。次の例では金額と商品名の並びを変えて更新しています。きちんと後から追加した Nintendo64 が読み込まれています。
また、Excel の内部コードでテーブルに書き込むため、おそらく VBA から一行一行セルにデータを書き込むよりも速いのではないかと思います。
クエリテーブルを使うデメリット
テーブルが必要です。
テーブルの置き場所を考えなくてはいけないのが面倒です。
応用
条件指定を加えてみます。
B1セルに"_商品名"という名前を付けてあります。
次のようにコードを変更し、_商品名セルがブランクでなければ where 句を追加します。
'クエリーテーブル更新PS5を入力して実行した結果です。
このように、シートに条件を用意すればユーザーが簡単に絞り込みを行えるようになります。
もちろん、テーブルのフィルターも使えるので、実際にはもっと複雑な条件の指定の仕方が多いと思います。
ユーザーDSNの追加方法
参考までに Access のファイルに対するユーザー DSN の追加方法を書いておきます。
ユーザー DSN はユーザー毎の DSN です。誰がログインしても使えるようにしたいのであれば、システムDSNを使用します。その場合は管理者権限が必要です。
- コントロールパネルを開き「管理ツール」を開き、ODBC Data Sources(32-bit)を開きます。(アプリが 64bit なら ODBC データソース 64bit を選択します)
- 「追加」ボタンを押します。
- Microsoft Access Driver (*.mdb, *.accdb) を選択して「完了」を押します。
- データソースに後から判別できる分かりやすい名前を入れ、「選択」ボタンを押します。
- Access のデータファイルを選択して、「OK」ボタンを押し、元のウィンドウで再度「OK」を押します。
- データソースが追加されているはずです。
コメント
コメントを投稿
間違いがあればコメントを頂けるとありがたいです。