データ移行とExcelVBAを使ったマクロツールのお話

はじめに

現場によっては、Excelを使ってデータベースに
接続してデータを取っているツールがあったりしました。
便利そうだったので作ってみようと思います。

これを作るメリット

今の世の中、便利なツールはいくらでもあります。
なんでこんなツールがあるのかと思うでしょう。
このマクロツールと出会ったのは、データ移行の案件の時でした。
システムを更新する際に元のデータを新しいデータに移行する
作業をこの時はしていました。
その時に、今回紹介するツールを使っていました。
実際の生データをExcel形式で落として保存。
その保存していたデータをそのままINSERTしました。
この時感じたメリットとしては、生データを取得すると同時に
バックアップとして残すことも可能ということでした。
また、複数バックアップ取ることで、
他のリアルタイムとの差分を見れることでした。
そういう訳で、本当に便利なツールだなと感じたので、
ExcelVBAとDBを繋いだマクロを作ってみようと思いました。

参照設定

とりあえず、作ってみましょう。
参照設定ですが、まずは
Microsoft ActiveX Data Objects 2.8 Libraryにチェックを入れてください。

Excel側のレイアウトについて

まずは３つのシートを用意しました。

各シートの役割は以下の通りです。

• DBの設定→SQLに接続する為の設定を記載
• メイン→取得したいテーブル名と項目を記載
• 結果→SQLの実行結果を張り付ける

レイアウトは以下の通りです。

設定シート

メインシート

結果シート（最初は中身は空）

実際にソースコードを書いてみました

ソースコード

Sub test()
'設定値を取得
Dim dbUserId As String
Dim dbPort As String
Dim dbServer As String
Dim dbName As String
Dim dbPassword As String
Static dbTablename As String
Const sheetName As String = "結果"

dbUserId = Worksheets("DBの設定").Range("C2").Value
dpPort = Worksheets("DBの設定").Range("C3").Value
dbServer = Worksheets("DBの設定").Range("C4").Value
dbName = Worksheets("DBの設定").Range("C5").Value
dbPassword = Worksheets("DBの設定").Range("C6").Value
dbTablename = Worksheets("メイン").Range("C5").Value

Dim dbConnect As Object
Set dbConnect = CreateObject("ADODB.Connection")
dbConnect.ConnectionString = "Provider=MSDASQL;Driver=PostgreSQL Unicode;" & _
                "UID=" & dbUserId & ";" & _
                "Port=" & dpPort & ";" & _
                "Server=" & dbServer & ";" & _
                "Database=" & dbName & ";" & _
                "PWD=" & dbPassword & ";" & " SSLmode=disable;"
dbConnect.Open
                
'SQL作成
Dim SQL As String: SQL = "SELECT  * from " & dbTablename
'SQL実行
Dim dbRecordset As ADODB.Recordset
' Set dbRecordset = New ADODB.Recordset
Set dbRecordset = dbConnect.Execute(SQL)
'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

' データベースを閉じる
dbRecordset.Close
dbConnect.Close
MsgBox ("完了")
                
End Sub

動作確認

PGAdminからSQLを使用した取得結果がこちらだとします。

テストボタンを押下

完了メッセージを表示

結果シートに値が表示されたことが確認できます。

まとめ

今回は、VBAを利用してDBから値を取得してみました。
また、このツールの使い方を紹介してみました。
今は全部をSelectしていますが、
取得したい値だけをSelectするなど応用ができると思います。

今回はここまでにしておきます。ではでは