VBA | DB操作 – 第4回：Excel から MERGE を実行

DB操作を行うExcelマクロについて説明します。

今回の記事は『第4回』となります。

第1回：Configシートの設定値をVBA上で取り扱う
第2回：SELECT文により取得したデータをExcelに格納
第3回：Excel から INSERT, UPDATE, DELETE を実行
第4回：Excel から MERGE を実行

【Excelダウンロードはこちら】

※2021/2/5改修版
　・ODBC接続からOraOLEDB.Oracle接続に変更
　・INSER,UPDATE,MERGE文の生成方法を修正

---

概要

第4回目は、第3回で実装したExcelシートに格納したデータ（セルの値）を、指定したDB（データベース）のテーブルに挿入・更新・削除する方法に加えて、データ挿入 or 更新（MERGE）を追加で実装しました。

MERGE実行機能を追加したので、今回のExcelファイルからOracleデータベースを操作する機能は下記となります。

タイプ	機能
INSERT	DBのテーブルにデータを挿入 （レコード挿入）
UPDATE	DBのテーブルに格納されているデータを更新 （レコード更新）
DELETE	DBのテーブルに格納されているデータを削除（レコード削除）
MERGE	DBのテーブルに格納されているデータを挿入もしくは更新

---

パッケージ構成

Excelマクロ内の構成は下記となります 。（使用するモジュールのみ記載）

Template_ver1.x.x.xlsm
├標準モジュール
|   ├modCmnGlbConst
|   ├modSql   
|
クラスモジュール
    ├Configurator
    ├DBManager

---

ソースコード解説

前回まで、INSERT・UPDATE・DELETE・MERGEの各シートから実行されるマクロはそれぞれ別関数として独立させていました。

それでは、今後機能を拡張する際に不便かと思いましたので、INSERT・UPDATE・DELETE・MERGEの各シートから実行したマクロは、一つの関数を経由してからデータベース操作クラスにアクセスするように改修しました。（イメージは下記画像）

例えば、MERGE文の実行では executeMergeSqlsOracle() を実行し、その中で executeDmlSqlsOracle 関数を呼び出しています。
（下記に一部のコードを記載していますが、詳細はExcelファイルのVBAを確認してみてください。）

' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
' brief : D.各MERGE文を連続実行
' note  : Oracle対応
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
Public Sub executeMergeSqlsOracle()

    Dim sheet As Worksheet          ' SQLを生成する情報が格納されているシート
    Dim tableName As String         ' 操作する対象のテーブル

    Set sheet = ThisWorkbook.Worksheets(MERGE_SHEET_NAME)
    tableName = sheet.Cells(TABLE_NAME_ROW, TABLE_NAME_COL)

    executeDmlSqlsOracle "MERGE", tableName, sheet, TYPE_DIFINED_ROW, DB_COL_NAME_DIFINED_ROW, DATA_START_ROW, DATA_START_COL

End Sub

' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
' brief : 各INSERT, UPDATE, DELETE, MERGE文を連続実行
' note  : Oracle対応
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
Private Sub executeDmlSqlsOracle(dmlType As String, tableName As String, sheet As Worksheet _
    , typeDifinedRow As Long, dbColNameDifinedRow As Long, dataStartRow As Long, dataStartCol As Long)

    Dim servicename As String, username As String, password As String

    ' Config 設定の読み込み
    Set config = New Configurator
    config.setData ThisWorkbook.Worksheets(GLB_CONFIG_SHEET), GLB_CONFIG_KEY_COL, GLB_CONFIG_ITEM_COL, GLB_CONFIG_START_ROW

    servicename = config.getItem("SERVICE_NAME")
    username = config.getItem("USERNAME")
    password = config.getItem("PASSWORD")

    ' DB接続
    Set dbManagerOracle = New DBManager
    dbManagerOracle.openOracle servicename, username, password

    ' トランザクション開始
    dbManagerOracle.begintrans

    On Error GoTo err

    ' SQL実行
    dbManagerOracle.createAndExcuteOracleSqls tableName, sheet, typeDifinedRow, dbColNameDifinedRow, dataStartRow, dataStartCol

    ' コミット
    dbManagerOracle.committrans

    ' DB切断
    dbManagerOracle.closeConnection

    Select Case dmlType

        Case "INSERT"
            Application.StatusBar = Now & "SQL INSERT実行完了"

        Case "UPDATE"
            Application.StatusBar = Now & "SQL UPDATE実行完了"

        Case "DELETE"
            Application.StatusBar = Now & "SQL DELETE実行完了"

        Case "MERGE"
            Application.StatusBar = Now & "SQL MERGE実行完了"

    End Select

    Exit Sub

err:
    ' エラー発生時はロールバック
    Debug.Print "エラー番号:" & err.Number & Chr(13) & "エラー内容:" & err.Description
    MsgBox "エラー番号:" & err.Number & Chr(13) & "エラー内容:" & err.Description

    MsgBox "ロールバックを実行し、プログラムを終了します。"
    dbManagerOracle.rollbacktrans

    End

End Sub

今回は以上となります。これでExcelファイルのマクロからOracleデータベースを操作する方法の説明は終了となります。

これまで説明してきたような、データベースを操作する部分”のみ”を予め製作しておけば、他の用途に応じて追加改造することで様々な場面に対応できます。

基本機能を準備しておくことの大切さを共感して頂ければ幸いです。