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

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

» 2015年10月02日 05時00分 公開
[薬師寺国安PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本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)。


Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。