Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – Excel → DB同期

Excel VBA
スポンサーリンク

ねらい：Excel の一覧を「DB の正本」と自動で同期させる“型”を作る

Excel でマスタや明細を管理しつつ、最終的には SQL Server／Access／MySQL などの DB を正本にしたい場面は多いです。
手作業でインポートするのではなく、

  • Excel で編集
  • ボタン1つで「追加／更新／削除」を DB に反映

という“Excel → DB 同期”の型を作っておくと、運用が一気に楽になります。

ここでは初心者向けに、

  • 基本の考え方（Excel 側の「同期フラグ」＋ DB 側のキー）
  • ADO で DB に接続するテンプレ
  • 行ごとの「INSERT／UPDATE／DELETE」を切り替えるロジック
  • シートとテーブルの項目マッピング
  • 具体例（顧客マスタの同期）

を、コード付きでかみ砕いて説明します。

設計：Excel 側で「同期対象」と「操作種別」をはっきり持つ

シート構成（CustomerSheet のイメージ）

例えば顧客マスタを同期したいとします。
Excel シート「Customers」を以下のような構成にします。

A列: SyncFlag（同期対象か。Y/N）
B列: Action（操作種別。INS/UPD/DEL）
C列: CustomerID（主キー。DB 側の PK と一致）
D列: CustomerName
E列: Email
F列: Phone

ポイントは 2 つです。

1つ目：全行を自動で判定しない。SyncFlag=Y の行だけ DB に反映するようにし、「送る／送らない」を人間が制御できるようにする。
2つ目：Action に INS/UPD/DEL を明示しておく。これで VBA 側は「何をするか」を迷わずに済みます。

共通基盤：ADO接続・SQL実行・ログのユーティリティ

ADO 接続テンプレ（SQL Server の例）

環境に合わせて ConnectionString を書き換えてください（Access や SQLite でも同じ型でいけます）。

' ModDb_Base.bas
Option Explicit

Public Function GetDbConnection() As Object
    ' ここでは SQL Server を例とします
    ' 実際の接続文字列は環境に合わせて変更してください
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    Dim connStr As String
    connStr = ""
    connStr = connStr & "Provider=SQLOLEDB;"
    connStr = connStr & "Data Source=SERVER_NAME;"
    connStr = connStr & "Initial Catalog=DB_NAME;"
    connStr = connStr & "Integrated Security=SSPI;"  ' または User ID=...;Password=...;
    
    conn.Open connStr
    Set GetDbConnection = conn
End Function

Public Sub ExecNonQuery(ByVal conn As Object, ByVal sql As String)
    ' INSERT/UPDATE/DELETE など、結果セットを返さない SQL 用
    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = conn
        .CommandType = 1 ' adCmdText
        .CommandText = sql
        .Execute
    End With
End Sub
VB

ここではシンプルさを優先して「直接 SQL を組み立てて流す」形にしています。
本番ではパラメータを使うとさらに安全ですが、「まず動く形」を優先します。

ログ出力（シート右側に結果を残す）

Public Sub WriteSyncLog(ByVal ws As Worksheet, ByVal rowIdx As Long, ByVal status As String, ByVal message As String)
    ' ここでは G列=Status, H列=Message と仮定
    ws.Cells(rowIdx, 7).Value = status
    ws.Cells(rowIdx, 8).Value = message
End Sub
VB

このように、「どの行で何をしたか／成功か失敗か」をログに残すことで、後から必ず確認できるようにします。

行ごとの INSERT／UPDATE／DELETE を切り替えるロジック

SQL を組み立てる小さな関数（危険ポイントを理解する）

' ModDb_SqlBuilder.bas
Option Explicit

Private Function SqlEscape(ByVal v As String) As String
    ' シングルクォートをエスケープ
    SqlEscape = Replace(v, "'", "''")
End Function

Public Function BuildInsertCustomerSql(ByVal customerId As String, _
                                       ByVal name As String, _
                                       ByVal email As String, _
                                       ByVal phone As String) As String
    Dim sql As String
    sql = "INSERT INTO Customers (CustomerID, CustomerName, Email, Phone) VALUES ("
    sql = sql & "'" & SqlEscape(customerId) & "',"
    sql = sql & "'" & SqlEscape(name) & "',"
    sql = sql & "'" & SqlEscape(email) & "',"
    sql = sql & "'" & SqlEscape(phone) & "')"
    BuildInsertCustomerSql = sql
End Function

Public Function BuildUpdateCustomerSql(ByVal customerId As String, _
                                       ByVal name As String, _
                                       ByVal email As String, _
                                       ByVal phone As String) As String
    Dim sql As String
    sql = "UPDATE Customers SET "
    sql = sql & "CustomerName='" & SqlEscape(name) & "',"
    sql = sql & "Email='" & SqlEscape(email) & "',"
    sql = sql & "Phone='" & SqlEscape(phone) & "' "
    sql = sql & "WHERE CustomerID='" & SqlEscape(customerId) & "'"
    BuildUpdateCustomerSql = sql
End Function

Public Function BuildDeleteCustomerSql(ByVal customerId As String) As String
    Dim sql As String
    sql = "DELETE FROM Customers WHERE CustomerID='" & SqlEscape(customerId) & "'"
    BuildDeleteCustomerSql = sql
End Function
VB

重要なのは SqlEscape です。
文字列中の '（シングルクォート）を '' に置き換えないと、SQLが壊れます（最悪、SQLインジェクションの入り口にもなります）。
本格運用ではパラメータ化必須ですが、まずはシングルクォートのエスケープだけは“絶対に入れる”癖をつけてください。

Excel → DB 同期本体：Customers シートを見て順番に反映する

一括同期マクロ（Insert/Update/Delete 切り替え）

' ModDb_SyncCustomers.bas
Option Explicit

Public Sub Sync_Customers()
    Dim ws As Worksheet
    Set ws = Worksheets("Customers")
    
    Dim a As Variant
    a = ws.Range("A1").CurrentRegion.Value
    
    Dim conn As Object
    Set conn = GetDbConnection()
    
    Dim r As Long
    For r = 2 To UBound(a, 1)
        Dim flag As String
        flag = UCase$(Trim$(CStr(a(r, 1)))) ' SyncFlag
        
        If flag = "Y" Then
            Dim act As String
            act = UCase$(Trim$(CStr(a(r, 2)))) ' Action: INS/UPD/DEL
            
            Dim customerId As String
            Dim name As String
            Dim email As String
            Dim phone As String
            
            customerId = CStr(a(r, 3))
            name = CStr(a(r, 4))
            email = CStr(a(r, 5))
            phone = CStr(a(r, 6))
            
            If Len(customerId) = 0 Then
                Call WriteSyncLog(ws, r, "NG", "CustomerID が空です")
                GoTo NextRow
            End If
            
            On Error GoTo ErrHandler
            
            Dim sql As String
            
            Select Case act
                Case "INS"
                    sql = BuildInsertCustomerSql(customerId, name, email, phone)
                    ExecNonQuery conn, sql
                    Call WriteSyncLog(ws, r, "OK", "Insert 完了")
                Case "UPD"
                    sql = BuildUpdateCustomerSql(customerId, name, email, phone)
                    ExecNonQuery conn, sql
                    Call WriteSyncLog(ws, r, "OK", "Update 完了")
                Case "DEL"
                    sql = BuildDeleteCustomerSql(customerId)
                    ExecNonQuery conn, sql
                    Call WriteSyncLog(ws, r, "OK", "Delete 完了")
                Case Else
                    Call WriteSyncLog(ws, r, "NG", "Action が不正: " & act)
            End Select
            
            On Error GoTo 0
        End If
        
NextRow:
    Next r
    
    conn.Close
    Set conn = Nothing
    
    MsgBox "Customers の DB 同期が完了しました。", vbInformation
    Exit Sub

ErrHandler:
    Call WriteSyncLog(ws, r, "NG", "DBエラー: " & Err.Number & " - " & Err.Description)
    Err.Clear
    Resume NextRow
End Sub
VB

ここでの重要ポイントを深掘りします。

SyncFlag が Y の行だけを対象にし、「今はこの行は流さない」といった制御を簡単にします。
Action によって、INS/UPD/DEL を切り替えています。これを Excel 側で明示しておくと、VBA 側のロジックが非常に単純になります。
エラーが出ても全体が止まらないように、行単位でエラーを Catch→ログ→次へ という流れにしています。
Status/Message を書き込むことで、「どの行が DB へ成功／失敗したか」「失敗の理由は何か」がすぐ分かります。

シートとテーブルのマッピングを柔軟にする（列名が変わっても壊れにくく）

列番号を「列名から探す」ヘルパー

列が増えたり順番が変わったりすると、a(r, 3) などの「ベタ列番号」はすぐに壊れます。
少しレベルを上げて、“列名から列番号を探す”ヘルパーを入れておくと耐性が上がります。

' ModDb_ColumnHelper.bas
Option Explicit

Public Function ColIndex(ByVal headers As Variant, ByVal name As String) As Long
    Dim c As Long
    For c = 1 To UBound(headers, 2)
        If LCase$(Trim$(CStr(headers(1, c)))) = LCase$(Trim$(name)) Then
            ColIndex = c
            Exit Function
        End If
    Next
    ColIndex = 0
