【ExcelVBA】PostgreSQLデータベースに接続する方法

Option Explicit

Const DB_HOST As String = "192.168.56.106"
Const DB_PORT As String = "5432"
Const DB_NAME As String = "awx"
Const DB_USER As String = "awx"
Const DB_PASS As String = "awx"
Const SHT_RESULT As String = "Sheet1"

Sub main()

    ' SQLクエリ
    Dim sql As String
    sql = "SELECT * FROM public.main_project"

    Call ConnectDB(sql, SHT_RESULT)

End Sub

Private Function ConnectDB(ByVal sql As String, ByVal sheetNm As String)

    ' DBコネクション開設
    Dim CN As New ADODB.Connection
    CN.Open "Driver=PostgreSQL Unicode;UID=awx;Port=" & DB_PORT & ";Server=" & DB_HOST & ";Database=" & DB_NAME & ";PWD=" & DB_PASS

    ' SQL実行
    Dim RS As New ADODB.Recordset
    RS.Open sql, CN, adOpenKeyset, adLockOptimistic, adCmdText

    Dim i As Long, j As Long

    ' 実行結果出力
    With Worksheets(sheetNm)
        .Cells.Clear
        RS.MoveFirst
        i = 2
        Do Until RS.EOF
            For j = 0 To RS.Fields.Count - 1
                If (i = 2) Then
                    .Cells(i - 1, j + 1) = RS(j).Name
                    .Cells(i, j + 1) = RS(j).Value
                Else
                    .Cells(i, j + 1) = RS(j).Value
                End If
            Next
            RS.MoveNext
            i = i + 1
        Loop
    End With

    ' 各種オブジェクトのクローズ
    RS.Close
    CN.Close
    Set CN = Nothing

End Function