検索

キーワード


目次

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

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

Workteria(ワークテリア)では難易度の高いものから低いものまで、スキルや経験に合わせた案件を多数揃えています。会員登録は無料ですので、ぜひ会員登録してご希望の案件を探してみてください!

フリーランス/正社員のエンジニアとして活躍するには、ご自身のスキルや経験に合わせた仕事を選ぶことが大切です。ご希望の案件がみつからない場合はお気軽にお問い合わせください!ユーザ満足度の高いキャリアコンサルタントが在籍していますので、希望条件や悩み事などなんでもご相談ください。ご希望にピッタリの案件をご紹介させていただきます。

はじめに

こんにちは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

「挑戦心」を忘れずに、新しいことにチャレンジしています。 資格:VBAエキスパート Excel VBA(ベーシック/スタンダード) 等

編集した記事一覧

正社員/フリーランスの方でこのようなお悩みありませんか?

  • 自分に合う案件を定期的に紹介してもらいたい
  • 週2、リモートワークなど自由な働き方をしてみたい
  • 面倒な案件探し・契約周りは任せて仕事に集中したい

そのような方はぜひ、Workteriaサイトをご利用ください!

  • 定期的にご本人に合う高額案件を紹介

  • リモートワークなど自由な働き方ができる案件多数

  • 専属エージェントが契約や請求をトータルサポート

よく読まれている記事