ここ最近、「エクセルの神髄」さんが企画するVBA100本ノックについて記事を書いています。
その記事(その1、その2)へのアクセス数も徐々に増えてきているのですが本ブログではExcelマクロに関する記事がこのVBA100本ノックのみとなっています。
それでは折角訪れた方に申し訳ないなと思いExcelマクロに関する記事を少しずつ増やしていこうと思います。
今回はその1つ目としてAccessのDB(拡張子が「accdb」や「mdb」のファイル)をExcelマクロを使って操作する方法について紹介します。
タイトルにある通り、OfficeにAccessが含まれていない場合でも以下の方法で操作が可能です。
AccessのDBを準備
Accessを持っていないとaccdbおよびmdbファイルを作る事はできません。
そこでDBのファイルをこちらで準備しておきましたのでダウンロードして使ってみてください。(zip圧縮してあります)
なお、中身のデータはVBA100本ノックの「VBA100本ノック 9本目：フィルターコピー」のデータになっています。
Excel VBAでAccessのDBからデータを取得
まずはAccessのDBからデータを取得するコードを紹介します。
※ソースコードを貼り付けようとしたのですがエラーが起きてしまいうまく貼り付けられなかったのでソースコード自体を上げておきます。
標準モジュールを追加して上記のソースコードを貼り付けて実行してみてください。(Excelファイルは事前に保存しておき、同じフォルダに「Score.accdb」が存在している必要があります)
「Score.mdb」で試す場合には「FILE_NAME_DB」を「Score.accdb」から「Score.mdb」に変更する必要があります。(上記ソースコードであれば「Score.accdb」をコメントして「Score.mdb」のコメントを解除する事で変更できます)
上記ソースコードではADOを使用しているためライブラリの参照設定が必要です。
参照設定で「Microsoft ActiveX Data Objects x.x Library」にチェックを入れます。(バージョンは最新のバージョンにしましょう)
全て準備が整ったら「test()」関数を実行してみてください。
イミディエイトウィンドウにデータが表示されれば成功です。
詳細解説
それではソースコードの中身について詳細の解説をしていきます。
設定の定数値
関数の最初には各設定用の定数値を記述しています。
「FILE_NAME_DB」はAccessのDBファイルの名前です。
接続するDBの特定に使います。
「PROVIDER_ACCESS」はAccessのDBへ接続する方法です。
AccessのDBへ接続する場合にはこのProviderを使うためこういう物と覚えて問題ないです。
情報収集
コメントで「情報収集」と書いているブロックです。
AccessのDBへ接続するためにProviderとData Sourceの値が必要になるため、その組み立てを行っている部分です。
「ThisWorkbook.Path」でExcelファイルが格納されているフォルダパスが取得できるのでその後ろにAccessのDBファイル名を付ける事でAccessのDBファイルパスを「Data Source」の値としています。(このため、Excelファイルと同じフォルダへの格納が必要になっています)
データベースへ接続
AccessのDBへ接続する準備が出来たので接続します。
今回の接続にはADO(ActiveX Data Objects)を使っています。
変数「adoConn」はADODB.Connection型で生成しており、DBへの操作をしてくれます。
「.Open」にて指定のDBへ接続をしてくれます。
データを取得
DBへ接続できたのでデータを取得します。
データの取得にはSQLを使って取得しました。
「SELECT * FROM tblScore;」は「tblScore」テーブル内のすべてのデータを抽出します。
「.Execute」はSQLを実行し、その結果を戻り値のRecordSetへ代入してくれます。
ソースコードではその中のデータをカンマ区切りでまとめた情報として「Debug.Print」にてイミディエイトウィンドウに出力するようにしています。
RecordSetは使い終わったら「.Close」で閉じて「Nothing」を代入して開放しましょう。
データベースを閉じる(＆解放(破棄))
不要となったデータベースは閉じて開放(破棄)します。
「.Close」でDBへの接続を閉じ、「Nothing」を代入して開放しましょう。
まとめ
今回は単純なDBへの接続とデータの抽出を行いました。
DBへの操作はSQLで行いましたがSQLを使えるという事はいろいろな操作が行えるので今後それらについて説明していきます。
また、今回はADOを使ったDBへの接続を行いましたがDAO(Data Access Object)で接続する方法もあるので機会があればこれについても紹介しようと思います。
コメント