PostgresSQLのデータベースから、関連する全てのデータをExcel抽出してほしいという依頼がありました。
そのデータベースの定義書を確認してみるとテーブル数が400超え。
その中から、一つのマスタ項目に紐づくすべてのデータを検索してということだったので、DBのツールを使って全ての関連テーブルを調べるのも面倒です。
どうせExcelで欲しいというのなら、直接セルに出力するのが早いんじゃないかということでVBAで作ることにしました。
ただ、最近はVBAから直接PostgresSQLに接続するというようなことをしていなかったので思い出しながらやってみたということで、とりあえず備忘録です。
目次
ODBCドライバインストール
まずは、自分がインストールしているPostgresSQLのバージョンに対応したODBCドライバをインストールします。
Windowsの場合は、こちらのページからダウンロードします。
https://www.postgresql.org/ftp/odbc/versions/msi/
64bitの場合は、たぶん32bit版もインストールする必要があると思いますので
psqlodbc_XX_XX_XXXX-x64.zip
psqlodbc_XX_XX_XXXX-x86.zip
を両方ダウンロードします。
ダウンロードしたZIPファイルを展開するとインストーラが入っていますのでダブルクリックしてインストールを行います。
ODBC設定
前述したとおり64bitの場合は2つのODBCを設定する必要があります。（たぶん）
設定するのは
64bit版
C:\Windows\System32\odbcad32.exe
32bit版
C:\Windows\SysWOW64\odbcad32.exe
画面は同じですが、64bit版、32bit版それぞれ別にしか設定できません。
まず「C:\Windows\System32\odbcad32.exe」を起動して「システムDSN」タブをクリックします。
「追加」をクリックします。
「PostgreSQL Unicode」を選択し「完了」をクリックします。
必要な情報を入力し「テスト」をクリックします。
「Connection successful」が表示されれば成功です。
「OK」をクリックした後元の画面で「保存」クリックします。
「PostgreSQL35W」が追加されていることを確認します。
これと同じことを「C:\Windows\SysWOW64\odbcad32.exe」でも行います。
最後に「64ビット」「32ビット」の両方が追加されていることを確認します。
Excel VBAの設定
Excel側の設定です。
まずはVBAの画面を開きます。
メニューバーの「ツール」→「参照設定」をクリックします。
「Microsoft ActiveX Data Object 2.8 Library」を選択して「OK」をクリックします。
VBAでPostgreSQLに接続
Sub dbconnTest()
Dim rs As ADODB.Recordset
Dim sql As String
Dim i As Integer
Dim rcnt As Integer
'接続情報を定義
Set cnn = New ADODB.Connection
cnn.Open "Provider=MSDASQL;Driver=PostgreSQL Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=postgres"
'レコードセット定義
Set rs = New ADODB.Recordset
'SQL記述
sql = "SELECT * FROM testtbl"
'レコードセットに接続情報とSQLをセット
rs.ActiveConnection = cnn
rs.Source = sql
rs.Open
'今回はフィールドの数もわからない前提で全て出力するのでFields.Countを保存
cnt = rs.Fields.Count
rcnt = 2
Do Until rs.EOF
'全データを抽出するのでFieldの数だけループ
For i = 0 To cnt - 1
Cells(rcnt, i + 1).Value = rs.Fields(i)
Next
rcnt = rcnt + 1
rs.MoveNext
Loop
'すべて読み終わったら閉じる
Set rs = Nothing
Set cnn = Nothing
End Sub
まとめ
基本的には、PostgreSQL以外にも同じ方法で接続できると思います。
今回は、久しぶりにやったのとPCが変わっていたのでODBCドライバからのインストールが必要でした。
最近はVBAからDBに接続することもなかったので、思い出しながらやってみましたが、特に難しいこともないし、Excelにそのままデータを出力することもできるので便利ですね。
ただ、C#やVBならDLLを読み込んで記述するだけで済みますが、VBAではODBCの設定などが必要になってきます。
そこだけちょっと面倒です。
あと注意が必要だとすれば、保存するときに「xlsm」にすること！
これを忘れると、記述した処理がすべて消えてしまいます。
昔は拡張子が「xls」だけだったので気にしていませんでしたが、マクロ付きのExcelファイルが「xlsm」になってから何度かやらかしました。
もしVBAを使用する場合は、保存するときは必ず「xlsm」にすることを忘れないようにしましょう！
