【中級】DoCmd.TransferSpreadsheetの罠：インポート時のデータ型不一致を回避する「インポート定義」の活用
皆さん、こんにちは！Access VBAの世界へようこそ。今日は、ExcelファイルをAccessにインポートする際によく遭遇する「あのエラー」、そう、「フィールドの型が一致しません」という、ちょっと厄介なエラーを華麗に回避する方法について、じっくりお話ししたいと思います。
マクロの記録から一歩進んで、VBAで自動化の世界を広げていきたい皆さん、そして、Access VBAの基本をしっかりマスターしたい皆さんにとって、この知識はきっと強力な武器になるはずです。
Excelインポートの落とし穴：なぜ「型不一致」エラーは起こるのか？
Access VBAでExcelファイルをインポートする際、最も手軽な方法の一つが `DoCmd.TransferSpreadsheet` メソッドを使うこと。これ、本当に便利なんですよね。でも、この便利さの裏には、ちょっとした落とし穴が潜んでいます。
例えば、Excelのシートにこんなデータがあったとしましょう。
| 名前 | 年齢 | 登録日 |
| —— | —- | ——– |
| 山田太郎 | 30 | 2023/10/27 |
| 佐藤花子 | 25 | 2023/10/28 |
| 鈴木一郎 | | 2023/10/29 |
このExcelファイルを、Accessのテーブルにインポートしようとしたとします。
「年齢」の列は、ぱっと見、数字が入っていますよね。でも、もし、どこかのセルに空欄があったり、全角数字が混じっていたりすると、Accessは「あれ？これは数値型でいいのかな？それともテキスト？」と迷ってしまいます。
特に、Excel側では「数値」として扱われているデータでも、空欄や特定の形式のデータが混在していると、Accessがインポート時に「これは数値型じゃない！」と判断し、エラーを発生させてしまうのです。これが、よく聞く「フィールドの型が一致しません」エラーの正体です。
実際によくあるパターン
- 数値型フィールドに空欄がある: Excelでは問題なくとも、Accessで必須項目になっている数値型フィールドに空欄があるとエラーになります。
- 数値型フィールドに日付やテキストが混在: 意図せず、数値としてインポートしたい列に日付形式の文字列や、単なるテキストが入っていた場合。
- 数値型フィールドに通貨記号やカンマが含まれる: 「￥1,000」のような形式だと、Accessは数値として認識できないことがあります。
従来の方法と、その限界
「じゃあ、どうすればいいんだ？」となりますよね。
従来は、以下のような方法で対応することが多かったかと思います。
1. Excel側でデータの整形を徹底する: インポート前に、Excel側で空欄を埋めたり、データ型を統一したりしてからインポートする。
- 限界: 手作業が多く、ミスも発生しやすい。自動化の恩恵を受けにくい。
2. Accessテーブルのフィールド型を「短縮形テキスト」にする: とりあえずインポートしてしまってから、VBAでデータ型を変換する。
- 限界: テーブル設計の意図が損なわれる。後続の処理で型変換の手間が増える。
これらの方法は、小規模なデータや、一度きりのインポートであれば有効かもしれません。しかし、定期的かつ大量のデータを扱う場合、これらの方法は非効率的で、エラーの温床になりがちです。
救世主！「インポート定義」の力
そこで、私たちが今回注目するのが、「インポート定義」という機能です。
「インポート定義」とは、Excelなどの外部データをAccessにインポートする際に、「どの列を、どのようなデータ型で、どのようにインポートするか」という詳細なルールをあらかじめ定義しておく仕組みのこと。
このインポート定義をうまく活用することで、`DoCmd.TransferSpreadsheet` を使う際にも、データ型に関する問題を劇的に軽減できるんです。
インポート定義の作成方法（手動）
まず、インポート定義をどうやって作るか見てみましょう。Accessの画面から、GUI操作で作成できます。
1. Accessのナビゲーションウィンドウで、インポート先のテーブルを選択（または新規作成）します。
2. リボンメニューの「外部データ」タブをクリックします。
3. 「インポート」グループにある「新しいデータソース」から「ファイルから」→「Excel」を選択します。
4. インポートしたいExcelファイルを選択し、「指定した場所にデータを格納します。」を選び、「OK」をクリックします。
5. 「テーブルのインポート」ウィザードが表示されます。ここで、インポート元のシートや範囲を選択します。
6. ここが重要！ 「最初の行を列名として使用する」などの設定を進めると、「フィールドのインポート」画面が表示されます。
- ここで、各列ごとに「フィールド名」や「データ型」を設定できます。
- さらに、「詳細設定」ボタンをクリックすると、「インポート/エクスポート仕様」として保存するオプションが出てきます。
この「インポート/エクスポート仕様」として保存したものが、いわゆる「インポート定義」です。
この定義ファイル（拡張子は `.accdb` や `.mdb` と同じ）をAccessが参照することで、指定したルールに従ってインポートが行われます。
VBAからインポート定義を呼び出す
手動で作成したインポート定義ですが、VBAから `DoCmd.TransferSpreadsheet` メソッドを使って呼び出すことができます。
その際に指定するのが、`SpecificationName` 引数です。
‘ 例：’MyImportSpec’ という名前で保存されたインポート定義を使用する場合
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetXLS, _
TableName:=”インポート先テーブル名”, _
FileName:=”C:\path\to\your\excel_file.xls”, _
HasFieldNames:=True, _
SpecificationName:=”MyImportSpec”
この `SpecificationName` に、保存したインポート定義の名前を指定するだけで、Accessは保存されたルールに従ってインポートを実行してくれるのです。
これにより、「年齢」フィールドは数値型としてインポートされるべき、といったルールが自動的に適用され、型不一致エラーを防ぐことができます。
罠を回避！VBAで動的にインポート定義を切り替える
さらに、このインポート定義の活用法を深掘りしてみましょう。
「毎回同じインポート定義でいいのか？」というと、そうとも限りません。
例えば、
- インポートするExcelファイルによって、列の順番が違う場合。
- ある日は「備考」列をインポートしたいが、別の日は不要な場合。
- インポートするデータの内容によって、インポート定義を切り替えたい場合。
このようなケースでは、固定のインポート定義だけでは対応が難しいことがあります。
そこで、VBAを使って、インポート定義を動的に切り替えるという高度なテクニックをご紹介します。
1. インポート定義を複数作成し、VBAで条件分岐させる
最もシンプルで分かりやすいのは、いくつかのインポート定義を事前に作成しておき、VBAのコードの中で条件に応じて適切な定義名を指定する方法です。
例えば、以下のようなシナリオを考えてみましょう。
- シナリオA: 通常のインポート（全列インポート）
- シナリオB: 特定の列（例えば「備考」列）を除外したインポート
この場合、それぞれ「通常インポート定義」「備考除外インポート定義」といった名前でインポート定義を作成しておきます。
そして、VBAコードで、どのようなインポートを行いたいかに応じて、`SpecificationName` に指定する名前を切り替えます。
Sub ImportExcelWithDynamicSpec()
Dim strExcelFilePath As String
Dim strTableName As String
Dim strSpecName As String
Dim blnExcludeRemarks As Boolean
‘ — 設定値 —
strExcelFilePath = “C:\Data\SampleData.xlsx” ‘ Excelファイルのパス
strTableName = “tblImportData” ‘ インポート先テーブル名
blnExcludeRemarks = True ‘ Trueなら備考列を除外、Falseなら通常インポート
‘ — インポート定義名の決定 —
If blnExcludeRemarks Then
strSpecName = “備考除外インポート仕様” ‘ 事前に作成したインポート定義名
Else
strSpecName = “通常インポート仕様” ‘ 事前に作成したインポート定義名
End If
‘ — DoCmd.TransferSpreadsheet を実行 —
On Error GoTo ErrorHandler ‘ エラーハンドリング
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetXLSX, _
TableName:=strTableName, _
FileName:=strExcelFilePath, _
HasFieldNames:=True, _
SpecificationName:=strSpecName
MsgBox “Excelファイルのインポートが完了しました！”, vbInformation
Exit Sub
ErrorHandler:
MsgBox “インポート中にエラーが発生しました。” & vbCrLf & _
“エラー番号: ” & Err.Number & vbCrLf & _
“エラー内容: ” & Err.Description, vbCritical
End Sub
このコードでは、`blnExcludeRemarks` というフラグ変数でインポートの条件を制御し、それによって `strSpecName` に指定するインポート定義名を切り替えています。
このように、VBAで条件分岐を設けることで、柔軟なインポート処理を実現できます。
2. VBAでインポート定義を動的に（プログラムで）作成・更新する
さらに一歩進んで、「毎回インポート定義をGUIで作成・更新するのは面倒だ！」という方のために、VBAから直接インポート定義を作成・更新する方法もあります。
これは、`Access.ImportExportSpecifications` コレクションと、`Access.ImportExportSpecification` オブジェクトを使用します。
（注意） この方法は、VBAの知識がさらに要求されます。また、インポート定義の構造を理解する必要があるため、最初は少し難しく感じるかもしれません。しかし、一度マスターすれば、インポート処理の自動化を劇的に進化させることができます。
ここでは、概念的な説明に留めますが、以下のような流れで処理を行います。
1. `Application.ImportExportSpecifications` コレクションにアクセスします。
2. 新しいインポート定義を作成するための `ImportExportSpecification` オブジェクトを作成します。
3. そのオブジェクトのプロパティ（`Name`、`FileName`、`FileFormat` など）を設定します。
4. さらに、インポートする各フィールドの詳細（フィールド名、データ型、インポートするかどうかなど）を設定します。これは、`ImportExportSpecification` オブジェクトの `FieldMappings` プロパティなどを介して行います。
5. 設定した `ImportExportSpecification` オブジェクトをコレクションに追加します。
6. その後、`DoCmd.TransferSpreadsheet` メソッドで、今回プログラムで作成したインポート定義名を `SpecificationName` に指定して実行します。
‘ — VBAでインポート定義を作成・更新するイメージ —
Sub CreateOrUpdateImportSpec()
Dim iimportSpec As Access.ImportExportSpecification
Dim strSpecName As String
strSpecName = “動的作成インポート仕様”
On Error Resume Next ‘ Speceficationが存在しない場合を考慮
‘ 既存のインポート定義を削除（更新する場合）
Application.ImportExportSpecifications.Delete strSpecName
On Error GoTo 0
‘ 新しいインポート定義を作成
Set iimportSpec = Application.ImportExportSpecifications.Add(strSpecName)
‘ インポート定義のプロパティを設定
iimportSpec.Name = strSpecName
‘ iimportSpec.FileName = “C:\Data\Sample.xlsx” ‘ ファイルパスは通常TransferSpreadsheetで指定
iimportSpec.FileFormat = acSpreadsheetXLSX ‘ ファイル形式
‘ … その他のプロパティ設定 …
‘ フィールドマッピングの設定（ここが一番複雑）
‘ 例：
‘ Dim fm As Access.FieldMapping
‘ Set fm = iimportSpec.FieldMappings.Add(“列1”, “フィールド1”, acLong) ‘ フィールド名、データ型などを指定
‘ fm.ImportThisField = True
‘ — ここに各フィールドの設定を記述 —
‘ 例：’氏名’フィールドをテキスト型でインポート
Dim fmName As Access.FieldMapping
Set fmName = iimportSpec.FieldMappings.Add(“氏名”, “氏名”, acText)
fmName.ImportThisField = True
‘ 例：’年齢’フィールドを数値型でインポート
Dim fmAge As Access.FieldMapping
Set fmAge = iimportSpec.FieldMappings.Add(“年齢”, “年齢”, acLong) ‘ acLongは32ビット整数
fmAge.ImportThisField = True
‘ 例：’登録日’フィールドを日付型でインポート
Dim fmDate As Access.FieldMapping
Set fmDate = iimportSpec.FieldMappings.Add(“登録日”, “登録日”, acDate)
fmDate.ImportThisField = True
‘ 設定を保存
iimportSpec.Save
MsgBox “インポート定義 ‘” & strSpecName & “‘ を作成しました。”, vbInformation
Set iimportSpec = Nothing
End Sub
‘ 上記で作成したインポート定義を使ってインポートする例
Sub ImportUsingDynamicSpec()
Dim strExcelFilePath As String
Dim strTableName As String
Dim strSpecName As String
strExcelFilePath = “C:\Data\SampleData.xlsx”
strTableName = “tblImportData”
strSpecName = “動的作成インポート仕様” ‘ 上で作成した定義名
On Error GoTo ErrorHandler
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetXLSX, _
TableName:=strTableName, _
FileName:=strExcelFilePath, _
HasFieldNames:=True, _
SpecificationName:=strSpecName
MsgBox “動的インポート定義を使用したインポートが完了しました！”, vbInformation
Exit Sub
ErrorHandler:
MsgBox “インポート中にエラーが発生しました。” & vbCrLf & _
“エラー番号: ” & Err.Number & vbCrLf & _
“エラー内容: ” & Err.Description, vbCritical
End Sub
このように、VBAからインポート定義を操作することで、Excelファイルの構造が多少変わっても、あるいはインポートしたい項目が変わっても、VBAコードを修正するだけで柔軟に対応できるようになります。これは、まさに「Access VBAを掌握する極限の知見」と言えるでしょう！
まとめ：インポート定義で、エラー知らずの安定したインポート処理を！
今日は、Access VBAにおける `DoCmd.TransferSpreadsheet` の「型不一致エラー」という落とし穴を回避するための強力な武器、「インポート定義」について、その作成方法からVBAでの活用法までをじっくり解説しました。
- Excelインポート時の「型不一致エラー」は、データ型の認識の違いや、空欄、混在データが原因で発生しやすい。
- 「インポート定義」を作成することで、インポート時のデータ型や処理ルールを細かく指定できる。
- VBAから `SpecificationName` 引数でインポート定義を指定することで、`DoCmd.TransferSpreadsheet` のエラーを回避できる。
- さらに、VBAで条件分岐させたり、インポート定義自体を動的に作成・更新したりすることで、より柔軟で堅牢なインポート処理が実現できる。
ここをクリアすれば、Access VBAを使ったデータ連携処理の基本はバッチリですよ！
ぜひ、皆さんのAccess VBA開発に取り入れてみてください。きっと、日々の業務がよりスムーズで、エラー知らずになるはずです。
次回も、皆さんのAccess VBAスキルアップに役立つ、実践的なテクニックをお届けしますね！お楽しみに！
