コンピューター

ExcelのVBAでPostgreSQL(データベース)に接続

投稿日:

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」にすることを忘れないようにしましょう!

 

-コンピューター

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

テレワーク・オンライン飲み会には光回線?「おてがる光」なら安くて縛りなし!「NURO光」も安い!

新型コロナウイルスの影響で、ZoomやSkypeなどを使用したテレワークやオンライン飲み会が増えていますね。   ただし、固定回線を引いていないため、モバイルWiFiやスマホのテザリングを使 …

データ全消失?HPEのサーバー向けSAS SSDで復旧も不可!

タイトルについてTwitterでトレンドになっているので何なんだろうと 調べてみたら、これとんでもないね! 目次1 記事2 稼働時間が・・・3 まとめ 記事 Hewlett Packard Enter …

no image

Windows10への無償アップグレードはまだ可能?あと5日でサポート終了

Windows7のサポート終了まで残り日数が僅か5日になりました。 Windows 10へのアップグレードにアップグレードしていない人はいないでしょうか? Windows7のサポート期限は2020年1 …

no image

Zoom (ズーム) を使ったオンライン飲み会の方法は?無料では人数や時間制限がある?

最近「Zoom(ズーム)」などのWeb会議・ビデオ会議に利用できるミーティングツールを使用してのオンライン飲み会が流行っています。   その「Zoom(ズーム)」についてですが、無料で使用する場合、人 …

no image

全国の小中学生に配布するPCがが27万8000円!酷すぎるスペックとは?

「全国の小中学生」がトレンドのなっていたので何かと思えば、全国の小中学生に配布されるPCが酷すぎるということらしいです。 スペックと値段をみてみると確かに何でこんなものが、27万8000円もするの?と …