検索

キーワード


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

  • 公開日:2021-03-03 17:25:40
  • 最終更新日:2021-05-26 09:46:34

はじめに

こんにちはIMMRです。

 

Excel VBAで

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

pdAdminのデータベース


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


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

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

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

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

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

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


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


pgAdmin(PostgreSQL)のインストール


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



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

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


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


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

ODBCダウンロード


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

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

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


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

Excelのバージョン

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


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

ODBCドライバーZipファイル

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

ダウンロードしたODBCzipファイル

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

ODBCセットアップ

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

ODBCセットアップ完了




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

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


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

ODBCのデータソース

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

アドミニストレータが表示されますので「システム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」のチェックボックスにチェックを入れます。

VBAprojectの選択

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 ドライバ」のセットアップ



【著者】

Imamura

今まではExcel VBAの開発をしてきました。
開発を主にしたものや他の業務の効率化を目指して行ったものさまざまです。
現在は、他のプログラミング言語に挑戦して、スキルアップを目指しています。

よく読まれている記事