この記事では、AccessのマクロがExcelのシートのデータを取得してテーブルに追加する方法についてご説明します。
【動画】AccessのマクロからExcelのマクロを実行する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
AccessのマクロがExcelのファイルに接続してデータを取り込むInsert文を実行することで、取得したデータをAccessのテーブルに追加しています。
マクロ作成の流れ
このConnectionオブジェクトのインスタンスが生成されていないとAccessのマクロがExcelにアクセスすることができないので必ず生成しておきます。
AExcelのデータベース接続情報は、Excelファイルの置き場所やExcelファイルのファイル名などの情報です。
コードの例
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」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
- 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のスキルや基礎」を学ぶのにおすすめですよ。