【Excel VBA】pgAdminのデータベースに接続して、テーブルを取得する方法

はじめに

こんにちはIMMRです。

Excel VBAで

下記のようなpgAdminで作成したデータベースを参照する方法を説明します。

1.pgAdminで作成したデータベースを参照する手順

Excel VBAでpgAdminのデータベース・テーブルを参照し、取得したい場合、下記の手順で行います。

　①データベースに接続するための「PostgreSQLのODBC ドライバ」のセットアップ

　②データベースに接続するための「ODBCデータソース」の登録

　③データベースに接続するための「VBA projectの詳細」の設定

　④データベースに接続およびテーブルを取得するサンプルコードの記載

の手順で行っていく必要がありますので順番に説明していきます。

ちなみにpdAdminのインストールまたは、データベースとテーブルの作成は下記を参照して下さい。

pgAdmin(PostgreSQL)のインストール

データベースとテーブルの作成

①データベースに接続するための「PostgreSQLのODBC ドライバ」

pgAdminのデータベースに接続に接続するには、まずPostgreSQL の ODBC ドライバのセットアップが必要です。

セットアップ用のzipデータを下記URLよりダウンロードを行います。

ODBCドライバセットアップ用zipファイル

ここで注意しないといけないのがExcelのバージョン(bit)です。バージョン(bit)によって異なります。

・Excelが32bitの場合、psqlodbc_11_01_0000-x86.zip

・Excelが64bitの場合、psqlodbc_11_01_0000-x64.zip

ここでは、32bitを例として行っていきます。

ファイル→その他→アカウント→Excelのバージョン情報　で確認出来ます。

バージョンにあったODBCドライバーzipファイルを選択します。　※32bitなので、psqlodbc_11_01_0000-x86.zipを選択しています。

↓

ダウンロードしたzipファイルから「psqlodbc_11_01_0000-x86」を実行します。

↓

psqlodbc_11_01_0000-x86を実行するとセットアップが始まります。

↓

手順に沿っていき、問題がなければセットアップが開始され、Completedとなります。

②データベースに接続するための「ODBCデータソース」

pgAdminのデータベースに接続に接続するには、ODBCデータソースの登録が必要です。

32bitのODBCのデータソースを選択します。　※32bitの場合

※コントロールパネル→システムとセキュリティ→管理ツールで上記画面に行けます。

↓

アドミニストレータが表示されますので「システムDN」のタブを選択します。

↓

「システムDN」のタブ選択後に「追加」を選択します。

↓

ドライバを選択する画面が表示されるので「PostgreSQL Unicode」を選択します。

↓

データソース名は自動で入力されますので、

「サーバー名」「ユーザー名」「パスワード」「Port」を入力します。

↓

上記、入力が完了したら、「テスト」を選択します。

↓

「テスト」を選択して、「Connection successful」が表示されるのを確認して、「OK」を選択します。

※「Connection successful」と表示されない場合は、「ユーザー名」等記入間違えがないか再確認が必要です。

↓

「保存」を選択します。

↓

下記のようにアドミニストレーターが追加されていることを確認します。

③データベースに接続するためのVBA projectの詳細

pgAdminのデータベースに接続に接続するに行うODBCデータソースの登録は、完了しました。

最後に行う設定は、Excel VBA内の「VBA project」の詳細の設定です。

VBA projectの「Microsoft ActiveX Data 2.8 Library」のチェックボックスにチェックを入れます。

VBEを表示→「ツール」タブ→詳細設定を選択します。

④データベースに接続およびテーブルを取得するサンプルコード

pgAdminのデータベースに接続するために「「ODBCデータソース」の登録」と「VBA projectの詳細設定」を行いました。

残るは、プログラムコードの記載です。

ここでは、データベースに接続して、SQLのSELECT文より、指定したテーブルのカラムやレコードを取得します。

サンプルコードは以下になります。

サンプルコード

Sub Test19()

