AccessからExcelに接続して開く方法＆Accessで作成したデータをExcelに出力する方法で学ぶ、連携VBAの基本：VBA／マクロ便利Tips（2/2 ページ）

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、AccessとExcelをVBAで連携する設定や、AccessからExcelに接続して開く方法、Accessで作成したデータをExcelに出力するDoCmd.TransferSpreadsheetメソッドの使い方などについて解説。

[薬師寺国安，PROJECT KySS]

AccessからExcelを起動する

　さて、準備が終わったので、「AccessからExcelを起動する」Tipsから解説していこう。

Excelを起動するボタンをフォームに設置

　Accessメニューの「作成」→「フォームデザイン」と選択する。フォームのデザイン画面が表示される。コントロールに配置されているアイコンの上にマウスカーソルを載せると、そのコントロールの種類名がツールチップヒントで表示される（図5）。

図5　コントロールの種類名が表示された

　Accessメニューの「コントロール」から「ボタン」を選択し、フォームデザイン画面上にドラッグ＆ドロップして「ボタン」を配置する（図6）。「ボタン」のウイザードが起動するが「キャンセル」する。

図6　「ボタン」を配置した

　「プロパティシート」から「ボタン」のプロパティを設定していく。まず、「書式」タブをクリックし、「標題」に「Excelの起動」と指定する。「フォント名」に「Meiryo UI」、「フォントサイズ」に「14」、「フォント太さ」に「太字」を指定する（図7）。次に、「その他」タブをクリックして、「名前」に「Excel起動ボタン」と指定する。

図7　「ボタン」に書式を設定した

　図7の画面から、上に表示されている「フォーム1」のタブの上で、マウスを右クリックし、「上書き保存」を選択する。すると、保存する「フォーム名」を入力するダイアログボックスが表示されるので、「Excel起動フォーム」と指定して「OK」ボタンを押す。

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

　配置した「Excelの起動」ボタンを選択した状態で、プロパティシートの「イベント」タブをクリックし、「クリック時」の右横にある「v」アイコンをクリックして、［イベント プロシージャ］を選択する。次に、「……」アイコンをクリックする。

　すると「VBE」が起動して、下記のプロシージャが自動で書かれている。この中にリスト1のようにコードを記述する。

Private Sub Excel起動ボタン_Click()
 
End Sub

自動で書かれたコード

Private Sub Excel起動ボタン_Click()
  Dim myExcel As Excel.Application
  Set myExcel = New Excel.Application
  
  myExcel.Visible = True
  
  myExcel.Workbooks.Add
  myExcel.Range("A1").Value = "AccessからExcel起動"
  
  Set myExcel = Nothing
End Sub

リスト1　AccessからExcelを起動するマクロのコード

Excel.Applicationオブジェクト

　まず、2行目でExcel.Application型の変数myExcelを宣言し、3行目で新しいExcel.Application型のインスタンスを作成して、myExcelオブジェクトに代入する。

Excel.Application.Visibleプロパティ

　VisibleプロパティでExcelを起動する（5行目）。

　7〜8行目でExcelのワークブックを追加し、「A1」のセルに「Accessから起動」と書き込む。最後に、10行目でmyExcelオブジェクトを全ての関連付けから解放する。

実行結果

　右隅上の「×」アイコンで「VBE」の画面を閉じると、フォームのデザイン画面が表示される。ナビゲーションウインドーに「Excel起動フォーム」という項目が作成されているので、これをダブルクリックすると、実行画面になる。

　「Excel起動ボタン」をクリックすると、Excelが起動して、「A1」のセルに「AccessからExcel起動」と書かれている（図8）。

図8　「Excelの起動」ボタンでExcelが起動した

　確認ができたら保存しないでExcelを閉じておこう。

AccessのデータをExcelに出力するDoCmd.TransferSpreadsheetメソッド

　次に「AccessのデータをExcelに取り込む」Tipsを紹介しよう。

ボタンをフォームに追加

　ナビゲーションウインドーから「Excel起動フォーム」を選択し、マウスの右クリックで表示されるメニューから、「デザインビュー」を選択する。

　「Excelの起動」ボタンの下に、もう1個「ボタン」を配置し、プロパティシートから、「Excelの起動」に設定したのと同じ書式を設定する。ただし、「書式」タブの「標題」には「AccessデータをExcelに出力」とする（図9）。また「その他」タブの「名前」には、「書き出しボタン」と指定しておく。

