【ExcelVBA】ExcelのシートのデータをSQL Serverのテーブルに追加するには

8分

SHARE

この記事では、ExcelのシートのデータをSQL Serverのテーブルに追加する方法についてご説明します。

【動画】ExcelのシートのデータをSQL Serverのテーブルに追加するする実際の動き

本題に入る前に、まずは次のツイートをご覧ください。

recordsetのaddnewメソッドでExcelのシートのデータをSQL Serverのテーブルに追加しています。

なお、Insert文は使っていません。

目次へ戻る

ExcelのシートのデータをSQL Serverのテーブルに追加する方法

ExcelのシートのデータをSQL Serverのテーブルに追加するには、次の流れの通りにコードを書いていきます。

Excelのマクロ

STEP.1
SQL Serverへの接続情報取得
SQL Serverに接続するための接続情報を取得します。
STEP.2
Connectionオブジェクトのインスタンスの生成
Connectionオブジェクトのインスタンスを生成します。
このインスタンスは、マクロがSQL Serverに接続するために必要です。
STEP.3
SQL Serverに接続
STEP.1の接続情報をもとにSQL Serverに接続します。
STEP.4
Recordsetオブジェクトのインスタンスの生成
Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、SQL Serverのテーブルデータの参照・挿入に必要です。
STEP.5
Excelのシートのデータをインポートするテーブル名をrecordsetに設定
recordsetに、Excelのシートのデータをインポートするテーブル名を設定します。
STEP.6
Recordsetオブジェクトに接続先情報を設定
RecordsetオブジェクトのActiveConnectionプロパティに、STEP.1で取得した接続先情報が設定されているConnectionオブジェクトのインスタンスを設定します。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
STEP.7
カーソルタイプの指定
recordsetにカーソルタイプを指定します。
STEP.8
テーブル編集中にレコードをロック
テーブル編集中にレコードをロックします。
STEP.9
カーソルを開く
カーソルを開きます。
STEP.10
Excelのシートのデータをインポートするテーブルのフィールド名を取得
Excelのシートのデータをインポートするテーブルのフィールド名を取得します。
STEP.11
Excelのシートのデータを配列に格納
Excelのシートのデータを配列に格納します。
STEP.12
AddNewメソッドでSTEP.11のデータをSQL Serverのテーブルに追加
AddNewメソッドでSTEP.11のデータをSQL Serverのテーブルに追加します。
AddNewメソッドを実行する場合は、STEP.10で取得したフィールド名とSTEP.11で取得したExcelのシートのデータの2つをパラメタに指定します。
目次へ戻る

コードの例

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

    Dim DBName As String, connDB As String, sqlStr As String, impTbl As String, cnt As Integer, rowCnt As Integer, rng As Range
    Dim wkAry() As String, fNMAry() As Variant, valAry() As Variant
    Dim oCon As ADODB.Connection
    Dim oRS As ADODB.Recordset
 
    'DBの名前
    DBName = "testDataDB"
    
    '(テータを追加する)テーブル名
    impTbl = "tbl_score_list"
    
    'データベース接続情報を取得
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"
    
    'Connectionオブジェクトのインスタンスを生成する
    Set oCon = New ADODB.Connection
    
    'SQL Serverに接続する
    oCon.Open connDB
    
    'Recordsetオブジェクトのインスタンスを生成する
    Set oRS = New ADODB.Recordset
    
    With oRS
    
        .Source = impTbl                'データを追加するテーブル名の取得
        .ActiveConnection = oCon        '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、
                                        'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
        .CursorType = adOpenKeyset      'カーソルタイプにキーセットカーソル使用
        .LockType = adLockPessimistic   'データの編集中のレコード単位排他的ロック
        .Open                           'カーソルを開く
        
        'フィールド名格納用配列の要素数の変更(フィールド数を指定)
        ReDim fNMAry(oRS.Fields.Count - 1)
        
        'recordsetからフィールド名の取得
        For cnt = 0 To oRS.Fields.Count - 1
            fNMAry(cnt) = oRS.Fields.Item(cnt).Name
        Next
    
        'Excelのシートのデータ件数分ループ
        For rowCnt = 2 To Sheets("data").Cells(Rows.Count, 1).End(xlUp).Row
            
            'Excelのシートのデータを取得する
            Set rng = Sheets("data").Range(Sheets("data").Cells(rowCnt, 1), _
                                           Sheets("data").Cells(rowCnt, oRS.Fields.Count))
            
            '配列の縦横を入れ替える
            valAry = WorksheetFunction.Transpose(rng)
            valAry = WorksheetFunction.Transpose(valAry)
            
            'テーブルにデータを追加する
            .AddNew fNMAry, valAry
    
            '変更内容を保存する
            .Update
        
        Next
    
    End With

コードの解説

注目すべきコード①

最初に見て頂きたいのは56行目と59行目です。

            'テーブルにデータを追加する
            .AddNew fNMAry, valAry
    
            '変更内容を保存する
            .Update

56行目のAddnewメソッドでテーブルに、Excelのシートのデータを追加し、59行目のUpdateメソッドでその追加したデータを保存します。

注目すべきコード②

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

    'Recordsetオブジェクトのインスタンスを生成する
    Set oRS = New ADODB.Recordset

この25行目が実行されるとrecordsetを扱うことができるようになります。

