Excel(VBA)からPostgreSQLにODBC接続

Tweet

Check

動作環境

Windows 7 64bit

Excel 2013 32bit

PostgreSQL 9.0.13

PostgreSQL用ODBCドライバのインストール

http://www.postgresql.org/ftp/odbc/versions/msi/

の一覧にある

psqlodbc_09_03_0300-x64-1.zip

のようなファイル名は、「_09_03_」はPostgreSQL 9.3に対応しているということ。

「-x64」が付いているものが64bit版。

Excelが32bitなので、ドライバも32bitを使うことにした。

psqlodbc_09_03_0300-1.zip

ダウンロードしたファイルを解凍して「psqlodbc.msi」を起動し、インストールする。

インストールは特に難しいところはない。

ODBC データソース アドミニストレータ起動と設定

Windows 7 64bitに32bit版をインストールした場合、下記を直接起動する。

C:\Windows\SysWOW64\odbcad32.exe

参考記事によると、

OSと同じbit版をインストールした場合は、下記の手順で起動できるとのこと。

「コントロールパネル→システムとセキュリティ→管理ツール」で管理ツールを起動し、「データソース（ODBC）」をダブルクリックするとODBCの「ODBC データソース アドミニストレータ」のダイアログを起動することができます。

起動したら「追加」をクリック。

「PostgreSQL Unicode」を選択し、「完了」をクリック。

データベースに接続するのに必要な情報(サーバ名・データベース名など)は

VBAで指定できるのでそのまま保存する。

VBAで接続テスト

ExcelからVBAエディタを起動し、ツール > 参照設定 を開く。

Microsoft ActiveX Data Objects x.x Libraryにチェックが入っていなければチェックをいれる。

今回はMicrosoft ActiveX Data Objects 6.1 Libraryにチェックをいれた。

下記プログラムを実行すると、データベース名hogehogeのテーブルprefecturesのデータがシートに表示される。

Sub Macro1()
  Dim myCon As ADODB.Connection

  Set myCon = New ADODB.Connection
  myCon.Open "Provider=MSDASQL; DSN=PostgreSQL35W;DATABASE=hogehoge;SERVER=192.168.1.10;PORT=5432;UID=postgres;;SSLmode=disable"

  Set myRS = New ADODB.Recordset

  With myRS
    .ActiveConnection = myCon
    .Source = "SELECT * FROM prefectures"
    .Open
  End With

  For i = 1 To myRS.Fields.Count 'フィールド名を取り込む
    Cells(1, i).Value = myRS.Fields(i - 1).Name
  Next

  Range("A2").CopyFromRecordset myRS 'データ書き出し

  myRS.Close
  Set myRS = Nothing

  myCon.Close
  Set myCon = Nothing

End Sub

5行目のところで、

DSNにODBC データソース アドミニストレータで保存したデータソース名「PostgreSQL35W」を指定し、

さらにデータベース名・サーバアドレス・ポート番号・ユーザ名を指定。

今回はパスワードなしで接続している。

参考

ExcelからPostgreSQLにODBC接続してピボットテーブルを作成する

Excel＋ＡＤＯでpostgreSQLにアクセス