図9　「AccessデータをExcelに出力」ボタンを配置した

VBEを起動してマクロのコードを記述する

　「AccessデータをExcelに出力」ボタンを選択した状態で、プロパティシートの「イベント」タブをクリックし、「クリック時」の右横にある「v」アイコンをクリックして、［イベント プロシージャ］を選択する。次に、「……」アイコンをクリックする。

　すると「VBE」が起動して、先ほどと同様にプロシージャのひな型が作成されている。この中にリスト2のコードを記述する。

Private Sub 書き出しボタン_Click()
  Dim FileName As String
  FileName = "C:\Excel2013Data\商品管理.xls"
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "商品管理テーブル", FileName, True, "商品管理"
  MsgBox (FileName & "を保存しました。")
End Sub

リスト2　Accessの先に作成しておいた、「商品管理テーブル」のデータをExcelに書き出すマクロのコード

コードの中身

　2行目で文字列型の変数「FileName」を宣言する。3行目では、変数「FileName」に完全パス付きファイル名を指定する。ここでは、「C:\Excel2013Data」というフォルダーに「商品管理.xls」というExcelファイルを書き出すので、事前に「C:\Excel2013Data」のフォルダーを作成しておいてほしい。このフォルダーが存在していないと、エラーになるので注意が必要だ。

　4行目では、DoCmd.TransferSpreadsheetメソッドで、AccessのデータをExcelに出力する。書式は下記の通り。

DoCmd.TransferSpreadsheetメソッドの書式

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

　「TransferType」には、変換の種類を指定する（表1参照）。

　「SpreadsheetType」には、ワークシートの種類を指定する（表2参照）。

　「TableName」には、ワークシートに書き出すAccessのテーブル名を指定する。

　「FileName」には、保存する完全パス付きファイル名を指定する。

　「HasFieldNames」には、1行目を項目名として使用する場合は「True」、そうでない場合は「False」を指定する。既定値は「False」。

　「Range」には、ワークシート名前を指定する。

　なお、これら全ての引数は省略可能となっている。

表1　TransferTypeに指定する値

名前	説明
acExport	データをエクスポートする
acImport	データをインポートする（既定値）
acLink	指定したデータソースにデータベースをリンクする
AcDataTransferType 列挙（Access）

　今回は、エクスポートしているので、「acExport」を指定している。

表2　SpredsheetTypeに指定する値

名前	説明
acSpreadsheetTypeExcel3	Microsoft Excel 3.0形式
acSpreadsheetTypeExcel4	Microsoft Excel 4.0形式
acSpreadsheetTypeExcel5	Microsoft Excel 5.0形式
acSpreadsheetTypeExcel7	Microsoft Excel 95.0形式
acSpreadsheetTypeExcel8	Microsoft Excel 97形式
acSpreadsheetTypeExcel9	Microsoft Excel 2000形式
AcSpreadSheetType 列挙（Access）

　今回は、「acSpreadsheetTypeExcel9」を指定している。

　「TableName」には「商品管理テーブル」を指定し、「FileName」には変数「FileName」の値を指定している。1行目を項目名としたいので「True」を指定する。「Range」の「ワークシート名」に、「商品管理」と指定しておく。

　最後に5行目で、AccessのデータをExcelに書き出して、保存した旨のメッセージを表示する。

実行結果

　実行して、「AccessデータをExcelに出力」ボタンをクリックすると、「C:\Excel2013Data」というフォルダーに「商品管理.xls」が保存され、ダブルクリックして開くと、Excelの「商品管理シート」に「商品管理テーブル」のデータが書き出されている。「商品管理.xls」を開いた結果が、図10だ。

図10　Accessの「商品管理テーブル」のデータをExcelに書き出した

次回からは、さらに実践的なAccessとExcelの連携Tipsを紹介

　今回はこれで終わりだ。AccessからExcelを起動したり、AccessのテーブルをExcelにエクスポートするしたりするという、かなり基本的な処理を解説した。そんなに、難しいコードではないので、理解しやすかったのではないかと思う。次回からは、さらに実践的なAccessとExcelの連携Tipsを紹介していくので、お楽しみに。

著者紹介

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

薬師寺国安事務所代表。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）。