注目すべきコード③

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

        'recordsetからフィールド名の取得
        For cnt = 0 To oRS.Fields.Count - 1
            fNMAry(cnt) = oRS.Fields.Item(cnt).Name
        Next

この41行目で、データを追加するテーブルの列名を取得しています。

注目すべきコード④

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

            'Excelのシートのデータを取得する
            Set rng = Sheets("data").Range(Sheets("data").Cells(rowCnt, 1), _
                                           Sheets("data").Cells(rowCnt, oRS.Fields.Count))
            
            '配列の縦横を入れ替える
            valAry = WorksheetFunction.Transpose(rng)
            valAry = WorksheetFunction.Transpose(valAry)

48行目でExcelのシートのデータを取得して変数「rng」に格納しています。(変数「rng」は、セルの値を範囲指定して取得し格納することができるrange型の変数)

52行目で変数「rng」の値を、Transposeメソッドで配列の次元を変更(2次元配列⇒1次元配列)して配列「valAry」に格納し、Addnewメソッドのパラメタに指定してもAddnewメソッドが実行ができる状態にしておきます。

なぜTransposeメソッドを2回も使っているのか？

52行目、53行目ではTransposeメソッドを使っています。

なぜTransposeメソッドを2回も使っているのかというと、配列「valAry」に対してTransposeメソッドを2回使わないと、その配列を受け取ったAddnewメソッドが実行時にエラーとなってしまうからです。

Transposeメソッドを1回実行しただけではダメです。2回Transposeメソッドを実行する必要があります。

ではなぜTransposeメソッドを2回使わないとエラーになるのかというと、変数「rng」の値を受け取った配列「valAry」が1次元配列ではないからです。(2次元配列になっている)

1回目のTransposeメソッド実行前

配列が2次元配列
1回目のTransposeメソッド実行後

配列が2次元配列

2次元配列をAddnewメソッドのパラメタに渡して実行するとエラーになります。

配列「valAry」をAddnewメソッドに渡して実行するなら、1次元配列である必要があります。

なぜ1次元配列でないといけないのかというと、列名を格納した配列「fNMAry」が1次元配列だからです。

配列「fNMAry」が1次元配列なら、配列「valAry」も1次元配列でなければなりません。

では2次元配列を1次元配列に変換するにはどうすればいいの？という問題が出てきますが、この問題を解決してくれるのが「Transposeメソッドを2回実行する」ことです。

Transposeメソッドを2回実行することで、2次元配列が1次元配列に変更されます。

なので、2次元配列を1次元配列に変えてくれる「Transposeメソッドの2回実行」を行うわけです。

ではTransposeメソッドを使い、2次元配列が1次元配列に変わる状態を見てもらいましょう。次の画像をご覧ください。

1回目のTransposeメソッド実行後

1回目のTransposeメソッド実行後は2次元配列のまま
2回目のTransposeメソッド実行後

2回目のTransposeメソッド実行後は1次元配列に変更されている

以上、配列「valAry」が1次元配列に変換されてAddnewメソッドに渡せる状態になり、無事にAddnewメソッドが実行できるようになりました。

【補足】Transposeメソッドを実行する前と後の結果のまとめ

Transposeメソッドを実行する前と後の結果をまとめると次のイメージになります。

  • ①Transposeメソッド実行前：
    ⇒配列は2次元配列
  • ②Transposeメソッド1回目実行後：
    ⇒配列は2次元配列のまま。①の2次元配列の縦横が並び替わる
  • ③Transposeメソッド2回目実行後：
    ⇒配列は1次元配列に変換。②の2次元配列が1次元配列に変更
注目すべきコード⑤

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

    'SQL Serverに接続する
    oCon.Open connDB

この22行目で、ExcelのマクロがSQL Serverに接続しています。

注目すべきコード⑥

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

        .ActiveConnection = oCon        '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、
                                        'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける

22行目でSQL Serverに接続した後にActiveConnectionプロパティにoConを設定します。この設定は、Excelマクロがカーソルを扱えるようにするのに必要な設定です。

目次へ戻る

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

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

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

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

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

    Dim oCon As ADODB.Connection
    Dim oRS As ADODB.Recordset

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

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

目次へ戻る

最後に

この記事では、ExcelのシートのデータをSQL Serverのテーブルに追加する方法についてご説明しました。

ExcelのシートのデータをSQL Serverのテーブルに追加する処理の流れは次の通りです。

  • ①Excelのシートのデータを取得(range型の変数に格納)
  • ②RecordsetのAddnewメソッドの引数に①のデータ(と列名)を渡して実行し、SQL Serverのテーブルに①のデータを追加
  • ③Excelのシートのデータの件数分、①と②を繰り返す

かなりザックリしていますが、上記が処理の流れです。

なお、本記事ではRecordset(のAddnewメソッド)を使って説明しています。

Insert文を使わずにExcelのシートのデータを挿入する方法があること、Recordsetを使ってどのようにデータを追加するのか、参考になるかと思います。

Insert文を使ってExcelのシートのデータを追加する方法については改めて別の記事でご紹介したいとも思います。

ExcelのシートのデータをSQL Serverのテーブルに追加したい時は本記事を参考にしてみてくださいね。

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

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

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

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



目次へ戻る

前の記事

【AccessVBA】ダブルクォーテーションを付けずにAcce…

次の記事

【ExcelVBA】ExcelのシートのデータをAccessの…