【ExcelVBA】Excelのシートの値をSQLのInsert文を使って別シートに追加する方法

5分

SHARE

この記事では、Excelのシートの値をSQLのInsert文を使って別シートに追加する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

【動画】Excelのシートの値をSQLのInsert文を使って別シートに追加する実際の動き

本題に入る前に、まずは次の動画をご覧ください。


シート「top」にある生徒の点数データを、月ごとのシートにコピーしています。

Insert文を実行してシートへのコピーを行っています。

マクロ作成の流れ

STEP.1
ADOを使用して自Excelファイルのデータソースに接続する
ADOを使用して自Excelファイルのデータソースに接続します。
STEP.2
Insert文を生成する
Insert文を生成します。
STEP.3
ADODB.ConnectionオブジェクトのExecuteメソッドを呼び出してInsert文を実行する
ADODB.ConnectionオブジェクトのExecuteメソッドを呼び出してInsert文を実行します。
Insert文が実行されると、対象のシートにデータが貼り付けられます。
目次へ戻る

Excelファイルの例

今回は次のExcelファイルを用意しました。

1月から6月までの期間で生徒それぞれ5人のテストデータが計30件入力されています。

以上のデータを、1月から6月までの期間ごとに用意されているシートに貼り付けていきます。

マクロ実行後は次の通りに生徒のテストデータが貼り付けられます。

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Private Sub btn_getDirFileList_Click()

    Dim oCon        As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim sqlStr      As String               'SQL文用変数
    Dim rowCnt      As Integer              '行数のカウンタ
        
    '行数のカウンタを初期化する(データの先頭行を設定)
    rowCnt = 11

    'インスタンスの生成
    Set oCon = New ADODB.Connection
    
    With oCon
    
        '接続情報の取得
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"
        
        'データソースへの接続を開く
        .Open
        
    End With
            
    Do
        
        'Insert文の生成
        sqlStr = "insert into "
        sqlStr = sqlStr & "[" & Worksheets("top").Range("A" & rowCnt).Value & "$]"
        sqlStr = sqlStr & " ("
        sqlStr = sqlStr & "  名前"
        sqlStr = sqlStr & " ,国語"
        sqlStr = sqlStr & " ,数学"
        sqlStr = sqlStr & " ,理科"
        sqlStr = sqlStr & " ,社会"
        sqlStr = sqlStr & " ,英語"
        sqlStr = sqlStr & " )"
        sqlStr = sqlStr & " VALUES"
        sqlStr = sqlStr & " ( "
        sqlStr = sqlStr & "'" & Worksheets("top").Range("B" & rowCnt).Value & "'"
        sqlStr = sqlStr & " ," & Worksheets("top").Range("C" & rowCnt).Value
        sqlStr = sqlStr & " ," & Worksheets("top").Range("D" & rowCnt).Value
        sqlStr = sqlStr & " ," & Worksheets("top").Range("E" & rowCnt).Value
        sqlStr = sqlStr & " ," & Worksheets("top").Range("F" & rowCnt).Value
        sqlStr = sqlStr & " ," & Worksheets("top").Range("G" & rowCnt).Value
        sqlStr = sqlStr & " )"
        
        'Insert文を実行する
        oCon.Execute sqlStr
        
        rowCnt = rowCnt + 1
        
        DoEvents
    
    'データが存在する間は処理を行う
    Loop While (Worksheets("top").Range("A" & rowCnt).Value <> "")
    
End Sub

コードの解説

注目すべきコード①

最初に見て頂きたいのは23行目から33行目です。

    'インスタンスの生成
    Set oCon = New ADODB.Connection
    
    With oCon
    
        '接続情報の取得
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"
        
        'データソースへの接続を開く
        .Open
        
    End With

以上は、自Excelファイルのデータソースにマクロが接続するためのコードです。

生徒のテストデータをマクロが参照するのに以上のコードを実行します。

30行目のThisWorkbook.FullNameには、自分自身のExcelファイルのフルパスが格納されています。

34行目のOpenメソッドを実行すると、マクロが自Excelファイルのデータソースに接続します。

注目すべきコード②

次に見て頂きたいのは23行目から33行目です。

        'Insert文の生成
        sqlStr = "insert into "
        sqlStr = sqlStr & "[" & Worksheets("top").Range("A" & rowCnt).Value & "$]"
        sqlStr = sqlStr & " ("
        sqlStr = sqlStr & "  名前"
        sqlStr = sqlStr & " ,国語"
        sqlStr = sqlStr & " ,数学"
        sqlStr = sqlStr & " ,理科"
        sqlStr = sqlStr & " ,社会"
        sqlStr = sqlStr & " ,英語"
        sqlStr = sqlStr & " )"
        sqlStr = sqlStr & " VALUES"
        sqlStr = sqlStr & " ( "
        sqlStr = sqlStr & "'" & Worksheets("top").Range("B" & rowCnt).Value & "'"
        sqlStr = sqlStr & " ," & Worksheets("top").Range("C" & rowCnt).Value
        sqlStr = sqlStr & " ," & Worksheets("top").Range("D" & rowCnt).Value
        sqlStr = sqlStr & " ," & Worksheets("top").Range("E" & rowCnt).Value
        sqlStr = sqlStr & " ," & Worksheets("top").Range("F" & rowCnt).Value
        sqlStr = sqlStr & " ," & Worksheets("top").Range("G" & rowCnt).Value
        sqlStr = sqlStr & " )"

以上はInsert文を生成するコードです。

31行目では、シート名を指定しています。

データベースのSQLのInsert文ではテーブル名を指定しますが、Excelのマクロで使うInsert文ではシート名を指定します。

33行目から38行目では、フィールド名を指定します。

このフィールド名は、生徒のテストデータを貼り付ける先シートの1行目の文字列を指します。

1行目の文字列と一致しない文字列を指定してしまうとエラーとなるので注意してくださいね。

42行目から47行目は、シートに貼り付ける生徒のテストデータを指定しています。

注目すべきコード③

次に見て頂きたいのは51行目です。

        'Insert文を実行する
        oCon.Execute sqlStr

以上のコードは、Insert文を実行するコードです。

ADODB.ConnectionオブジェクトのExecuteメソッドの引数に先ほど生成したInsert文を指定してExecuteを呼び出すことでInsert文が実行されます。

Insert文が実行されると、月ごとのシートに生徒のテストデータが貼り付けられます。

目次へ戻る

動作確認

マクロを実行した実行結果は、本記事内のExcelファイルの例の内容をご覧ください。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

  1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim oCon        As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

本記事では、Excelのシートの値をSQLのInsert文を使って別シートに追加する方法についてご説明しました。

Excelのシートの値を一度に別シートに追加したい時は参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら



目次へ戻る

前の記事

【ExcelVBA】別ブックのセルの値をSQLのSelect文…

次の記事

【ExcelVBA】SQLのSelect文を使ってCSVファイ…