【AccessVBA】AccessのマクロがExcelのシートのデータを取得してテーブルに追加するには

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

【動画】AccessのマクロからExcelのマクロを実行する実際の動き

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

AccessのマクロがExcelのファイルに接続してデータを取り込むInsert文を実行することで、取得したデータをAccessのテーブルに追加しています。

マクロ作成の流れ

コードの例

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

Option Compare Database

Sub test()

    Dim conn            As ADODB.Connection     'Connection用変数
    Dim objExcel        As Object               'Excelアプリケーションのインスタンス用オブジェクト変数
    Dim db              As dao.Database         'DAOデータベース用オブジェクト用変数
    Dim excelFile       As String               'Excelのファイル
    Dim strSQL          As String               'SQL文
    Dim excelFileConst  As String               'Excelファイルの定義文用変数
    
    'Accesのカレントデータベース
    Set db = CurrentDb
        
    'Excelファイル名をフルパスで取得する
    excelFile = Application.CurrentProject.Path & "\" & "0180.xlsm"
    
    'Excelファイルのデータが存在するシート名
    Const SheetNM As String = "work"

    'Excelファイルの定義を取得する
    excelFileConst = "Excel 12.0;HDR=YES;IMEX=1;DATABASE="
    
    'Excelアプリケーションのインスタンスを生成する
    Set objExcel = CreateObject("Excel.Application")

    'Connectionインスタンスの生成
    Set conn = New ADODB.Connection
    
    With conn

        '接続情報の取得
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & excelFile & _
        ";Extended Properties =Excel 12.0;"

        'データソースへの接続を開く
        .Open

    End With

    'データを取り込む先のテーブルを削除する
    strSQL = "Delete * FROM [T_商品マスタ]"
    db.Execute strSQL
    
    'データを取り込むSQLを用意する'
    strSQL = "INSERT INTO [T_商品マスタ]("
    strSQL = strSQL & "[商品コード]"
    strSQL = strSQL & " ,[商品名]"
    strSQL = strSQL & " ,[価格]"
    strSQL = strSQL & ")"
    strSQL = strSQL & "SELECT"
    strSQL = strSQL & "[商品コード]"
    strSQL = strSQL & " ,[商品名]"
    strSQL = strSQL & " ,[価格]"
    strSQL = strSQL & " FROM"
    strSQL = strSQL & " [" & excelFileConst & excelFile & "].[work$A1:C21]"
    
    'SQLの実行'
    db.Execute strSQL
    
    'Excelを終了する
    objExcel.Quit

    'AccessDBの接続を解除する
    conn.Close
    
    '後処理
    Set conn = Nothing
    Set objExcel = Nothing
    Set db = Nothing

End Sub

注目すべきコード①

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

    'Accesのカレントデータベース
    Set db = CurrentDb

以上のコードは、開いているAccess自分自身を表すコードです。

このコードを実行することで、INSERT文を実行することができるようになるので、必ずこのコードを実行しておきます。

注目すべきコード②

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

    'Excelファイル名をフルパスで取得する
    excelFile = Application.CurrentProject.Path & "\" & "0180.xlsm"
    
    'Excelファイルのデータが存在するシート名
    Const SheetNM As String = "work"
    'Excelファイルの定義を取得する
    excelFileConst = "Excel 12.0;HDR=YES;IMEX=1;DATABASE="

以上のコードは、(フルパスで)Excelのファイル名、Excelファイルのデータが存在するシート名、Excelファイルの定義をそれぞれ取得しているコードです。

Excelファイルの定義は、AccessのマクロがExcelのシートのデータを取得する際にヘッダの有無の判定(HDR=YES)、インポートモードの指定(IMEX=1)を定義しています。

ヘッダの有無の判定は、YESなら1行目をフィールド名として扱い、NOなら1行目をデータとして扱います。

今回は1行目をフィールド名として扱いたいのでYESを指定しています。

注目すべきコード③

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

    'Excelアプリケーションのインスタンスを生成する
    Set objExcel = CreateObject("Excel.Application")

以上のコードは、CreateObjectを使ってExcelアプリケーションのインスタンスを生成するコードです。

このインスタンスを生成することにより、Excelからデータを取得することができるようになります。

注目すべきコード④

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

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

以上のコードは、Connectionインスタンスの生成し、接続情報の取得後にExcelファイルのデータソースへの接続を開いている処理のコードです。

Excelファイルのデータソースへの接続を開くためにまずConnectionインスタンスを生成します。

次に接続情報の取得してExcelファイルのデータソースへの接続を開きます。

これでAccessのマクロがExcelのシートにあるデータを参照・取得することができるようになります。

注目すべきコード⑤

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

    'データを取り込むSQLを用意する'
    strSQL = "INSERT INTO [T_商品マスタ]("
    strSQL = strSQL & "[商品コード]"
    strSQL = strSQL & " ,[商品名]"
    strSQL = strSQL & " ,[価格]"
    strSQL = strSQL & ")"
    strSQL = strSQL & "SELECT"
    strSQL = strSQL & "[商品コード]"
    strSQL = strSQL & " ,[商品名]"
    strSQL = strSQL & " ,[価格]"
    strSQL = strSQL & " FROM"
    strSQL = strSQL & " [" & excelFileConst & excelFile & "].[work$A1:C21]"
    
    'SQLの実行'
    db.Execute strSQL

以上のコードは、Excelのデータを取得してAccessのテーブル「T_商品マスタ」に追加するINSERT文を用意して実行(追加)する処理のコードです。

ちなみに、57行目では「セルA1からセルC21まで」と指定しています。

このように、データを取得するのにExcelファイルのセルの範囲を指定することができます。

なお、以上のコードだけではどんなINSERT文なのか分かりにくいと思うので、実行時のINSERT文を以下にお見せします。

INSERT INTO [T_商品マスタ](
     [商品コード]
    ,[商品名]
    ,[価格]
) SELECT
 [商品コード]
,[商品名]
,[価格]
FROM
    [Excel 12.0;HDR=YES;IMEX=1;DATABASE=C:¥work¥10_勉強¥10_VBA関連¥0180¥0180.xlsm].[work$A1:C21]

INSERT文が用意出来たら、60行目のExecuteメソッドの引数にINSERT文を指定して実行するとAccessのテーブル「T_商品マスタ」に取得したデータが追加されます。

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

    'Excelを終了する
    objExcel.Quit

以上のコードは、開いたExcelファイルを閉じる処理のコードです。

Quitメソッドを実行しないとExcelファイルが開いたまま残ってしまうので、必ずQuitメソッドを実行しておきます。

動作確認

マクロ実行前

今回は以下のExcelのシートにあるデータをAccessのテーブル「T_商品マスタ」に追加します。

マクロ実行後

マクロを実行すると、Accessのテーブル「T_商品マスタ」にExcelのシートにあるデータが追加されていることが確認できました。

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

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

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

1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
2. Microsoft Office 15.0 Access databese engine Object Library(ACEDAO.DLL)

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

    Dim conn            As ADODB.Connection     'Connection用変数

    Dim db              As dao.Database         'DAOデータベース用オブジェクト用変数

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

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

最後に

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

もしAccessのマクロからExcelのシートのデータをAccessのテーブルに追加したい場合は本記事を参考にしてみてくださいね。

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

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

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

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