【ExcelVBA】Excelファイルにある複数シートのデータを全てAccessのテーブルに追加する方法とは

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

【動画】Excelファイルにある複数シートのデータを全てAccessのテーブルに追加する実際の動き

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

Excelファイルにある複数のシートのデータをSQLのSELECT文を使ってAccessのデータベースファイルのテーブルに追加しています。

複数のシートのデータはSELECT文のUNION ALLを使い結合させてRecordsetに格納させています。

次に、そのRecordsetにあるレコードをAccessのデータベースファイルに追加しています。

Excelファイルの例

マクロ側のExcelファイル

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

A2の黄色のセルには、Accessのデータベースファイルのフルパスを入力し、実行ボタンをクリックするとExcelファイルにある複数のシートのデータを全てAccessのテーブルに追加されます。

今回のサンプルでは、Accessのデータベースファイルのテーブルに以下のシートのデータが追加されます。

Accessのデータベースファイルの例

今回は次のAccessのデータベースファイルを用意しました。

Excelファイルのデータがある表と同じ構造のテーブル「T_社員」が用意されています。

なお、テーブル「T_社員」はマクロのコード内で使われているので、そちらも確認して頂けたらと思います。

    'Access用のRecordsetを開く
    aRs.Open "T_社員", aConn, adOpenKeyset, adLockOptimistic

マクロ作成の流れ

コードの例

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

Option Explicit

Private Sub btn_exec_Click()

    Dim filePath            As String               'データが入力されているExcelファイルの格納先
    Dim eConn               As adodb.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数(Excelファイル用)
    Dim aConn               As adodb.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数(Accessファイル用)
    Dim eRs                 As adodb.Recordset      'レコードセット用変数(Excelファイル用)
    Dim aRs                 As adodb.Recordset      'レコードセット用変数(Accessファイル用)
    Dim ws                  As Worksheet            'Worksheet用変数
    Dim cnt                 As Long                 'カウンタ
    Dim sheetNM()           As String               'シート名用配列
    Dim sqlStr              As String               'SQL文用変数
    
    'Accessのファイルが格納されているフルパスが入力されているExcelファイルの格納先を取得する
    filePath = Worksheets("top").Range("dbName").Value
                                
    'Excelファイル用のConnectionインスタンスの生成
    Set eConn = New ADODB.Connection
                
    'Accessファイル用のConnectionインスタンスの生成
    Set aConn = New ADODB.Connection
            
    'Accessファイル用のRecordsetオブジェクトのインスタンスを生成する
    Set eRs = New ADODB.Recordset
    
    'Excelファイル用のRecordsetオブジェクトのインスタンスを生成する
    Set aRs = New ADODB.Recordset
    
    'データベース接続情報の取得
    aConn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & filePath & ""

    'Accessに接続する
    aConn.Open filePath
    
    'Access用のRecordsetを開く
    aRs.Open "T_社員", aConn, adOpenKeyset, adLockOptimistic
        
    With eConn
    
        '接続情報の取得(自分自身のExcelファイルに接続する)
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"
        
        'データソースへの接続を開く
        .Open
        
    End With

    For Each ws In Worksheets
    
        If ws.Name <> "top" Then
        
            '「top」以外のシートの場合
            
            '動的配列の再宣言を行う(配列に格納されている値を残したまま)
            ReDim Preserve sheetNM(cnt)
            
            'セルに表示されている日付を配列に格納する
            sheetNM(cnt) = ws.Name
            
            cnt = cnt + 1

        End If

    Next ws
    
    'SELECT文を用意する
    sqlStr = sqlStr & "Select"
    sqlStr = sqlStr & " *"
    sqlStr = sqlStr & " From"
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " Select"
    sqlStr = sqlStr & " *"
    sqlStr = sqlStr & " From"
    sqlStr = sqlStr & " [" & sheetNM(0) & "$]"
    
    '表をUNION ALLで結合する
    For cnt = 1 To UBound(sheetNM)
    
        sqlStr = sqlStr & " UNION ALL"
        sqlStr = sqlStr & " Select"
        sqlStr = sqlStr & " *"
        sqlStr = sqlStr & " From"
        sqlStr = sqlStr & " [" & sheetNM(cnt) & "$]"
    
    Next cnt
    
    sqlStr = sqlStr & " )"
    
    'Excelファイル用のRecordsetを開く
    eRs.Open sqlStr, eConn, adOpenStatic
    
    Do Until eRs.EOF
    
        '新規レコードを追加する
        aRs.AddNew
            
        'Accessのテーブルのフィールド数分ループさせるFor文
        For cnt = 0 To aRs.Fields.Count - 1
            
            'ExcelファイルのデータをAccessの項目に追加する
            aRs.Fields(aRs.Fields.Item(cnt).Name) = eRs.Fields(cnt).Value
        
        Next cnt
        
        'Access用のRecordsetを更新する
        aRs.Update
        
        'Excel用Recordsetのレコードのカーソルを次に移動する
        eRs.MoveNext
        
        DoEvents
            
    Loop
        
    'Excelファイル用のrecordsetを閉じる
    eRs.Close
    
    'Accessファイル用のrecordsetを閉じる
    aRs.Close
    
    'Excelファイル用のConnectionを切断する
    eConn.Close
    
    'Accessファイル用のConnectionを切断する
    aConn.Close
    
    '後処理
    Set eConn = Nothing
    Set aConn = Nothing
    Set eRs = Nothing
    Set aRs = Nothing

