Access VBAでシステム開発をしていると、
- Excelファイルを取り込みたい
- AccessのデータをExcelへ出力したい
- 毎月の報告書を自動作成したい
- CSVではなくExcel形式で連携したい
- 利用者が扱いやすい帳票を作りたい
という要望が頻繁に発生します。
Access VBAでExcel連携を行う場合、まず覚えるべきなのがDoCmd.TransferSpreadsheetです。
Excel操作と聞くと、
Excel.Application
Workbook
Worksheet
を使うイメージがありますが、多くの業務システムではTransferSpreadsheetだけで十分な場合が少なくありません。
特に、
Excel取込
Excel出力
日報集計
売上管理
マスタ取込
などで圧倒的によく利用されます。
- TransferSpreadsheetとは
- 何が便利なのか
- 基本構文
- 逆引き① Excelを取り込みたい
- 逆引き② Excelへ出力したい
- 逆引き③ クエリ結果をExcel出力したい
- 逆引き④ 指定シートを取り込みたい
- 逆引き⑤ マスタ取込を自動化したい
- 実務活用例① 顧客マスタ取込
- 実務活用例② 売上集計レポート
- 実務活用例③ 設備点検結果取込
- 実務活用例④ 請求データ出力
- ImportとExportの違い
- Excel形式の指定
- よくあるエラー
- ベテラン開発者の定番コード
- Copilot活用術① Excel取込コード作成
- Copilot活用術② Excel出力作成
- Copilot活用術③ エラー対策追加
- TransferSpreadsheetが向いている処理
- 開発現場での利用頻度
- まとめ
TransferSpreadsheetとは
TransferSpreadsheetは、
Excel
↓
Access
または
Access
↓
Excel
を実現するAccess標準機能です。
何が便利なのか
通常のExcel連携です。
CreateObject("Excel.Application")から始まり、
Excel起動
ブック操作
シート操作
セル操作
が必要になります。
TransferSpreadsheetなら、
DoCmd.TransferSpreadsheetだけで完了します。
基本構文
DoCmd.TransferSpreadsheet _
処理区分, _
Excel種類, _
テーブル名, _
ファイル名, _
見出し有無
逆引き① Excelを取り込みたい
最も利用頻度が高い処理です。
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_CUSTOMER", _
"C:\DATA\Customer.xlsx", _
True結果
Excel
↓
T_CUSTOMER
へ取込できます。
逆引き② Excelへ出力したい
帳票出力です。
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel12Xml, _
"T_CUSTOMER", _
"C:\DATA\Customer.xlsx", _
True結果
Access
↓
Excel
へ出力できます。
逆引き③ クエリ結果をExcel出力したい
実務ではこちらの方が多いです。
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel12Xml, _
"Q_SALES", _
"C:\DATA\Sales.xlsx", _
True活用例
売上集計
月次報告
管理資料
です。
逆引き④ 指定シートを取り込みたい
シート名指定です。
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_CUSTOMER", _
"C:\DATA\Customer.xlsx", _
True, _
"Sheet1$"効果
必要なシートだけ取り込めます。
逆引き⑤ マスタ取込を自動化したい
マスタ更新です。
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_PRODUCT", _
strFile, _
True効果
ボタン一つで更新できます。
実務活用例① 顧客マスタ取込
営業部門から受領したExcelです。
流れ
Excel受領
↓
TransferSpreadsheet
↓
Access登録
効果
入力作業不要
実務活用例② 売上集計レポート
毎月の定例業務です。
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel12Xml, _
"Q_MONTHLY_SALES", _
strFile, _
True効果
Excel資料自動作成
実務活用例③ 設備点検結果取込
設備管理システムです。
Excel
設備番号
測定値
判定結果
Access
履歴テーブル登録
できます。
実務活用例④ 請求データ出力
経理業務です。
クエリ
Q_INVOICE
出力
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel12Xml, _
"Q_INVOICE", _
strFile, _
True効果
請求資料自動作成
ImportとExportの違い
Import
acImport
意味
Excel → Access
Export
acExport
意味
Access → Excel
初心者が最も間違えやすいポイントです。
Excel形式の指定
現在は基本的に次を利用します。
acSpreadsheetTypeExcel12Xml
対応形式
.xlsx
現在のExcelならこれで問題ありません。
よくあるエラー
ファイル不存在
Dir(strFile)
で事前確認しましょう。
シート名間違い
“Sheet1$”
を確認しましょう。
テーブル項目不一致
列名が違う場合は取り込みエラーになります。
ベテラン開発者の定番コード
存在確認付きです。
If Dir(strFile) <> "" Then
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_CUSTOMER", _
strFile, _
True
End If非常によく利用されるパターンです。
Copilot活用術① Excel取込コード作成
Copilotへの依頼例です。
Access VBAです。
TransferSpreadsheetを利用して
ExcelをT_CUSTOMERへ
取り込むコードを
作成してください。
Copilot活用術② Excel出力作成
Q_SALESをExcelへ出力する
VBAコードを作成してください。
Copilot活用術③ エラー対策追加
TransferSpreadsheetコードへ
エラーハンドリングを
追加してください。
TransferSpreadsheetが向いている処理
特に活躍するのは次の用途です。
- Excelマスタ取込
- 売上集計出力
- 顧客情報取込
- 設備管理データ取込
- 測定データ取込
- 月次報告書出力
- 請求資料作成
開発現場での利用頻度
| 順位 | 用途 |
|---|---|
| 1位 | Excel取込 |
| 2位 | Excel出力 |
| 3位 | マスタ更新 |
| 4位 | 集計資料作成 |
| 5位 | 帳票出力 |
Access VBA開発では非常によく利用される機能です。
まとめ
DoCmd.TransferSpreadsheetは、Access VBAでExcel連携を行うための最重要機能の一つです。
特に
- Excel取込
- Excel出力
- マスタ更新
- 売上集計
- 設備管理
- 線量測定データ管理
などで大きな効果を発揮します。
まずは次の2つを覚えましょう。
acImport
acExport
そして、
DoCmd.TransferSpreadsheet
を使いこなせるようになると、業務システムの自動化範囲が一気に広がります。
Copilotを活用すれば、
- Excel連携コード生成
- エラー処理追加
- マスタ取込処理作成
も効率的に進められます。
Access開発者なら必ず習得しておきたい定番機能です。
コメント