この記事では、ExcelのシートのデータをSQL Serverのテーブルに追加する方法についてご説明します。
【動画】ExcelのシートのデータをSQL Serverのテーブルに追加するする実際の動き
本題に入る前に、まずは次のツイートをご覧ください。
コードが少し冗長だったので、コードを少し書き換えて実行してみました😅
【書き換えた内容】
・フィールド名はコードに直接書き込まず、SQL Serverのテーブルから取得
・Excelのデータは1セルずつ取得せず、セルを範囲指定してrange型の変数に取得#ExcelVBA#SQLServer pic.twitter.com/1WcHmid8Qi— まさ@Excel、VBA、マクロ(経験年数16年) (@masamasa9785) August 4, 2021
recordsetのaddnewメソッドでExcelのシートのデータをSQL Serverのテーブルに追加しています。
なお、Insert文は使っていません。
ExcelのシートのデータをSQL Serverのテーブルに追加する方法
ExcelのシートのデータをSQL Serverのテーブルに追加するには、次の流れの通りにコードを書いていきます。
Excelのマクロ
このインスタンスは、マクロがSQL Serverに接続するために必要です。
このインスタンスは、SQL Serverのテーブルデータの参照・挿入に必要です。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
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次元配列になっている)
配列が2次元配列
配列が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メソッド実行後は2次元配列のまま
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」ボタンをクリックします。
- 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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。