End Sub

注目すべきコード①

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

    'Accessのファイルが格納されているフルパスが入力されているExcelファイルの格納先を取得する
    filePath = Worksheets("top").Range("dbName").Value

以上のコードは、Accessのデータベースファイルの置き場を取得するコードです。

今回のサンプルでは、A2のセルの黄色のセルにAccessのデータベースファイルの置き場を入力しておき、マクロが実行されるとAccessのデータベースファイルの置き場が変数filePathに格納されます。

注目すべきコード②

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

    'Excelファイル用のConnectionインスタンスの生成
    Set eConn = New ADODB.Connection
                
    'Accessファイル用のConnectionインスタンスの生成
    Set aConn = New ADODB.Connection
            
    'Accessファイル用のRecordsetオブジェクトのインスタンスを生成する
    Set eRs = New ADODB.Recordset
    
    'Excelファイル用のRecordsetオブジェクトのインスタンスを生成する
    Set aRs = New ADODB.Recordset

以上のコードはAccess用とExcel用の、ConnectionとRecordsetオブジェクトのインスタンスを生成するコードです。

Excelファイル用のConnectionオブジェクトのインスタンスは、マクロが自分自身のExcelファイルにADO接続するのに必要です。

Excelファイル用のRecordsetオブジェクトのインスタンスは、SELECT文を実行したり、取得したデータを扱うなどするのに必要です

Accessのデータベースファイル用Connectionオブジェクトのインスタンスは、ExcelのマクロがAccessのデータベースファイルにADO接続するのに必要です。

Accessのデータベースファイル用Recordsetオブジェクトのインスタンスは、Excel側のデータをAccessのデータベースファイルにあるテーブルに追加するのに必要です。

注目すべきコード③

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

    'データベース接続情報の取得
    aConn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & filePath & ""

    'Accessに接続する
    aConn.Open filePath

以上のコードは、ExcelがAccessにADO接続する接続情報を取得してAccessに接続している処理のコードになります。

ADO接続するために必要なConnectionインスタンスを30行目で生成し、そのConnectionインスタンスに対してADO接続するために33行目から35行目で接続情報を設定します。

接続情報が取得できたら、36行目のOpenメソッドの引数にAccessのデータベースファイルの置き場を指定して実行しています。

Openメソッドが正常に実行されると、マクロがAccessのデータベースファイルに接続できるようになります。

注目すべきコード④

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

    'Access用のRecordsetを開く
    aRs.Open "T_社員", aConn, adOpenKeyset, adLockOptimistic

以上のコードは、Accessのデータベースファイルにあるテーブル「T_社員」に対し、Recordsetを開いている処理のコードです。

Openメソッドの2つ目に指定している変数aConnがAccessにADO接続する接続情報で、マクロがAccessのデータベースファイルに接続しテーブル「T_社員」に対し参照しています。

このAccessのデータベースファイルのRecordsetを開くことで、Excel側のデータをテーブル「T_社員」に追加可能にする準備が整いました。

注目すべきコード⑤

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

    With eConn
    
        '接続情報の取得(自分自身のExcelファイルに接続する)
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"
        
        'データソースへの接続を開く
        .Open
        
    End With

以上のコードは、マクロが自分自身のExcelファイルにADO接続するためのコードになります。

ADO接続するために必要なConnectionインスタンスに対してADO接続するために44行目から46行目で接続情報を設定します。

なお、45行目では自分自身のExcelファイルをフルパスで指定しています。

自分自身のExcelファイルのフルパスはThisWorkbook.FullNameプロパティから取得することができます。

注目すべきコード⑥

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

    For Each ws In Worksheets
    
        If ws.Name <> "top" Then
        
            '「top」以外のシートの場合
            
            '動的配列の再宣言を行う(配列に格納されている値を残したまま)
            ReDim Preserve sheetNM(cnt)
            
            'セルに表示されている日付を配列に格納する
            sheetNM(cnt) = ws.Name
            
            cnt = cnt + 1

        End If

    Next ws

以上のコードは、「top」と名前の付いたシート以外のシート名を全て取得する処理のコードです。

今回のサンプルでは、「top」と名前の付いたシートはあくまでAccessのデータベースファイルの格納先を入力するセルと、マクロを実行するシートなので、データを取得する対象のシートではありません。

「top」と名前の付いたシート以外は全てAccessのデータベースファイルのテーブルに追加したいデータが存在してるシートです。

なので、「top」と名前の付いたシート以外のシート名を全て取得します。

53行目のコードは、Excelファイルにあるシート全てに対して処理を行うFor文で、55行目のIF文は「top」と名前の付いたシート以外の場合の条件です。

「top」と名前の付いたシート以外の場合は、63行目でそのシート名を配列sheetNMに格納します。

60行目では、「ReDim Preserve」を配列に指定し、配列の再宣言を行っています。

