AccessへExcelデータを取り込む方法はいくつかありますが、私がおすすめするのはVBAによるインポートです。
過去の記事では、
- Access標準機能
- VBA
- Copilot＋VBA
の3つの方法を比較しました。
その結果、定期的な業務で利用するのであればVBAによるインポートが最もバランスが良いという結論になりました。標準機能は手軽ですが毎回手作業が必要になり、Copilotは便利な反面、最終的にはVBAを理解する必要があります。まずはVBAによるインポートを習得することで、多くの業務自動化に応用できるようになります。 [latest-inf…system.com], [latest-inf…system.com]
今回は、Access VBAで最も利用されるExcelインポート方法であるTransferSpreadsheetについて詳しく解説します。
なぜVBAによるインポートがおすすめなのか
まずはAccess標準機能との違いを見てみましょう。
Access標準機能
外部データ
↓
Excel選択
↓
ファイル選択
↓
テーブル選択
↓
インポート実行
毎回同じ操作を繰り返します。 [latest-inf…system.com]
VBAの場合
ボタンを押す
↓
完了
になります。
一度作ってしまえば何度でも利用できるため、業務効率が大幅に向上します。 [latest-inf…system.com]
TransferSpreadsheetとは
Access VBAでExcelを取り込む場合、最も利用されるのが
DoCmd.TransferSpreadsheetです。
このメソッドを利用することで、
- Excel→Access
- Access→Excel
の双方のデータ連携が実現できます。 [latest-inf…system.com]
最もシンプルなインポートコード
まずは最低限のコードから見てみましょう。
Sub ExcelImport()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "T_Import", "C:\TEST\Test.xlsx", True
End Subわずか1行です。
引数を理解しよう
コードを見ると少し難しく感じるかもしれません。
一つずつ確認していきます。
第1引数
acImport
インポート処理を意味します。
エクスポートの場合は
acExport
になります。 [latest-inf…system.com]
第2引数
acSpreadsheetTypeExcel12Xml
Excelファイル形式です。
現在利用されている
xlsx
形式であればこれで問題ありません。
第3引数
“T_Import”
インポート先のテーブルです。
第4引数
“C:\TEST\Test.xlsx”
Excelファイルの場所を指定します。
第5引数
True
Excelの1行目をフィールド名として扱います。
例えば、
| 社員番号 | 氏名 |
|---|---|
| 1001 | 山田 |
| 1002 | 鈴木 |
の場合、
社員番号
氏名
をフィールド名として認識します。 [latest-inf…system.com]
サンプルデータを作成してみる
Excelファイルを準備します。
Test.xlsx
| 社員番号 | 氏名 | 所属 |
|---|---|---|
| 1001 | 山田太郎 | 営業部 |
| 1002 | 鈴木花子 | 総務部 |
| 1003 | 佐藤一郎 | 技術部 |
Access側の準備
テーブル
T_Import
を作成します。
フィールドは以下です。
| フィールド名 | データ型 |
|---|---|
| 社員番号 | 数値型 |
| 氏名 | 短いテキスト |
| 所属 | 短いテキスト |
VBAを実行してみる
作成したコードを実行します。
すると、
Test.xlsx
↓
T_Import
へデータが取り込まれます。
複雑なSQLやADOを利用しなくても簡単に実現できるのがTransferSpreadsheetの強みです。 [latest-inf…system.com]
ファイル選択を利用する
実務ではファイル名が毎回変わるケースもあります。
その場合はファイル選択ダイアログを利用します。
Sub ExcelImport()
Dim FilePath As String
FilePath = Application.FileDialog(3).SelectedItems(1)
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_Import", _
FilePath, _
True
End Sub利用者が任意のExcelファイルを選択できるようになります。
よくあるエラー
データ型不一致
最も発生しやすいエラーです。
例えば、
Access側 数値型
なのに、
Excel側 文字列
が入っている場合です。
対策としては、
- Excel形式を統一
- Accessテーブル構造を確認
を実施します。
ファイルが開いている
他の利用者がExcelを開いている場合、
ファイルにアクセスできません
`というエラーになることがあります。
実行前に対象ファイルを閉じるようにしましょう。
フィールド名が一致しない
Excel 社員番号
Access 社員ID
のようになっている場合です。
インポート時に意図しないフィールドが作成されることがあります。
実務では取込専用テーブルを作ろう
初心者の方は直接本テーブルへ取り込むことが多いですが、おすすめはしません。
実務では一般的に以下の構成を利用します。
Excel
↓
T_Import
↓
チェッククエリ
↓
本テーブル
この構成にすると、
- 重複チェック
- エラーチェック
- 再実行
が簡単になります。
また将来的に、
- 重複除外
- 差分更新
- 履歴管理
などにも対応しやすくなります。
Copilotと組み合わせるとさらに便利
最近ではCopilotを活用してVBAコードを作成できます。
例えば、
AccessのT_Importテーブルへ
Excelファイルを取り込むVBAを作成してください。
と依頼すると、TransferSpreadsheetを利用したコードのひな型を生成できます。
さらに
重複チェックを追加してください
や
エラー時にログ出力してください
などの指示も可能です。
ただし、生成されたコードを理解するためにもTransferSpreadsheetの基本を知っておくことが重要です。
VBAを覚えると次のステップへ進める
TransferSpreadsheetはAccess自動化の入口です。
これを習得すると、
- 任意シート取込
- フォルダ内一括取込
- 重複データ除外
- 差分更新
- 定期バッチ処理
へ発展できます。 [latest-inf…system.com], [latest-inf…system.com]
つまり、最初に学ぶべきAccess VBAの機能として非常におすすめです。
まとめ
AccessへExcelデータを取り込む方法はいくつかありますが、定期業務で利用するのであればVBAによるインポートがおすすめです。
特にTransferSpreadsheetを利用すると、わずか数行のコードでExcelデータを取り込むことができます。
最初は単純なインポート処理から始めて、慣れてきたら
- 重複チェック
- 複数シート取込
- フォルダ一括取込
へ発展させることで、Accessによる業務効率化を大きく推進できます。
最近ではCopilotを活用してコード作成も効率化できますが、まずは今回紹介したTransferSpreadsheetの基本を理解することが、Access VBA活用への第一歩になるでしょう。
コメント