Const dbUserId As String = "postgres"
Const dbPort As String = "5432"
Const dbServer As String = "localhost"
Const dbName As String = "devdb01"
Const dbPassword As String = "password"
Const dbTablename As String = "Member"
Const sheetName As String = "Sheet1"

Dim dbConnect As Object
Dim dbRecordset As Variant
Set dbConnect = CreateObject("ADODB.Connection")
dbConnect.Open "Provider=MSDASQL;Driver=PostgreSQL Unicode;" & _
                "UID=" & dbUserId & ";" & _
                "Port=" & dbPort & ";" & _
                "Server=" & dbServer & ";" & _
                "Database=" & dbName & ";" & _
                "PWD=" & dbPassword

'SQL作成
Dim SQL As String: SQL = "SELECT * from " & Chr(34) & dbTablename & Chr(34)

'SQL実行
Set dbRecordset = New ADODB.Recordset
dbRecordset.Open SQL, dbConnect, adOpenKeyset, adLockOptimistic, adCmdText

'ワークシートの選択
Dim sheet As Worksheet
Set sheet = Worksheets(sheetName)

'ワークシートの初期化
sheet.Cells.Clear

'データベースのカラムとテーブルを取得
dbRecordset.MoveFirst
i = 1
Do Until dbRecordset.EOF
    For j = 0 To dbRecordset.Fields.Count - 1
        'カラムを取得
        If i = 1 Then
            sheet.Cells(i, j + 1) = dbRecordset(j).Name
        End If
        'テーブルを取得
        sheet.Cells(i + 1, j + 1) = dbRecordset(j).Value
    Next j
    i = i + 1
    dbRecordset.MoveNext
Loop

'幅の自動調整およびワークシートの表示
sheet.Activate
ActiveSheet.Range(Columns(1), Columns(dbRecordset.Fields.Count)).AutoFit

' データベースを閉じる
dbRecordset.Close
dbConnect.Close

End Sub

上記、プログラムを使用することでデータベースに接続して、SQLのSELECT文より、指定したテーブルのカラムやレコードを取得

することができますが、

1部分だけは、変更する必要があります。

それは、下記箇所です。

プログラム変更箇所

・Const dbUserId As String = "postgres"

　ユーザー名を指定します。

・Const dbPort As String = "5432"

　データベースのポート番号です。※PostgreSQLのデフォルトポートであれば5432

・Const dbServer As String = "localhost"

　接続しようとするデータベースのIP または ホスト名　※個人のPCならlocalhost

・Const dbName As String = "devdb01"

　接続しようとするデータベースの名前です。

・Const dbPassword As String = "password"

　接続しようとするデータベースのパスワードです。

・Const dbTablename As String = "Member"

　接続しようとするデータベース内のテーブル名です。

・Const sheetName As String = "Sheet1"

　データベースを接続するのに必要な個所ではないです。

　ここでは、テーブル内のデータを出力する場所(シート)です。　

上記、箇所を修正すれば、サンプルプログラムを使用して、実行することでデータベースに接続出来ます。

後は、必要に応じて、下記部分の修正を行います。

指定したテーブルのデータを取得し、Excelのセルに出力していますが

どのように出力しているかを記載しています。

1行目にカラム

2行目以降にレコードのデータ　です。

2.データベースに接続およびテーブルを取得するサンプルコードの実行

では、上記、サンプルプログラムを使用して、

pgAdminのデータベースに接続して、テーブルを取得出来るか確認します。

Sheet1内のセルに値は何も入力されていません。そして、プログラムを実行します。

↓

下記のようにSheet1のセルにデータベースの値が記載されました。

実際のデータベースと比較しても正しく出力されていることがわかります。

データベース名(赤枠)のテーブル名(緑枠)を取得しています。

カラム(青枠)の個数や名前を正しく表示されています。

レコード(オレンジ枠)も全て12件出力されています。

データベース名(赤枠)とテーブル名(緑枠)が正しく設定され、取得出来ていることがわかります。DBC ドライバ」のセットアップ