データベースがソフトウェアから使えると、自動化の可能性が広がります。
今回、PostgreSQLにExcelからアクセスし、SELECT文を実行してみましたので、覚書として記しておきます。接続にはADODBを使いました。
PostgreSQLのインストール
データベースには、PostgreSQLを選びました。フリーなリレーショナルデータベース(RDB)だからです。
まずはPCにインストールします。あまり細かく書いても、すぐに変わってしまう可能性があるので、概要のみ示します。
- 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」の中にテーブルができました。今回は、この設定で使います。
-
- スタート → ProgreSQL 13 → SQL Shell (psql)
- 「Database[postgres]:」の欄をmiscとしてログイン
- 以下のSQLを流して、テストテーブルを作ってみる
CREATE TABLE usage_record( no SERIAL PRIMARY KEY, id VARCHAR(16) NOT NULL, ct TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
- テストレコードを追加する
INSERT INTO usage_record (no, id) VALUES (1, 'MISC1');
ODBCの設定
作成したデータベースにODBCで接続できるように設定します。
-
- コントロールパネル → 管理ツール → ODBC データ ソース (64 ビット) → ユーザーDNSタブ → 追加
- PostgreSQL ANSI(x64)→完了
※ SQL Shell (psql)で、Client Encoding [SJIS]となっていたため - データベース名(ここではmisc)、ユーザ名、パスワードを設定して「テスト」
- Connection successfulを確認 → OK → 保存 → OK
ExcelからPostgreSQLにアクセスする
Excelにマクロを設定し、PostgreSQLにアクセスします。
Excelにマクロを設定する
- Excelを立ち上げる
- 開発 → Visual Basic → プロジェクトエクスプローラ → VBAProject(Book1)を右クリック → 挿入 → ユーザーフォーム
- フォームのオブジェクト名を「FrmPostgreSQL」とする
- FrmPostgreSQLにチェックボックスとテキストボックスを1つずつ配置する
- チェックボックスのオブジェクト名を「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
データが取れれば成功です。delete文やInsert文も使えますので、試してみてください。
余談:Excel自体をデータベースとして使ってはいけないのか?
Excelをデータベースとして使うことができます。
しかし、次のような問題点があります。
- (ADODBでは?)複雑なSQLが使えない場合がある
- (ADODBでは?)delete文が使えない
- (僕のPCのExcelでは)1,048,576行までしか使えない
10年ほど前は、Excelシートに対してADODBを使い、SQLで検索をかけたりもしていました。しかし、ちょっと複雑なSQLを投げると、すぐに「そんな複雑なSQLは使えません」というようなエラーが返ってきた覚えがあります。久しく使っていないので、もしかすると、現在は改善されたかもしれません。
また、ADODBでは、delete文が使えないようです。
更に、僕のPCでは、Excelの最大行数が1,048,576行です。これは、一日1,000レコード書き込めば、3年持たない計算です。多いようで少ない行数です。
これらの問題点が致命的でなければ、Excelをデータベースとして使うことができると考えます。でも、ADODBを使うより、普通にVBAでアクセスする方が簡単だと思います。
まとめ
ExcelからPostgreSQLにアクセスするために、PostgreSQLのインストールからの手順を示しました。
Select文で検索した結果がExcelシートにコピーされると、解析する際に便利です。このExcelマクロを改造して、いろいろなデータベースに接続できるようにしたり、SQLの履歴を残したりするなど、使い勝手を良くしてご活用ください。
コメント