Excel-VBA ORACLE INSERT

こんにちは！健史です。

Excel-VBAにて、ExcelデータをORACLEデータへ出力するプログラムを作成しました。

目　次　

プログラム
補足
最後に

プログラム

Sub TEST2()
'コンスタント定義
    Const CNS_COMMIT = 100
'変数定義
    Dim ix2, ix2_max, iy2, iy2_max As Long
    Dim cnt_insert, cnt_commit As Long
'Oracleアクセス用変数定義
    Dim USER_ID, PASSWORD As String
    Dim PROVIDER, DATA_SOURCE As String
    Dim TABLE_NAME As String
    Dim strSQL As String
'設定シートからセット
    USER_ID = Sheets(1).Cells(2, 2)
    PASSWORD = Sheets(1).Cells(3, 2)
    PROVIDER = Sheets(1).Cells(4, 2)
    DATA_SOURCE = Sheets(1).Cells(5, 2)
    TABLE_NAME = Sheets(1).Cells(10, 2)
'格納するExcelデータの行列の最大値を求める
    ix2_max = Sheets(2).Range("A1").End(xlDown).row
    iy2_max = Sheets(2).Range("A1").End(xlToRight).Column
'格納するテーブル初期クリアの確認：データが消す確認であり、手動でTRUNCATEする場合は３行を削除する
    If MsgBox(TABLE_NAME & "を削除しますが、良いですか？", vbYesNo + vbDefaultButton2) = vbNo Then
        Exit Sub
    End If
'インスタンス生成 データベース接続
    Dim conn As New ADODB.Connection
'Oraclenの場合
    conn.ConnectionString = _
        "Provider=" & PROVIDER & ";" & _
        "Data Source=" & DATA_SOURCE & ";" & _
        "USER ID=" & USER_ID & ";" & _
        "Password=" & PASSWORD & ";"
'ＤＢオープン
    conn.Open
'格納するテーブルを初期クリア：データが消えるので、手動でTRUNCATEする場合は２行を削除する
    strSQL = "TRUNCATE TABLE " & TABLE_NAME
    conn.Execute (strSQL)
'カウントの初期クリア
    cnt_insert = 0
    cnt_commit = 1
'INSERT処理
    For ix2 = 2 To ix2_max
'   INSERT INTO テーブル名 (
        strSQL = "INSERT INTO " & TABLE_NAME & " ("
'   [INSERT INTO テーブル名 (]内の項目名を編集、最後に[)VALUES (]
        For iy2 = 1 To iy2_max
            Select Case iy2
            Case iy2_max
                strSQL = strSQL & Sheets(2).Cells(1, iy2) & ") VALUES("
            Case Else
                strSQL = strSQL & Sheets(2).Cells(1, iy2) & ","
            End Select
        Next
'   [INSERT INTO テーブル名 (・,・・・) VALUES (]内の値を編集、最後の[)]
        For iy2 = 1 To iy2_max
            Select Case iy2
            Case iy2_max
                strSQL = strSQL & "'" & Sheets(2).Cells(ix2, iy2) & "'" & ") "
            Case Else
                strSQL = strSQL & "'" & Sheets(2).Cells(ix2, iy2) & "'" & ","
            End Select
        Next
'   INSERT実行
        conn.Execute (strSQL)
'   カウントアップ
        cnt_insert = cnt_insert + 1
        cnt_commit = cnt_commit + 1
'   コミット
        Select Case cnt_commit
        Case Is > CNS_COMMIT
            strSQL = "COMMIT"
            conn.Execute (strSQL)
            cnt_commit = 1
        End Select
    Next
'コミット(最終)
    strSQL = "COMMIT"
    conn.Execute (strSQL)
'ＤＢクローズ
    conn.Close
    Set conn = Nothing
'メッセージ表示
    MsgBox "更新件数：" & cnt_insert & vbCrLf & "処理終了！"
End Sub

補足

前提条件・注意

・初期処理で、処理対象テーブルをTRUNCATEしていますので、データが消えます。

　後述のシート１で設定するテーブル名を誤ってしまうと消えてしまいます。

　なので、最初に確認メッセージ表示しています。
　
　それでも不安という場合は、以下を削除して、TRUNCATEを手動で実行する運用が必要です。

'格納するテーブル初期クリアの確認：データが消す確認であり、手動でTRUNCATEする場合は３行を削除する
    If MsgBox(TABLE_NAME & "を削除しますが、良いですか？", vbYesNo + vbDefaultButton2) = vbNo Then
        Exit Sub
    End If

'格納するテーブルを初期クリア：データが消えるので、手動でTRUNCATEする場合は２行を削除する
    strSQL = "TRUNCATE TABLE " & TABLE_NAME
    conn.Execute (strSQL)

・Oracle Clientのインストールが必要です。

・オラクルへインサートするデータは、シート２に作成しておきます。

・Excelデータはインサートするテーブルと同じレイアウトであり、１行目の項目名はインサートするテーブルの項目名と同じである必要があります。

シート１に環境情報を設定

Excelシート１の設定シートを環境に合わせて修正すれば、動くと思います。

設定内容は以下です。
1).USER_ID
2).PASSWORD
3).PROVIDER
4).DATA_SOURCE
5).テーブル名

その他

データサンプルおよびテーブル情報、実行結果は以下です。

あとは、コメントを参照いただければと思います。

最後に

Excelにとっておいたバックアップデータを戻す作業を手で行おうとすると
・出力するテーブルをTRUNCATEしておく
・Excelデータをコピーして、貼り付け
・Commit;
ですかね。

忙しいときには「面倒くさい！」「煩わしい！」って思う作業です。