Accessでは、メニュー（外部データ→新しいデータソース）からExcelデータを手動でインポートできます。
しかし、この操作を定期的に行う場合、毎回手作業で操作するのは効率が悪くなります。
そこで本記事では、フォーム上のボタンをクリックするだけで Excel データを自動インポートできる仕組みの作り方を紹介します。
Accessで作成する業務ツールでは定番の機能ですので、ぜひ習得しておきましょう。
手順
手順は、大きく次の３工程です。
- 取込先テーブルと取込元データのフィールド名等を確認する
- 取込ボタンを設置するフォームを作成する
- ボタンにVBAコード（
TransferSpreadsheetメソッド）を設定する
取込先テーブルと取込元データのフィールド名等を確認する
取込先の既存テーブルと取込元であるExcelデータは、項目名及び項目数が一致している必要があります。
既に手動で取込しているデータであれば問題ないと思いますが、初めて取り込むEXcelデータであれば確認しておきましょう。
《例：商品管理マスタにデータを取り込む場合》
| 【Access】フィールド名 | 【Excel】項目名 |
|---|---|
| 商品コード | 商品コード |
| 商品名 | 商品名 |
| 単価 | 単価 |
こんな感じで一致している必要があります。
《エラー例》
仮にExcel側の項目数が多い場合は、「インデックスが有効範囲にありません」のエラーが表示されます。
また名称が異なると、この場合もエラーとなり先に進みません。
| 【Access】フィールド名 | 【Excel】項目名 |
|---|---|
| 商品コード | 商品コード |
| 商品名 | 商品名称 ← Access側と名称が異なる |
| 単価 | 所属人数 ← Access側に存在しない |
| 単価 |
取込ボタンを設置するフォームを作成する
取込ボタンを設置するフォームを作成します。今回はサンプルとして、ボタン一つだけのシンプルなフォームにしてみました。
このボタンの動作として、ExcelデータをAccessの既存テーブルにインポートする処理をVBAで設定します。
なお、今回は単純なフォームで行いますが、実務の業務用ツールでは、外部データというメニューに、インポートやエクスポート機能をまとめて配置するケースが多いです。
ボタンにVBAコード「TransferSpreadsheet」メソッドを設定する
それでは、いよいよメインとなる設定です。
既存テーブルにExcelデータをインポートするには「TransferSpreadsheet」メソッドを使用します。
TransferSpreadsheetの構文
Docmd.TransferSpreadsheet(TransferType,SpreadsheetType,TableName, FileName, asFieldNames, Range)
TransferSpreadsheetメソッドは、Excelデータに関するインポート/エクスポートの両方を実行できる命令です。
まず引数TransferTypeでインポート/エクスポートのどちらを行うか指定し、その他の引数はその両方の動作に対応できます。
各引数を説明します。
| 引数 | 必須/省略 | 内容 | 具体例 |
|---|---|---|---|
TransferType | 必須 | インポート/エクスポート/リンクのいずれかを指定 | acImport(インポート) |
SpreadsheetType | 必須 | ファイル形式を指定 Excelバージョンに対応 | ※Excel2007以降はacSpreadsheetTypeExcel12Xml |
TableName | 必須 | 取込先または作成先のテーブル/クエリ名 | “T_商品管理マスタ“ |
FileName | 必須 | 対象のExcelファイルパスを指定 | “C:\Excel\Data.xlsx“ |
HasFieldNames | 省略可 | 1行目をフィールド名として扱うかどうか指定 | True |
Range | 省略可 | 取り込むセル範囲やシート指定が可能 未指定ならシート全体を指定 | “Sheet1!A1:D3“ |
各引数の役割をもっと、5W1H的に分かりやすく表すと以下のイメージになります。
TransferType→ 何をする？SpreadsheetType→ ファイル形式は？TableName→ どこへ？FileName→ どこから？HasFieldNames→ 見出しはある？Range→ どこまで？
こんな風に覚えておくと分かりやすいかもです
あと、VBE画面で実際にコードを入力する際は、自動メンバー表示による選択できるので、
綴りを全部覚える必要はないですよ
VBAコード具体例
では、作成したフォームのコマンドボタンに対して、「クリック時」のイベントプロシージャを設定しましょう。
Private Sub btnImport_Click()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "T_商品管理マスタ", "C:\Excel\Data.xlsx", True
MsgBox "インポートが完了しました。", vbInformation
End Sub
ファイルパス「C:\Excel\Data.xlsx」の場所に、取込元ファイルがきちんと保存されていれば、上記のコードでデータがインポートされます。
また、DoCmd.TransferSpreadsheetメソッドは、一行が長くなりがちで、可読性が低下するため、以下のような記載方法でも可能です。
Private Sub btnImport_Click()
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:="T_商品管理マスタ", _
FileName:="C:\Excel\Data.xlsx", _
HasFieldNames:=True
MsgBox "インポートが完了しました。", vbInformation
End Sub
各引数名を明示したうえ改行し、一行ずつ記載しています。文字数は多いですが見やすくなります。
より汎用的に使用するには
今回ご紹介したコードは、引数FileNameに直接ファイルパスを書き込んでいるため、毎回固定の場所にファイルを保存しておく必要があります。
もちろん、そういった運用で問題なければそのままでいいのですが、実務では、取り込むべきファイルが毎回同じ場所に保存されているとは限りません。
このようなケースに対応するには、ファイルを選択するためのダイアログボックスを表示し、ユーザが自由にファイルを選択できるようにします。
ダイアログボックスとは、このようなものです。
ExcelやWordを操作し「名前を付けて保存」を実行した際の保存先選択画面としてもおなじみですね。
VBAでは、こういったWindowsの機能を呼び出して使用することができます。
ダイアログボックスによるファイル指定方法の詳細は、こちらの記事で解説しています。
まとめ
今回は、Accessで業務用ツールを作成するうえで必須ともいえる、フォームから外部データ（Excelデータ)を取り込む方法を解説しました。
実際にツールの機能として組み込むためには、ファイルが無かった又はファイルの形式に不備がある場合のエラー対応やインポート進行状況表示、本文でも触れたダイアログボックスによるファイル指定など、様々な工夫が必要になります。
まずは、TransferSpreadsheetの基本構文を習得し、徐々に応用的な使い方を進めていきましょう。
・Accessで業務用ツールを作ってみたいけど、なかなか時間がとれない。
・先輩が作った既存のツールを改修したいけど難しい…。
そのような場合には、私がみなさまの代わってカタチにいたします。ぜひ作成サービス
をご利用ください。
