PostgreSQLをインストールしてExcel VBAからアクセスしてみた

データベースがソフトウェアから使えると、自動化の可能性が広がります。
今回、PostgreSQLにExcelからアクセスし、SELECT文を実行してみましたので、覚書として記しておきます。接続にはADODBを使いました。

PostgreSQLのインストール
ODBCのインストール
データベースを作り、データを入れる
ExcelからPostgreSQLにアクセスする
1. Excelにマクロを設定する
2. ExcelからPostgreSQLにアクセスする
余談：Excel自体をデータベースとして使ってはいけないのか？
まとめ

PostgreSQLのインストール

データベースには、PostgreSQLを選びました。フリーなリレーショナルデータベース（RDB）だからです。
まずはＰＣにインストールします。あまり細かく書いても、すぐに変わってしまう可能性があるので、概要のみ示します。

PostgreSQL Database Downloadに接続する
使用しているPCのOSを考慮して、Versionを選ぶ
→ 特別なことがない限り、Versionは数字の大きい新しいバージョンを選ぶ
→ Windowsの場合、Windows x86-64は64ビット用、Windows x86-32は32ビット用
→ 自分のPCのOSやビット数が分からない場合は、デスクトップのPCアイコンを右クリックしてプロパティを選択し、開いたウィンドウを参照する
Downloadボタンをクリックするとダウンロードが始まる
ダウンロードフォルダに「postgresql-13.2-1-windows-x64.exe」などといったファイルが作成される
念のためウィルス検出ソフトなどでウィルスの無いことを確認してから、ダブルクリックして実行
→ パスワード以外はほぼ全て「Next」をクリック

ODBCのインストール

続いて、ODBC（Open Database Connectivity）をインストールします。
ODBCは、PostgreSQLとExcelの仲立ちになるソフトです。

ODBCのダウンロードサイトに接続する
バージョンとビット数を確認してダウンロードする
→ 僕はWindows 64ビットで、PostgreSQLのバージョン13.2をダウンロードしたので、「psqlodbc_13_00_0000-x86.zip」を選択
念のためウィルス検出ソフトなどでウィルスの無いことを確認してからzipを展開
展開されたmsiファイルをダブルクリックしてインストール
→ 途中、かなり強いセキュリティ警告表示が出現
→ 気持ち悪かったが、ウィルス検出ソフトを信じた

データベースを作り、データを入れる

データベースを作る

PostgreSQLをインストールすると、postgresというデータベースが既にできているそうですので、この「データベースを作る」作業は必ずしも必要ではありません。

データベースを作る

スタート → ProgreSQL 13 → SQL Shell (psql)
パスワード以外はEnterでログインする
CREATE DATABASEコマンドで任意のデータベースを作る（ここではmisc）
「\q」でいったん抜ける

テーブルを作ってデータを入れる

普通は、テーブルの中にスキーマを作り、その中にテーブルを作るようです。
そこまで考えずにテーブルを作ったら、デフォルトのスキーマである「public」の中にテーブルができました。今回は、この設定で使います。

テーブルを作ってデータを入れる

1. スタート → ProgreSQL 13 → SQL Shell (psql)
2. 「Database[postgres]:」の欄をmiscとしてログイン
3. 以下のSQLを流して、テストテーブルを作ってみる
```
CREATE TABLE usage_record(
    no SERIAL PRIMARY KEY,
    id VARCHAR(16) NOT NULL,
    ct TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
4. テストレコードを追加する
```
INSERT INTO usage_record (no, id) VALUES (1, 'MISC1');
```

ODBCの設定

作成したデータベースにODBCで接続できるように設定します。

1. コントロールパネル → 管理ツール → ODBC データソース (64 ビット) → ユーザーDNSタブ → 追加
  ODBCデータソースアドミニストレーター
2. PostgreSQL ANSI(x64)→完了
  ※ SQL Shell (psql)で、Client Encoding [SJIS]となっていたため
  
  PostgreSQL ANSI ODBCセットアップ
3. データベース名（ここではmisc）、ユーザ名、パスワードを設定して「テスト」
4. Connection successfulを確認 → OK → 保存 → OK

ExcelからPostgreSQLにアクセスする

Excelにマクロを設定し、PostgreSQLにアクセスします。

Excelにマクロを設定する

Excelを立ち上げる
開発 → Visual Basic → プロジェクトエクスプローラ → VBAProject(Book1)を右クリック → 挿入 → ユーザーフォーム
フォームのオブジェクト名を「FrmPostgreSQL」とする
FrmPostgreSQLにチェックボックスとテキストボックスを１つずつ配置する
フォーム
チェックボックスのオブジェクト名を「chkIsString」とする
テキストボックスのオブジェクト名を「txtSQL」とする
テキストボックスのMultiLine属性を「True」にする

「F7」キーを押してフォームのエディタを表示し、次のコードを貼り付ける
※「パスワード」は自分で設定したパスワードに変更のこと

Option Explicit

'-----------------------------------------------------------------------
' Summary : txtSQLテキストボックスでキーを押したときの処理
' Caution : 参照設定からMicrosoft ActiveX Data Objects x.x Libraryを選択
'-----------------------------------------------------------------------
Private Sub txtSQL_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    ' エラーメッセージ
    Dim strErr As String
    
    ' キーコードによって分岐
    Select Case KeyCode
        ' Enterキーの場合
        Case &HD
            ' ADODBを使用するための変数宣言
            Dim cnn As New ADODB.Connection
            Dim rec As ADODB.Recordset
            
            ' エラーが発生しても次の処理に移る
            On Error Resume Next
            ' 設定したODBCデータソースを使ってDBを開く
            Call cnn.Open("DSN=PostgreSQL30;" & _
                    "UID=postgres;PWD=パスワード;Database=misc")
            ' エラーチェック
            If Err.Number <> 0 Then
                strErr = Err.Description
                GoTo PostHandler
            End If
            ' テキストボックスに記されたSQLを内部変数に保持する
            Dim strSQL As String
            strSQL = Me.txtSQL.Text
            ' SQLを実行する
            Set rec = cnn.Execute(strSQL)
            ' エラーチェック
            If Err.Number <> 0 Then
                strErr = Err.Description
                GoTo PostHandler
            End If
            ' エラー処理を無効にする
            On Error GoTo 0
            
            ' Recordsetの状態を確認
            If rec.State <> adStateClosed Then
                ' 開いている場合には、先ず、シートをクリア
                Cells.Clear
                ' カラム名を表示
                Dim j As Long
                For j = 1 To rec.Fields.Count
                    Cells(1, j) = rec.Fields(j - 1).Name
                Next j
                ' 文字列チェックボックスの状態を確認
                If Me.chkIsString Then
                    ' チェックのある場合はシート全体を文字列書式にする
                    Cells.NumberFormatLocal = "@"
                    ' 行番号用の変数を準備
                    Dim i As Integer
                    i = 2
                    ' Recordsetの最後までループ
                    Do While rec.EOF = False
                        ' フィールドに値をセット
                        For j = 1 To rec.Fields.Count
                            Cells(i, j).Value = "'" & _
                                    rec.Fields(j - 1).Value
                        Next j
                        ' 次のレコードに移る
                        rec.MoveNext
                        ' 次の行に移る
                        i = i + 1
                    Loop
                Else
                    ' シートに一括で入力する
                    Call Range("A2").CopyFromRecordset(rec)
                End If
                ' 列幅を整える
                Columns("A:" & Split( _
                        Columns(rec.Fields.Count).Address, "$")(2)) _
                        .EntireColumn.AutoFit
            Else
                ' Recordsetが閉じている場合はDelete等が成功している筈
                Call MsgBox("成功しました", vbOKOnly + vbInformation, _
                        "txtSQLKeyDown")
            End If
        Case Else
    End Select
    
PostHandler:
    ' ADODBオブジェクトが開いている場合は閉じる
    If rec Is Nothing = False Then _
            If rec.State <> adStateClosed Then rec.Close
    Set rec = Nothing
    If cnn Is Nothing = False Then _
            If cnn.State <> adStateClosed Then cnn.Close
    Set cnn = Nothing
    If strErr <> "" Then
        ' エラーの場合はエラーの内容を表示する
        Call MsgBox(Err.Description, vbOKOnly + vbCritical, "txtSQLKeyDown")
    End If
End Sub

'-----------------------------------------------------------------------
' Summary : フォームの初期化
'-----------------------------------------------------------------------
Private Sub UserForm_Initialize()
    ' キャプションの設定
    Me.Caption = "FrmPostgreSQL"
    ' 取得データを文字列に強制的に変換はしない
    Me.chkIsString.Value = False
End Sub

プロジェクトエクスプローラ → VBAProject(Book1)を右クリック → 挿入 → 標準モジュール

次のコードを貼り付ける

Option Explicit

'-----------------------------------------------------------------------
' Summary : FrmPostgreSQLフォームをモードレスで開く
'-----------------------------------------------------------------------
Public Sub UseFrmPostgreSQL()
    ' FrmPostgreSQLフォームをモードレスで開く
    FrmPostgreSQL.Show (vbModeless)
End Sub

ツール → 参照設定 →「Microsoft ActiveX Data Objects x.x Library」を選択
※ 僕は6.1 Libraryを選択
デバッグ →「VBAProjectのコンパイル」を選び、エラーの無いことを確認
適当な名前を付けて、拡張子を「xlsm」で保存

ExcelからPostgreSQLにアクセスする

いよいよPostgreSQLにアクセスします

前節で保存した「xlsm」シートを開く
マクロ → UseFrmPostgreSQL → 実行
例では、「usage_record」というテーブルを作ったので、テキストボックスに下記コマンドを打って「Enter」
```
select * from usage_record order by no
```