AccessへExcelデータを取り込む際、多くの方がまず利用するのが「TransferSpreadsheet」メソッドです。しかし、この方法には大きな落とし穴があります。それは、同じExcelファイルを再度取り込むとデータが重複登録されてしまう点です。
顧客管理や売上管理などの業務では、毎日または毎月データを取り込むケースが多いため、重複データが蓄積されると集計結果が正しくなくなり、業務品質の低下につながります。
そこで今回は、Access VBAを利用してExcelデータをインポートする際に、既に登録済みのデータを自動判定し、新規データのみを登録する方法を紹介します。さらに、既存データを更新する応用方法についても解説します。
重複データが発生する原因
これまで紹介してきたTransferSpreadsheetを用いたインポート処理では、ExcelのデータをそのままAccessテーブルへ追加します。
例えば次のような顧客データを考えてみます。
| 顧客コード | 氏名 |
|---|---|
| A001 | 山田太郎 |
| A002 | 鈴木花子 |
| A003 | 佐藤一郎 |
初回のインポートでは問題ありません。
しかし、翌日に同じExcelファイルを取り込んだ場合、Accessテーブルは以下のようになります。
| 顧客コード | 氏名 |
|---|---|
| A001 | 山田太郎 |
| A002 | 鈴木花子 |
| A003 | 佐藤一郎 |
| A001 | 山田太郎 |
| A002 | 鈴木花子 |
| A003 | 佐藤一郎 |
この状態では正しい件数集計や分析ができなくなります。
そのため実務では、
- 登録済みデータか判定する
- 未登録データのみ追加する
- 必要に応じて更新する
という処理が非常に重要になります。
今回のサンプルデータ
まずExcel側で次のデータを用意します。
| 顧客コード | 氏名 | 地域 |
|---|---|---|
| A001 | 山田太郎 | 東京 |
| A002 | 鈴木花子 | 神奈川 |
| A003 | 佐藤一郎 | 千葉 |
ファイル名は以下とします。
C:\TEST\Customer.xlsxAccessテーブルの準備
Access側には次のテーブルを作成します。
テーブル名
T_Customer
構造は以下の通りです。
| フィールド名 | データ型 |
|---|---|
| 顧客コード | 短いテキスト |
| 氏名 | 短いテキスト |
| 地域 | 短いテキスト |
ポイントは「顧客コード」を一意に管理することです。
実務では、
- 社員番号
- 取引先コード
- 商品コード
など重複しない項目を判定キーとして使用します。
DCount関数で重複チェックする
Access VBAにはDCount関数があります。
指定した条件に一致するレコード件数を取得できます。
例えば、
DCount("*", "T_Customer", _
"顧客コード='A001'")の場合、
A001が存在する場合
1
存在しない場合
0
が返ります。
この値を利用することで重複判定が可能になります。
VBAによる重複チェック付きインポート
今回はExcelを1件ずつ読み込みながら重複判定を行います。
標準モジュールを作成し、次のコードを記述してください。
Public Sub ExcelImportCheck()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim LastRow As Long
Dim i As Long
Dim CustomerCode As String
Dim CustomerName As String
Dim Area As String
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\TEST\Customer.xlsx")
Set xlSheet = xlBook.Worksheets("Sheet1")
LastRow = xlSheet.Cells(xlSheet.Rows.Count, 1).End(-4162).Row
For i = 2 To LastRow
CustomerCode = xlSheet.Cells(i, 1).Value
CustomerName = xlSheet.Cells(i, 2).Value
Area = xlSheet.Cells(i, 3).Value
If DCount("*", "T_Customer", "顧客コード='" & CustomerCode & "'") = 0 Then
CurrentDb.Execute _
"INSERT INTO T_Customer " & _
"(顧客コード,氏名,地域) " & _
"VALUES('" & _
CustomerCode & "','" & _
CustomerName & "','" & _
Area & "')"
End If
Next i
xlBook.Close False
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
MsgBox "インポートが完了しました。"
End Sub処理内容の解説
Excelファイルを開く
Set xlBook = xlApp.Workbooks.Open( "C:\TEST\Customer.xlsx")指定したExcelファイルを開きます。
最終行を取得する
LastRow = _
xlSheet.Cells(xlSheet.Rows.Count, 1) _
.End(-4162).RowExcelデータの最終行を取得します。
これにより件数が増減しても対応できます。
重複判定を行う
If DCount("*", "T_Customer", "顧客コード='" & CustomerCode & "'") = 0 Then顧客コードが存在しない場合のみ、
新規登録
を実施します。
データを登録する
CurrentDb.Execute "INSERT INTO T_Customer ..."登録済みでなければレコードを追加します。
再度同じExcelを取り込んでも重複登録は発生しません。
更新処理も行う方法
実務では次のケースもあります。
初回
| 顧客コード | 氏名 | 地域 |
|---|---|---|
| A001 | 山田太郎 | 東京 |
Excel更新後
| 顧客コード | 氏名 | 地域 |
|---|---|---|
| A001 | 山田太郎 | 埼玉 |
この場合は重複なので登録しないのではなく、
東京 → 埼玉
へ更新したいことがあります。
その場合は次のようにします。
If DCount("*", T_Customer", "顧客コード='" & CustomerCode & "'") > 0 Then
CurrentDb.Execute _
"UPDATE T_Customer " & _
"SET 氏名='" & CustomerName & "'," & _
"地域='" & Area & "' " & _
"WHERE 顧客コード='" & _
CustomerCode & "'"
End Ifこれにより最新情報で上書きできます。
実務でおすすめの構成
大規模データを扱う場合は次の構成がおすすめです。
①取込専用テーブル
T_Import
Excelデータを一時的に取り込む
②重複チェッククエリ
Q_CheckDuplicate
既存データとの差分確認
③本テーブル
T_Customer
正式データ保管
この設計にしておくと、
- インポート失敗時の調査
- エラーデータの確認
- ログ管理
が容易になります。
実際の業務システムではこちらの構成が一般的です。
Copilotを活用してコードを作成する方法
最近ではMicrosoft Copilotを利用することでVBAコードの作成を効率化できます。
例えば次のように質問します。
Access VBAでExcelファイルを読み込み、
顧客コードで重複判定し、
未登録データのみ追加するコードを作成してください。
すると今回紹介したようなコードのひな型を短時間で生成できます。
その後、
- テーブル名
- フィールド名
- ファイルパス
を修正することで業務に適用できます。
Copilotは特に
- SQL文作成
- エラー処理追加
- ログ出力追加
といった作業で大きな効果を発揮します。
よくあるエラーと対処法
「データ型が一致しません」
原因
- Access側が数値型
- Excel側が文字列
対策
- Excel側のデータ形式を統一する
- VBAで型変換する
「ファイルが見つかりません」
原因
C:\TEST\Customer.xlsxが存在しない
対策
Dir()で事前チェックを実装する
重複判定が遅い
原因
大量データをDCountで逐次検索
対策
- インデックス設定
- クエリ方式への変更
- 一時テーブル活用
を実施する
まとめ
今回は、Access VBAでExcelインポート時の重複データを自動判定して登録する方法を紹介しました。
単純なTransferSpreadsheetは便利ですが、同じデータを何度も取り込むと重複レコードが発生してしまいます。
そこでDCount関数を利用して既存データを確認し、
- 存在しない場合は新規登録
- 存在する場合は更新
という仕組みを導入することで、実務で利用できるインポート処理を構築できます。
さらにCopilotを活用することで、重複チェックやSQL作成の手間を大幅に削減できます。今後Access VBAを活用する際は、ぜひ今回のサンプルコードをベースに自社業務へ応用してみてください。
コメント