Excel VBAでPostgreSQLに接続してデータを取得するには、ODBC（Open Database Connectivity）を使用する方法があります。
PostgreSQLの設定
1.PostgreSQL用のODBCドライバをインストール
まず、PostgreSQL用のODBCドライバをインストールする必要があります。ドライバはPostgreSQLの公式サイトからダウンロードできます。
PostgreSQL: File Browser
サイトに入ってFile Browserの部分で「releases」のフォルダをクリックします。
「REL-〇〇～～」を選択します。〇〇はバージョンになります。
「paqlodbc_x64.msi」を選択します。
ダウンロードが完了したら「paqlodbc_x64.msi」をクリックしてセットアップウィザードを立ち上げます。立ち上げたら「Next」ボタンをクリックします。
利用規約になります。チェックを入れ「Next」ボタンをクリックします。
そのまま「Next」ボタンをクリックします。
インストールを開始します「Install」ボタンをクリックします。
暫くするとインストールが完了します。「Finish」ボタンをクリックします。
2.ODBCデータソースの設定
Windowsの検索欄で「odbc」と入力します。「ODBCデータソース(64ビット)」を開き、新しいシステムDSNを作成します。
システムDSNタブをクリックし、次に追加ボタンをクリックします。
PostgreSQL Unicode(x64)ドライバを選択し、完了ボタンをクリックします。
データソース名に既に「PostgreSQL35W」と入力されています。ここは好きな名前でOKです。ここでの設定は以上になるので「保存」ボタンを押します。
PostgreSQLのVBAコード
ここのコードは例でパスワードなどをハードコーティングしています。基本的にVBAはパスワードをコード内にハードコーディングしないように注意します。
このページの最後にハードコーディング対策としてiniファイルを使用して接続情報を管理する例を載せます。
ODBC接続文字列を設定します。
' ODBC接続文字列を設定します
connStr = "Driver={PostgreSQL Unicode(x64)};Server=localhost;Database=purchasing_management;Uid=testuser;Pwd=testpass;Option=3;"ODBC接続文字列を設定
- Driver={PostgreSQL Unicode(x64)}
DriverにはODBCのドライバーを入力します。 - Server=localhost
サーバー名またはサーバーアドレスを入力します。 - Database＝purchasing_management
データベースを指定します。 - Uid=testuser
PostgreSQLのUser名を入れます。 - Pwd=testpass
PostgreSQLのPasswordを入れます。 - Port=your_port_number
※必要な場合のみ。例: Port=3306 - Option=3: 追加のオプションを指定します。通常はこの設定で問題ありません。
全体のコード
Sub GetPostgreSQLData()
' 変数を定義
Dim conn As Object
Dim rs As Object
Dim connStr As String
Dim sql As String
Dim row As Long
Dim col As Long
' PostgreSQLへの接続文字列
connStr = "Driver={PostgreSQL Unicode(x64)};Server=localhost;Database=purchasing_management;Uid=testuser;Pwd=testpass;Option=3;"
' SQLクエリ
sql = "SELECT * FROM products"
' 接続とレコードセットの作成
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
' 接続を開く
conn.Open connStr
' クエリを実行
rs.Open sql, conn
' 結果をシートに書き込む
row = 1
Do Until rs.EOF
For col = 0 To rs.Fields.Count - 1
Cells(row, col + 1).Value = rs.Fields(col).Value
Next col
rs.MoveNext
row = row + 1
Loop
' 接続とレコードセットを閉じる
rs.Close
conn.Close
' オブジェクトを解放
Set rs = Nothing
Set conn = Nothing
MsgBox "データが正常に取得されました!"
End Subコードの解説
' SQLクエリ
sql = "SELECT * FROM products"sql 変数に実行するSQLクエリを設定します。ここでは、products テーブルの全データを取得するクエリを設定しています。
' ADOオブジェクトを作成します
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")conn 変数に新しいADO接続オブジェクトを作成します。rs 変数に新しいADOレコードセットオブジェクトを作成します。
' データベースに接続します
conn.Open connStrconnStr に基づいてデータベースに接続します。
' SQLクエリを実行し、結果をレコードセットに取得します
rs.Open sql, connsql クエリを実行し、その結果を rs レコードセットに取得します。
' レコードセットの内容をシートに書き込みます
ws.Range("A1").CopyFromRecordset rsrs レコードセットの内容を、Excelシート ws のセル範囲 “A1” から開始して書き込みます。
' リソースを解放します
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothingrs レコードセットを閉じ、リソースを解放します。conn 接続を閉じ、リソースを解放します。rs と conn 変数を Nothing に設定して、オブジェクトを解放します。
ハードコーディングを避ける方法
ハードコーディングとは、コード内に固定の値を直接書き込むことを指します。
先ほどのコードはデータベース接続に必要な情報（ドライバ、サーバー、データベース、ユーザー名、パスワード）がコード内に直接書かれており、変更が必要な場合にはコードを編集する必要があります。
以下は、.iniファイルを使用して接続情報を管理する例です。
dbconfig.ini という名前で、以下の内容のファイルを作成します。(メモ帳などで作成できます。)
VBAコードで.iniファイルを読み込む
[Database]
Driver=PostgreSQL Unicode(x64)
Server=localhost
Database=purchasing_management
User=testuser
Password=testpass
Option=3iniファイルを使用することで、パスワードを直接VBAコードに書くことを避けられますが、.iniファイル自体が暗号化されていないため、完全なセキュリティ対策とは言えません。
より安全な方法として、以下のような対策を検討することをお勧めします。
・ファイルのアクセス権限を適切に設定する
・暗号化を使用して機密情報を保存
・環境変数を利用する
・安全な外部ストレージ（例えば、セキュアなサーバーやクラウドサービス）を利用する
Private Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" ( _
ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
Sub GetDataFromMySQL()
Dim conn As Object
Dim rs As Object
Dim connStr As String
Dim sql As String
Dim ws As Worksheet
Dim iniFile As String
Dim driver As String
Dim server As String
Dim database As String
Dim user As String
Dim password As String
Dim optionStr As String
' .iniファイルのパスを設定します
iniFile = ThisWorkbook.Path & "\dbconfig.ini"
' .iniファイルから設定を読み取ります
driver = GetIniValue("Database", "Driver", iniFile)
server = GetIniValue("Database", "Server", iniFile)
database = GetIniValue("Database", "Database", iniFile)
user = GetIniValue("Database", "User", iniFile)
password = GetIniValue("Database", "Password", iniFile)
optionStr = GetIniValue("Database", "Option", iniFile)
' ODBC接続文字列を設定します
connStr = "Driver={" & driver & "};Server=" & server & ";Database=" & database & ";Uid=" & user & ";Pwd=" & password & ";Option=" & optionStr & ";"
' SQLクエリを設定します
sql = "SELECT * FROM products"
' Excelシートを設定します
Set ws = ThisWorkbook.Sheets("Sheet1")
' ADOオブジェクトを作成します
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
On Error GoTo ErrorHandler
' データベースに接続します
conn.Open connStr
' SQLクエリを実行し、結果をレコードセットに取得します
rs.Open sql, conn
' レコードセットの内容をシートに書き込みます
ws.Range("A1").CopyFromRecordset rs
' リソースを解放します
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
MsgBox "データが正常に取得されました!", vbInformation
Exit Sub
ErrorHandler:
If Not rs Is Nothing Then
If rs.State = 1 Then rs.Close
Set rs = Nothing
End If
If Not conn Is Nothing Then
If conn.State = 1 Then conn.Close
Set conn = Nothing
End If
MsgBox "Error: " & Err.Description, vbCritical
End Sub
Function GetIniValue(section As String, key As String, iniFile As String) As String
Dim buffer As String * 255
Dim valueLength As Long
valueLength = GetPrivateProfileString(section, key, "", buffer, 255, iniFile)
GetIniValue = Left(buffer, valueLength)
End Function以上でハードコーディングを避ける方法になります。
注意事項
64ビットのPostgreSQL ODBCドライバがインストールされていることを確認してください。
.iniファイルの場所は、VBAコード内の ThisWorkbook.Path & “\dbconfig.ini” に設定しています。dbconfig.ini ファイルがExcelファイルと同じディレクトリにあることを確認してください。
エラーハンドリングを追加して、エラー発生時に適切にリソースを解放するようにしています。