DoCmd.TransferSpreadsheetメソッドでExcelシートのデータをAccessデータベースに取り込み：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、DoCmd.TransferSpreadsheetメソッドを使ってExcelシートのデータをAccessデータベースに取り込む方法などについて。

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013＋Access 2013

　今回は、Excelシートに記録していた商品管理のデータを、Accessの「商品管理テーブル」に取り込む方法を解説する。

　使用するテーブルは、TIPS「AccessからExcelに接続して開く方法＆Accessで作成したデータをExcelに出力する方法で学ぶ、連携VBAの基本」で作成しておいた、「商品管理テーブル」だ。「商品管理データベース.accdb」をクリックして、Accessを起動しておいてほしい。

Accessのフォームとコントロールの配置

　Accessメニューの「作成」→「フォームデザイン」と選択して、「フォームデザイン」の画面を表示する。

　「フォーム1」タブの上でマウスの右クリックをして、表示されるメニューから「上書き保存」を選択し、「フォーム名」を「Excelデータの読み込みフォーム」という名前にしておく。

　すると、フォームの「デザイン画面」のタブが「フォーム1」から「Excelデータの読み込みフォーム」という名前に変化する。

　「Excelデータの読み込みフォーム」画面に、「ボタン」を1個だけ配置する。ウイザードが表示されるが「キャンセル」しておく。

　「ボタン」を選択して、「プロパティシート」の「書式」タブをクリックする。「標題」に「Excelのデータを読み込む」、「フォント名」に「Meiryo UI」、「フォントサイズ」に「14」、「フォント太さ」に「太字」を指定する。

　次に、「その他」タブをクリックして「名前」に「Excel読み込みボタン」としておく（図1）。

図1　「ボタン」コントロールを配置した

VBEを起動してVBAを記述する

　配置した「Excelのデータを読み込む」ボタンを選択した状態で、プロパティシートの「イベント」タブ→「クリック時」の右横にある「v」アイコン→「イベント プロシージャ」を選択する。次に、「…」アイコンをクリックすると、VBE（Visual Basic Editor）が起動して、「実行ボタン_Click()」のプロシージャのひな型が作成されている。この中にリスト1のコードを記述する。

Private Sub Excel読み込みボタン_Click()
  DoCmd.TransferSpreadsheet acImport, , "商品管理テーブル", "K:\Access_Excel VBA Tips\Accessに追加用商品管理データ.xlsx", True
  MsgBox ("Excelのデータを取り込みました。")
  Exit Sub
End Sub

リスト1　 AccessからExcelシートのデータを取り込むコード

DoCmd.TransferSpreadsheetメソッドでデータをインポートする

　2行目のDoCmd.TransferSpreadsheetメソッドで、Excelのデータをインポートする。書式は下記の通りだ。詳細は、TIPS「AccessからExcelに接続して開く方法＆Accessで作成したデータをExcelに出力する方法で学ぶ、連携VBAの基本」を参照してほしい。

DoCmd.TransferSpreadsheetメソッドの書式

DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range)

　今回は上記Tipsとは違いインポートなので、違いだけ説明する。

　「TransferType」には、変換の種類を指定する。今回はインポートなので、「acImport」を指定する。

　「TableName」には、ワークシートからデータを取り込むAccessのテーブル名を指定する。今回は「商品管理テーブル」を指定する。

　「FileName」には、データを取り込む元になるファイル名を完全パス付きで指定する。上記Tipsでは、拡張子を「.xls」で書き出していたが、今回は「.xlsx」の拡張子のファイルを読み込んでいる。どちらの拡張子でも、読み込み、書き込みは可能なので、どちらを指定しても問題はない。

　今回は「K:\Access_Excel VBA Tips\Accessに追加用商品管理データ.xlsx」を指定している。このExcelシートの中身は図2のようになっている。

図2　Accessにインポートする「Accessに追加用商品管理データ.xlsx」の中身

　最後にデータを取り込んだ旨のメッセージを表示する（3行目）。

実行結果

　実行すると、図3のようになる。テーブルのデータが反映されていない場合は、Accessメニューの「すべて更新」を選択すると、データが反映される。

図3　Accessのフォームから「Excelのデータを読み込む」ボタンをクリックして、「商品管理テーブル」にExcelのデータがインポートされた

DoCmd.TransferSpreadsheetメソッドをインポート／エクスポートで使い分けよう

　今回は、これで終わりだ。今回も、他のTipsで何度も解説した、DoCmd.TransferSpreadsheetメソッドを使用して、引数にインポートを表す、「acImport」を指定している。ExcelのデータをAccessのテーブルに読み込むわけだから、「acImport」になる。

　Accessのテーブルのデータを、Excelのシートにインポートやエクスポートするには、このメソッドを使用するだけで実現できる。何とも便利なメソッドではないだろうか。

著者紹介

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev （Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。

Microsoft MVP for Development Platforms - Windows Platform Development (Oct 2014-Sep 2015）。