配列sheetNMに1つずつシート名を格納するたびに、配列に格納したシート名は残したまま要素数を1つずつ拡張しています。

要素数を拡張することで先に追加したシート名は残ったまま、新たなシート名を配列に格納できるようにしています。

注目すべきコード⑦

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

    'SELECT文を用意する
    sqlStr = sqlStr & "Select"
    sqlStr = sqlStr & " *"
    sqlStr = sqlStr & " From"
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " Select"
    sqlStr = sqlStr & " *"
    sqlStr = sqlStr & " From"
    sqlStr = sqlStr & " [" & sheetNM(0) & "$]"
    
    '表をUNION ALLで結合する
    For cnt = 1 To UBound(sheetNM)
    
        sqlStr = sqlStr & " UNION ALL"
        sqlStr = sqlStr & " Select"
        sqlStr = sqlStr & " *"
        sqlStr = sqlStr & " From"
        sqlStr = sqlStr & " [" & sheetNM(cnt) & "$]"
    
    Next cnt
    
    sqlStr = sqlStr & " )"
    
    'Excelファイル用のRecordsetを開く
    eRs.Open sqlStr, eConn, adOpenStatic

以上のコードは、Excelファイルにある複数シートのデータを全てAccessのテーブルに追加するSELECT文を用意し、取得する処理のコードです。

UNION ALLを使って(84行目)、データのある表を結合させて条件に合致したデータを取得します。

76行目から79行目の1つのSELECT文と、82行目のFOR文内でUNION ALLを使って残りのSELECT文を結合させます。

上記のコードだけではどんなSELECT文になるのか分かりにくいと思うので、実際のSELECT文は次の通りです。

Select
    *
From
    (
        Select
            *
        From
            [法務部$]
        UNION ALL
        Select
            *
        From
            [経理部$]
        UNION ALL
        Select
            *
        From
            [開発部$]
        UNION ALL
        Select
            *
        From
            [総務部$]
        UNION ALL
        Select
            *
        From
            [営業部$]
        UNION ALL
        Select
            *
        From
            [情報システム部$]
    )

全てのSELECT文を結合させたSELECT文が用意できたら、95行目でこのSELECT文を実行します。

注目すべきコード⑧

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

    Do Until eRs.EOF
    
        '新規レコードを追加する
        aRs.AddNew
            
        'Accessのテーブルのフィールド数分ループさせるFor文
        For cnt = 0 To aRs.Fields.Count - 1
            
            'ExcelファイルのデータをAccessの項目に追加する
            aRs.Fields(aRs.Fields.Item(cnt).Name) = eRs.Fields(cnt).Value
        
        Next cnt
        
        'Access用のRecordsetを更新する
        aRs.Update
        
        'Excel用Recordsetのレコードのカーソルを次に移動する
        eRs.MoveNext
        
        DoEvents
            
    Loop

以上のコードは、Excelファイルにある複数シートから取得したデータをAccessのテーブルに追加する処理のコードです。

97行目では、Excelファイルにある複数シートから取得したデータの数分ループさせるループで、そのデータを全てAccessのデータベースファイルのテーブルにし終わったらこのループ処理から抜けます。

100行目では、AddNewメソッドを実行し1レコード分のデータを追加できるようにしておきます。

103行目から108行目は、Excelファイル側のデータの列分繰り返すループ処理で、106行目でその列の値一つ一つ繰り返しAccessのデータベースファイルのテーブルに追加していきます。

なお、列の数は「aRs.Fields.Count」(103行目)から取得することができます。

データをAccessのデータベースファイルのテーブルに追加したら、111行目でAccessのデータベースファイルのテーブルを更新します。

106行目のテーブルへのデータ追加と111行目のテーブル更新処理はセットで行う必要があります。

更新処理まで行わないとテーブルへの追加が反映されないので、追加・更新はセットで行うのを忘れないようにしましょう。

ここまででExcelファイル側のデータ1レコード分が追加されたので、次のレコードを追加していきます。

次のレコードを追加するためには、114行目のMoveNextメソッドを実行する必要があります。

このMoveNextメソッドを実行しないとExcel側の同じレコードを参照したままなので、必ずMoveNextメソッドを実行するのを忘れないようにしましょう。

動作確認

マクロ実行前

今回は「Excelファイルの例」のexcelファイル、「Accessのデータベースファイルの例」のAccessのデータベースファイルを使います。

マクロ実行後

Excelファイルにある複数シートのデータを全てAccessのテーブルに追加されたことが確認できました。

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

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

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

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

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

    Dim eConn               As adodb.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数(Excelファイル用)
    Dim aConn               As adodb.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数(Accessファイル用)
    Dim eRs                 As adodb.Recordset      'レコードセット用変数(Excelファイル用)
    Dim aRs                 As adodb.Recordset      'レコードセット用変数(Accessファイル用)

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

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

最後に

本記事では、Excelファイルにある複数シートのデータを全てAccessのテーブルに追加する方法についてご説明しました。

同じ構造の表なら、複数のシートにデータがあってもSQLのUNION ALLを使えばお手軽にデータを追加することができるので参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→　受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら