Excel VBAを利用して特定のシートのデータをデータベースに一括インサートする方法

この記事では、Excel VBAを使って特定のシートのデータをデータベースに一括インサートする方法について解説します。実際のコード例とその詳細な解説、さらには実践的な応用例を3つも紹介しています。これにより、Excelデータのデータベース移行作業を大幅に時短できるテクニックを身につけられます。

VBAを使ってExcelのデータをデータベースにインサートする基本的な方法

データベース操作にはADO（ActiveX Data Objects）を使用します。以下はExcelのシートからデータを読み取り、Accessデータベースに一括インサートする基本的なコードです。

Sub InsertIntoDB()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim lastRow As Long
    Dim i As Long

    'オブジェクトの初期化
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    'データベース接続
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\your\database.accdb;"

    '最後の行を取得
    lastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

    'データの一括インサート
    For i = 2 To lastRow
        strSQL = "INSERT INTO TableName (Column1, Column2) VALUES ('" & _
                Sheets("Sheet1").Cells(i, 1).Value & "','" & _
                Sheets("Sheet1").Cells(i, 2).Value & "')"
        conn.Execute strSQL
    Next i

    '接続を閉じる
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

上記のコードは、Sheet1のデータを読み取り、Accessのテーブルにインサートする例です。具体的には、A列とB列のデータを読み取り、データベースのColumn1とColumn2に挿入しています。データベース接続の文字列部分とテーブル名やカラム名は、それぞれの環境に合わせて変更してください。

応用例1：複数のカラムデータを一括インサート

特定の範囲のカラムデータを一括でインサートしたい場合の方法を解説します。

Sub MultiColumnInsert()
    '...（前述の初期化処理）...

    '複数カラムのデータを一括インサート
    For i = 2 To lastRow
        strSQL = "INSERT INTO TableName (Column1, Column2, Column3, Column4) VALUES ('" & _
                Sheets("Sheet1").Cells(i, 1).Value & "','" & _
                Sheets("Sheet1").Cells(i, 2).Value & "','" & _
                Sheets("Sheet1").Cells(i, 3).Value & "','" & _
                Sheets("Sheet1").Cells(i, 4).Value & "')"
        conn.Execute strSQL
    Next i

    '...（後述の接続閉じる処理）...
End Sub

応用例2：エラーハンドリングを追加

データベース操作中にエラーが発生する可能性があるため、エラーハンドリングを追加します。

Sub InsertWithErrorHandler()
    On Error GoTo ErrorHandler

    '...（前述の操作処理）...

    Exit Sub
ErrorHandler:
    MsgBox "エラーが発生しました。エラー内容：" & Err.Description
End Sub

応用例3：SQLインジェクション対策

セキュリティを高めるため、SQLインジェクション対策を取り入れた方法を解説します。

Function PreventSQLInjection(str As String) As String
    PreventSQLInjection = Replace(str, "'", "''")
End Function

Sub InsertPreventSQLInjection()
    '...（前述の初期化処理）...

    For i = 2 To lastRow
        strSQL = "INSERT INTO TableName (Column1, Column2) VALUES ('" & _
                PreventSQLInjection(Sheets("Sheet1").Cells(i, 1).Value) & "','" & _
                PreventSQLInjection(Sheets("Sheet1").Cells(i, 2).Value) & "')"
        conn.Execute strSQL
    Next i

    '...（後述の接続閉じる処理）...
End Sub

この例では、SQLインジェクションのリスクを軽減するために、文字列内のシングルクォートを二重に置き換える簡易的な方法を使用しています。しかし、よりセキュアな対策を取る場合には、パラメータクエリやストアドプロシージャの使用を検討してください。

まとめ

Excel VBAを使用して、Excelのシートデータをデ

ータベースに一括インサートする方法は、大量のデータ移行作業を効率化する強力な手段となります。基本的なコードから応用例までを実践すれば、より高度で安全なデータベース操作が可能となります。