Accessでフォームからデータを追加し、Excelのシートにエクスポートする：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、カレントデータベースを開くOpenRecordsetメソッド、新しいレコードを挿入するAddNewメソッド、レコードを更新するUpdateメソッド、レコードを閉じるCloseメソッド、フォームの全てのコントロールを更新するMe.Recalcメソッドの使い方などについて。

[薬師寺国安，PROJECT KySS]

連載目次

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

　今回は、Accessのフォームからデータを入力し、そのデータをExcelに追加するTIPSを紹介する。もちろん、Accessのテーブルにもデータは追加される。AccessとExcelに同じデータが追加されるので、新たにAccessからExcelにエクスポートする手間が省ける。

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

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

　Accessメニューの「作成」→「フォームデザイン」と選択して、「フォームデザイン」の画面を表示する。「フォーム1」タブの上でマウスを右クリックして、表示されるメニューから「上書き保存」を選択し、「フォーム名」を「AccessフォームからExcelシートにデータを追加フォーム」という名前にしておく。

　すると、フォームの「デザイン画面」のタブが「フォーム1」から「AccessフォームからExcelシートにデータを追加フォーム」という名前に変化する（図1）

図1　「AccessフォームからExcelシートにデータを追加フォーム」という名前のフォームが作成された

　図1の「AccessフォームからExcelシートにデータを追加フォーム」画面に、コントロールを配置していく。配置するコントロールは表1を参照してほしい。

表1　配置するコントロールと「名前」

コントロール名	名前
テキストボックス	商品NOテキストボックス
テキストボックス	商品名テキストボックス
テキストボックス	単価テキストボックス
テキストボックス	在庫テキストボックス
ボタン	書き出しボタン

　表1のコントロールを図2のように配置する。コントロールを配置すると「ウイザード」が表示されるが、今回は全て「キャンセル」を選択する。

図2　各コントロールを配置した

　各ラベルやテキストボックスの書式は「プロパティシート」から設定する。ラベルに指定している項目名は、「書式」タブの「標題」から指定する。後は、「フォント名」「フォントサイズ」「フォント太さ」を読者の好みに応じて設定してほしい。テキストボックスやボタンに付ける「名前」は「その他」タブの「名前」から指定する。

VBEを起動してコードを記述する

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

　すると「VBE」（Visual Basic Editor）が起動して、プロシージャが作成されるので、この中にリスト1のコードを記述する。

Private Sub 書き出しボタン_Click()
    Dim myRecordset As Recordset
    Dim FileName As String
    
    If IsNumeric(単価テキストボックス) = False Or IsNumeric(在庫テキストボックス) = False Then
      MsgBox ("単価または在庫のデータが不正です。")
      Exit Sub
    End If
    Set myRecordset = CurrentDb.OpenRecordset("select * from 商品管理テーブル")
    myRecordset.AddNew
    myRecordset("商品NO") = 商品Noテキストボックス
    myRecordset("商品名") = 商品名テキストボックス
    myRecordset("単価") = 単価テキストボックス
    myRecordset("在庫") = 在庫テキストボックス
    myRecordset.Update
    Me.Recalc
    myRecordset.Close
    Set myRecordset = Nothing
   
    FileName = "C:\Excel2013Data\商品管理データベース.xls"
  
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "商品管理テーブル", FileName, True, "商品管理データ"
    MsgBox (FileName & "を保存しました。")
End Sub

リスト1　 AccessのフォームからExcelのシートにデータを追加するコード

　まず、Recordsetを扱うオブジェクト変数「myRecordset」を宣言する（2行目）。次に、文字列型の「FileName変数」を宣言しておく（3行目）。IsNumeric関数を使って、「単価」と「在庫」に入力された値が数値でない場合は、警告メッセージを発して処理を抜ける（5〜8行目）。

カレントデータベースを開くOpenRecordsetメソッド

　9行目では、カレントデータベースをOpenRecordsetメソッドで開く。OpenRecordsetの引数にはSQL文を指定する。ここでは、「select * from 商品管理テーブル」と指定して、「商品管理テーブル」から全てのレコードを選択するよう指定している。こうすることで、myRecordSetオブジェクト変数に、SQLを引数に持ったカレントデータベースへの参照を代入する。

　「Access SQLを使用してレコードを取得する方法」については、下記のURLを参照してほしい。

• http://msdn.microsoft.com/ja-jp/library/ff822095.aspx

新しいレコードを挿入するAddNewメソッドとレコードを更新するUpdateメソッド

　10行目でAddNewメソッドで新しいレコードを挿入し、11〜14行目で「商品NO」「商品名」「単価」「在庫」のフィールドに、それぞれに対応する名前のテキストボックスで入力されている値を指定している。最後にUpdateメソッドで、レコードを更新する（15行目）。

フォームの全てのコントロールを更新するMe.Recalcメソッド

　Me.Recalcメソッドで、フォームの全てのコントロールを更新する（16行目）。「Me」というのは「自分自身」ということで、「今開いている、このフォーム」のことを指す。「Me」は省略することもできる。

レコードを閉じるCloseメソッド

　17〜18行目では、Closeメソッドでレコードを閉じ、myRecordsetを全ての関連付けから解放している。

Excel側の処理

　20行目で変数FileNameに、フォームで入力したデータをExcelのシートに書き出す完全パス付ファイル名を格納している。今回のファイル名は「商品管理データベース.xls」とする。また事前に「C:\Excel2013Data」フォルダーを作成しておく必要がある。このフォルダーが存在しないとエラーになるので、注意してほしい。

　22行目のDoCmd.TransferSpreadsheetメソッドで、AccessのデータをExcelに出力する。

　TransferSpreadsheetメソッドの書式はTIPS「AccessからExcelに接続して開く方法＆Accessで作成したデータをExcelに出力する方法で学ぶ、連携VBAの基本」でも紹介しているが、確認の意味で再度掲載しておく。

TransferSpreadsheetメソッドの書式

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

　「TransferType」には、変換の種類を指定する。今回は、エクスポートになるので、「acExport」を指定している。

　「SpreadsheetType」には、ワークシートの種類を指定する。今回は、「acSpreadsheetTypeExcel9」を指定している。

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

　「FileName」には、保存する完全パス付きファイル名を指定する。変数「FileName」が格納している「C:\Excel2013Data\商品管理データベース.xls」を指定する。

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

　「Range」には、ワークシートの名前を指定する。今回は「商品管理データ」と指定する。

実行結果

　実行すると、図3のようになる。

図3　Accessのフォームからデータを入力して、Excelのシートに追加した

Access／Excelデータ連携の利便性を高める

　今回は、これで終わりだ。AccessのフォームからExcelのシートにデータをエクスポートするには、「DoCmd.TransferSpreadsheet」メソッドを使用して、引数にエクスポートを表す、「acExport」を指定することでエクスポートできる。

　VBAのコードも短いので、意外と理解しやすいのではないだろうか。これだけの処理で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）。