こんにちは。現場の最前線でAccessと格闘している皆さん。
「ExcelのデータをAccessに取り込むとき、なぜかエラーが出る」「数値なのにテキストと判定されてデータが消える」。そんな経験、一度はありますよね。
今日は、Access VBAにおける「Excel取り込み」の核心に迫ります。`DoCmd.TransferSpreadsheet`は一見便利な魔法ですが、その裏側にある「型推論という名の罠」を知らなければ、いずれ必ずシステムは破綻します。
中級者への登竜門として、この「型不一致問題」を完全に制圧する方法を伝授しましょう。
—
1. なぜ「魔法のコマンド」が裏切るのか？
`DoCmd.TransferSpreadsheet` を使うと、AccessはExcelの先頭数行をスキャンして「これは数値列だ」「これは日付だ」と勝手に判断します。これを「型推論」と呼びます。
しかし、この推論は非常に脆い。
- 空行やゴミデータ: 最初の数行が空だと、Accessは「この列はテキストだ」と決めつけ、後から出てくる数値を切り捨てることがあります。
- 混在データ: 1000行目に突然「N/A」のような文字列が入っていると、それ以降の数値がインポートされません。
「マクロの記録」から脱却したエンジニアなら、ここで「Accessの判断に任せてはいけない」と気づくはずです。
—
2. 解決策：インポート定義（Spec）を使いこなす
この罠を回避する最も堅牢な手段は、「インポート定義」を使うことです。
GUI（ウィザード）で一度設定を保存し、VBAからそれを呼び出すことで、Accessに「推論」させず「命令」できるようになります。
ステップ1：インポート定義を作成する
1. [外部データ]タブ -> [新しいデータソース] -> [ファイルから] -> [Excel]を選択。
2. ファイルを選択し「インポート」を選択。
3. ウィザードを進め、フィールドのオプションで各列のデータ型を明示的に指定します。
4. 最後に「詳細設定」ボタンを押し、「名前を付けて保存」で定義名（例：`MyImportSpec`）を付けます。
ステップ2：定義を呼び出すVBAコード
定義さえ作れば、コードは驚くほどシンプルで強固になります。
Public Sub ImportExcelData()
On Error GoTo ErrorHandler
‘ 定義名（SpecName）を指定することで、型推論の罠を回避する
‘ acImport: インポート, acSpreadsheetTypeExcel12Xml: .xlsx形式
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=”T_ImportTable”, _
FileName:=”C:\Data\Source.xlsx”, _
HasFieldNames:=True, _
Range:=”Sheet1!”, _
StoreLogin:=False ‘ 実際にはインポート定義の指定はここにはないが、
‘ 定義済みの場合は「インポート定義」機能を使うのが正攻法
MsgBox “取り込み完了！”, vbInformation
Exit Sub
ErrorHandler:
MsgBox “エラー発生: ” & Err.Description, vbCritical
End Sub
補足：VBAからインポート定義を直接指定するには `DoCmd.RunSavedImportExport` を使用するのが現代のAccess開発の定石です。
—
3. 次の一手：事前バリデーションという「防波堤」
定義を使っても、Excelファイル自体のフォーマットが崩れていたら意味がありません。
インポートする前に、「最低限ここだけはチェックする」という関数を挟みましょう。
‘ 簡単なバリデーションの考え方
Public Function IsValidExcel(filePath As String) As Boolean
Dim xlApp As Object
Set xlApp = CreateObject(“Excel.Application”)
Dim wb As Object
Set wb = xlApp.Workbooks.Open(filePath)
‘ 1行目のヘッダーが正しいかチェックするなどのロジック
If wb.Sheets(1).Cells(1, 1).Value <> “ID” Then
IsValidExcel = False
Else
IsValidExcel = True
End If
wb.Close False
xlApp.Quit
Set wb = Nothing
Set xlApp = Nothing
End Function
—
4. 最後に：エンジニアの心得
Access VBAで最も大切なのは、「コンピュータを信用しないこと」です。
1. インポート定義で型を固定する（推論という名の甘えを捨てる）
2. 取り込み前にファイルを検証する（泥水をシステムに入れない）
3. エラーハンドリングを必ず書く（想定外を想定内にする）
この3つを守るだけで、あなたの書くコードは「動くだけのスクリプト」から「堅牢なシステム」へと進化します。
「ここをクリアすれば、Access VBAの基本はバッチリ」です。
さあ、次に構築するシステムでは、ぜひこの「定義による管理」を導入してみてください。あなたのAccess開発ライフが、より安定したものになることを確信しています。
何か壁にぶつかったら、またいつでも相談してくださいね。応援しています。
