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

8分

SHARE

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

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

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

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

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

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

目次へ戻る

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

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

Excelのマクロ

STEP.1
Accessへの接続情報取得
Accessに接続するための接続情報を取得します。
STEP.2
Connectionオブジェクトのインスタンスの生成
Connectionオブジェクトのインスタンスを生成します。
このインスタンスは、マクロがAccessに接続するために必要です。
STEP.3
Accessに接続
STEP.1の接続情報をもとにAccessに接続します。
STEP.4
Recordsetオブジェクトのインスタンスの生成
Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、Accessのテーブルデータの参照・挿入に必要です。
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のデータをAccessのテーブルに追加
AddNewメソッドでSTEP.11のデータをAccessのテーブルに追加します。
AddNewメソッドを実行する場合は、STEP.10で取得したフィールド名とSTEP.11で取得したExcelのシートのデータの2つをパラメタに指定します。
目次へ戻る

コードの例

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

    Dim mdbName     As String, strSql As String, impTbl As String, cnt As Integer, rowCnt As Integer, rng As Range
    Dim fNMAry()    As Variant, valAry() As Variant
    Dim adoCON      As New ADODB.Connection
    Dim oRS         As ADODB.Recordset
    
    'カレントディレクトリのAccessファイル名を取得
    mdbName = ActiveWorkbook.Path & "\" & "0060.mdb"
    
    'CSVファイルにデータをエクスポートするAccessのテーブル名を取得
    impTbl = "tbl_score_list"
    
    'Microsoft Accessへの接続情報を取得する
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & mdbName & ""
                            
    'Accessに接続する
    adoCON.Open

    'レコードセットの作成
    Set oRS = New ADODB.Recordset
        
    With oRS
    
        .Source = impTbl                'データを追加するテーブル名
        .ActiveConnection = adoCON      '先ほど取得したデータベース接続情報が設定されているadoCONをActiveConnectionプロパティに設定し、
                                        'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
        .CursorType = adOpenKeyset      'カーソルタイプにキーセットカーソル使用
        .LockType = adLockPessimistic   'データの編集中のレコード単位排他的ロック
        .Open                           'Accessに接続
        
        'フィールド名格納用配列の要素数の変更(フィールド数を指定)
        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

    '後処理
    Set adoCON = Nothing

コードの解説

注目すべきコード①

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

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

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

注目すべきコード②

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

    'レコードセットの作成
    Set oRS = New ADODB.Recordset

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

注目すべきコード③

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

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

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

注目すべきコード④

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

            '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)

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

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

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

48行目、49行目では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次元配列に変更
注目すべきコード⑤

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

    'Accessに接続する
    adoCON.Open

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

注目すべきコード⑥

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

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

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

目次へ戻る

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

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

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

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

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

    Dim adoCON      As New ADODB.Connection
    Dim oRS         As ADODB.Recordset

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

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

目次へ戻る

最後に

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

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

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

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

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

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

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

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

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

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

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

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



目次へ戻る

前の記事

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

次の記事

【ExcelVBA】SQL ServerのテーブルのデータをE…