End Function
VB

これを使う形で、Sync_Customers を書き換えるとこうなります。

Public Sub Sync_Customers_ByHeader()
    Dim ws As Worksheet
    Set ws = Worksheets("Customers")
    
    Dim a As Variant
    a = ws.Range("A1").CurrentRegion.Value
    
    Dim idxSync As Long, idxAction As Long, idxId As Long, idxName As Long, idxMail As Long, idxPhone As Long
    idxSync = ColIndex(a, "SyncFlag")
    idxAction = ColIndex(a, "Action")
    idxId = ColIndex(a, "CustomerID")
    idxName = ColIndex(a, "CustomerName")
    idxMail = ColIndex(a, "Email")
    idxPhone = ColIndex(a, "Phone")
    
    Dim conn As Object
    Set conn = GetDbConnection()
    
    Dim r As Long
    For r = 2 To UBound(a, 1)
        Dim flag As String
        flag = UCase$(Trim$(CStr(a(r, idxSync))))
        
        If flag = "Y" Then
            Dim act As String
            act = UCase$(Trim$(CStr(a(r, idxAction))))
            
            Dim customerId As String
            customerId = CStr(a(r, idxId))
            
            Dim name As String
            name = CStr(a(r, idxName))
            
            Dim email As String
            email = CStr(a(r, idxMail))
            
            Dim phone As String
            phone = CStr(a(r, idxPhone))
            
            ' 以下は前と同じ（省略）
        End If
    Next
    
    conn.Close
    Set conn = Nothing
End Sub
VB

これなら、列の順番を変えてもヘッダ名さえ合っていれば動き続けます。
現場運用で列追加が頻繁に起こる場合は、こちらの書き方がおすすめです。

例題：Excel側で顧客マスタを編集 → DB と同期する一連の流れ

想定シナリオ

DB 側の Customers テーブルは既に存在し、
CustomerID を主キーとして持っているとします（Excel の C列と一致）。

  1. 初回だけ DB から Excel にダウンロード（別マクロで SELECT→シート貼り付け）
  2. Excel 側で顧客名・メール・電話を編集
  3. 追加したい顧客には SyncFlag=Y, Action=INS を設定
  4. 既存顧客の修正は SyncFlag=Y, Action=UPD
  5. 削除したい顧客は SyncFlag=Y, Action=DEL
  6. Sync_Customers を実行

これで、Excel 上の編集内容が DB に反映されます。
反映後は、SyncFlag を空に戻す／Action をクリアするなどの運用ルールを決めておくとよいです。

落とし穴と対策（重要ポイントの深掘り）

直接本番 DB に書き込みに行かない

最初のうちは、必ず「テスト用 DB」「テスト用テーブル」で動作確認してください。

接続先 DB を変えただけで本番と同じコードになるので、
実際に SQL がどう発行されているか、期待通り INSERT/UPDATE/DELETE されているかをテスト環境でチェックしてから本番に切り替えましょう。

トランザクション（まとまり）をどう扱うか

本格的にやるなら、

  • 100 行まとめて BEGIN TRAN → コミット → ロールバック

といったトランザクション制御を入れるのが理想です。
初心者向けにはまず「1行ずつ確実に同期する」形で慣れ、
必要になったタイミングで conn.BeginTrans / conn.CommitTrans / conn.RollbackTrans を入れていくのが良いです。

SQL 組み立てミスでデータがおかしくなる

SqlEscape を必ず通すこと。
特に、名前やメールに ' が入るケースは意外と多いです（O’Neil など）。
本番運用では、できるだけパラメータ化（cmd.Parameters）に移行していくことを強くおすすめします。

日付・数値の型が DB と合わない

今回は文字列項目だけの例にしましたが、日付や数値を扱う場合、
Excel 上の見た目と DB の型（DATE, DECIMAL など）が合わずにエラーになることがあります。

実務では、

  • Excel 側では「文字列」として DB の期待するフォーマットを作る
  • もしくはパラメータで型を合わせる

方向で設計するのが安全です。

まとめ：Excel は“編集画面”、DB は“正本”として、同期ロジックをテンプレ化する

Excel → DB 同期の肝は、

Excel 側で「同期フラグ」「操作種別（INS/UPD/DEL）」「主キー」をはっきり持つ。
VBA 側で ADO 接続→行ごとに SQL を発行し、ログを残す。
列名とテーブル名のマッピングをテンプレート化し、プロジェクトごとに差し替えるだけにする。

という“型”を作ることです。

スポンサーリンク
タイトルとURLをコピーしました