業務で複数のExcelファイルを集約する作業が発生するなら、Access VBAで自動化するのがおすすめです。
月次報告や各拠点から集まるデータでは、「複数のExcelを1つにまとめる」という作業が頻繁に発生します。しかし手作業では時間がかかり、転記ミスも起こりがちです。
今回はVBA逆引きシリーズとして、「複数Excelファイルの自動統合」をテーマに、実務で使えるテクニックを紹介します。
Excel統合作業でよくある課題
例えば次のようなケースです。
- 全国拠点から売上データが届く
- 各担当者が作成したExcelを集約する
- 月末に複数部門の実績をまとめる
- 点検データを一括管理する
担当者が毎回コピー＆ペーストしていると、
- 集計漏れ
- 二重登録
- 工数増加
が発生します。
Access VBAを使えば、フォルダ指定だけで統合作業を自動化できます。
VBA逆引き① Excelファイルを取り込む
まずは1つのExcelファイルを取り込む方法です。
サンプルコード
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_売上", _
"C:\Data\売上.xlsx", _
True
ポイント
TransferSpreadsheetを利用すると、
- xlsx
- xls
- xlsm
などのファイルを簡単に取り込めます。
VBA逆引き② 指定シートのみ取り込む
対象シートを限定する場合です。
サンプルコード
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_売上", _
"C:\Data\売上.xlsx", _
True, _
"売上一覧$"
利用例
- 集計用シートのみ
- マスタシートのみ
- テンプレート運用
不要なデータを除外できます。
VBA逆引き③ フォルダ内のExcelを一括取込
実務では最も利用頻度が高い方法です。
サンプルコード
Dim strPath As String
Dim strFile As String
strPath = "C:\Data"
strFile = Dir(strPath & "*.xlsx")
Do While strFile <> ""
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"T_売上", _
strPath & strFile, _
True
strFile = Dir()
Loop
メリット
担当者はフォルダへExcelを保存するだけです。
集計担当者の作業はほぼ不要になります。
VBA逆引き④ 取込元ファイル名を記録する
後からデータの出所を確認したい場合があります。
取込テーブルに
取込ファイル名
列を準備します。
サンプルコード
CurrentDb.Execute _
"UPDATE T_売上 " & _
"SET 取込ファイル名 = '" & strFile & "' " & _
"WHERE 取込ファイル名 Is Null"
活用例
- データ監査
- 原本確認
- トレーサビリティ
VBA逆引き⑤ 重複データを除外する
同じファイルを誤って登録するケースがあります。
主キーで管理
例
社員番号
日付
を複合キーに設定します。
VBA例
CurrentDb.Execute _
"DELETE FROM T_売上 " & _
"WHERE 社員番号 Is Null"
ポイント
テーブル設計で重複防止を考慮すると運用が安定します。
VBA逆引き⑥ ファイル名から年月を取得する
実務で非常に便利なテクニックです。
例えば
売上_202607.xlsx
というファイル名の場合、
年月を自動登録できます。
サンプルコード
Dim strYm As String
strYm = Mid(strFile, 4, 6)
取得結果
202607
これを管理項目として保存できます。
VBA逆引き⑦ Excelアプリを開かず高速取込
よくある誤解として、
「Excel VBAで開いてから読まなければならない」
と思われています。
しかしAccessでは
TransferSpreadsheet
だけで直接読み込めます。
効果
- 高速処理
- メモリ節約
- 安定運用
大量データでは特に有効です。
VBA逆引き⑧ 取込エラーをログに残す
運用開始後に重要になります。
ログテーブル例
| 項目 | 内容 |
|---|---|
| 取込日時 | 日時 |
| ファイル名 | 文字列 |
| 結果 | 成功・失敗 |
| エラー内容 | 文字列 |
サンプルコード
CurrentDb.Execute _
"INSERT INTO T_取込ログ " & _
"(取込日時,ファイル名,結果) " & _
"VALUES(#" & Now() & "#,'" & _
strFile & "','成功')"
効果
障害発生時の調査が容易になります。
VBA逆引き⑨ 処理済みファイルを移動する
再取込防止に有効です。
サンプルコード
Name _
"C:\Data" & strFile _
As _
"C:\Data\Done" & strFile
運用例
Data
├─ Incoming
├─ Done
└─ Error
実務システムでは定番構成です。
VBA逆引き⑩ すべて自動化する統合処理
実際の運用では以下の流れになります。
STEP1
フォルダ監視
STEP2
Excel取込
STEP3
重複チェック
STEP4
ログ登録
STEP5
処理済みフォルダへ移動
STEP6
結果通知
これだけで月次集計業務が大幅に効率化できます。
実務でおすすめのテーブル構成
T_売上
| 項目 |
|---|
| 社員番号 |
| 売上日 |
| 金額 |
| 取込年月 |
| 取込ファイル名 |
T_取込ログ
| 項目 |
|---|
| 処理日時 |
| ファイル名 |
| 結果 |
| 件数 |
| エラー内容 |
これだけで中小規模の業務システムなら十分運用可能です。
AccessがExcel集約に強い理由
Excelだけで集計すると、
- ファイル数増加
- 処理速度低下
- 同時編集問題
が発生します。
一方Accessでは、
- データベース管理
- 高速検索
- クエリ集計
- レポート出力
が利用できます。
Excelをデータ入力ツール、Accessを集計基盤として使い分けると非常に効率的です。
まとめ
今回はVBA逆引きとして「複数のExcelファイルを自動統合する方法」を紹介しました。
ポイントは以下のとおりです。
- TransferSpreadsheetでExcelを簡単に取込可能
- フォルダ内ファイルの一括処理ができる
- ファイル名管理で追跡性を確保できる
- ログ管理とエラー処理が重要
- 月次集計や拠点集約業務に最適
Excelを扱う現場は非常に多く、Accessによる自動統合は今でも高い業務改善効果があります。特に「毎月同じ集計作業をしている」という方は、一度仕組み化を検討してみてはいかがでしょうか。
コメント