Access VBAでExcel連携を行う際に、多くの方が利用するのがTransferSpreadsheetです。
これまで本サイトでは、
- Excelファイルのインポート
- Excelファイルへのエクスポート
- 任意シートのインポート
- フォルダ内Excelファイルの一括インポート
などを紹介してきました。 [latest-inf…system.com], [latest-inf…system.com], [latest-inf…system.com]
しかし実際の業務では、
「毎回異なるファイルを取り込みたい」
「出力ファイル名に日付を付けたい」
「複数シートをまとめて処理したい」
など、標準的なサンプルコードだけでは対応できない場面が多くあります。
今回は、Access VBAのTransferSpreadsheetを利用した実務でよく使われる応用テクニックを紹介します。
TransferSpreadsheetとは
TransferSpreadsheetはAccess VBAでExcelとのデータ連携を行うための機能です。
例えば、
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_Import", _
"C:\TEST\Test.xlsx", _
TrueでExcelをAccessへ取り込みできます。
逆に、
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel12Xml, _
"Q_Report", _
"C:\TEST\Report.xlsx", _
TrueでAccessからExcelへ出力できます。 [latest-inf…system.com], [latest-inf…system.com]
応用例① ファイル選択ダイアログからインポートする
サンプルコードではファイル名を固定しています。
しかし実務では毎回ファイル名が異なることも少なくありません。
そこでファイル選択ダイアログを利用します。
Sub ExcelImport()
Dim FilePath As String
With Application.FileDialog(3)
.Show
FilePath = .SelectedItems(1)
End With
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "T_Import",
FilePath, True
End Subこれにより利用者が自由にExcelファイルを選択できます。
応用例② ファイル名へ日付を付けてエクスポートする
レポート出力では非常によく利用します。
Dim FileName As String
FileName =
"C:\Report\売上" & Format(Date, "yyyymmdd") & ".xlsx"結果
売上_20260902.xlsx
のようなファイル名になります。
月次レポートや日次レポートで特に便利です。
応用例③ クエリ結果のみをExcelへ出力する
初心者の方はテーブルをそのまま出力しがちです。
しかし実務ではクエリを利用します。
DoCmd.TransferSpreadsheet _
acExport, acSpreadsheetTypeExcel12Xml, "Q_売上集計", "C:\TEST\Report.xlsx", True流れ
T_売上
↓
Q_売上集計
↓
Excel
必要なデータだけ出力できるため非常に便利です。
応用例④ 特定シートだけインポートする
Excelファイル内に複数シートが存在するケースです。
例えば
Sheet1
Sheet2
Sheet3
がある場合、
Sheet2だけ取り込みたいことがあります。
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_Import", _
FilePath, _
True, _
"Sheet2!"任意シート指定が可能です。 [latest-inf…system.com]
応用例⑤ フォルダ内の全Excelを一括取込する
実務では定型Excelが大量に集まることがあります。
例えば
20260901.xlsx
20260902.xlsx
20260903.xlsx
などです。
この場合、
FileName = Dir("C:\DATA*.xlsx")
Do While FileName <> ""
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_Import", _
"C:\DATA" & FileName, _
True
FileName = Dir()
Loopとすることで一括取込できます。 [latest-inf…system.com]
応用例⑥ 出力完了後にExcelを自動で開く
利用者に喜ばれる機能です。
Application.FollowHyperlink "C:\TEST\Report.xlsx"エクスポート後に自動起動できます。
例えば、
レポート出力
↓
Excel起動
↓
すぐ確認
という流れになります。
応用例⑦ 出力前にファイル存在チェックを行う
運用で非常に重要です。
If Dir(FileName) <> "" Then
Kill FileName
End If既存ファイルを削除してから出力します。
これにより、
同名ファイルが存在するため
保存できません
というエラーを回避できます。
実務で最もよく使う組み合わせ
実際の開発で利用頻度が高いのは次の構成です。
Excel選択
↓
取込テーブル
↓
チェッククエリ
↓
本テーブル
さらに出力時は
本テーブル
↓
集計クエリ
↓
日付付きExcel出力
です。
この構成はほとんどのAccessシステムで利用できます。
TransferSpreadsheetの限界
非常に便利なTransferSpreadsheetですが、苦手なこともあります。
例えば、
セル書式設定
文字色
罫線
背景色
グラフ作成
棒グラフ
円グラフ
特定セルへの出力
A10セルから出力
などです。
このような場合はExcelオブジェクトを利用した処理が必要になります。
Copilotで応用コードを作成する
最近ではCopilotを利用することで応用コードも簡単に作成できます。
例えば、
Access VBAで
フォルダ内の全Excelファイルを
TransferSpreadsheetで
取り込むコードを作成してください。
や
TransferSpreadsheetを利用して
ファイル名へ日付を付与して
Excel出力するコードを作成してください。
と依頼できます。
特に今回紹介したような応用例との相性は非常に良いです。
過去記事との違い
これまでの記事では、
- 基本的なExcelインポート
- Excelエクスポート
- 任意シート取込
- フォルダ内Excel取込
など個別機能を紹介してきました。 [latest-inf…system.com], [latest-inf…system.com], [latest-inf…system.com], [latest-inf…system.com]
今回はそれらを横断し、
TransferSpreadsheetを実務でどのように活用するのか
という視点でまとめています。
Access VBAを活用する上で知っておきたい実践テクニック集として参考にしていただければと思います。
まとめ
TransferSpreadsheetは単なるExcelインポート・エクスポート機能ではありません。
工夫次第で、
- 任意ファイル取込
- 日付付きレポート出力
- 複数シート取込
- フォルダ一括取込
- 出力後自動起動
など様々な業務自動化を実現できます。
特にAccess VBA初心者の方は、まずTransferSpreadsheetを使いこなせるようになるだけでも大きな業務改善につながります。
さらにCopilotを活用すれば応用コードの作成も容易になるため、ぜひ今回紹介したサンプルを業務へ取り入れてみてください。
コメント