Access Tips #533 Excelインポート時の型変換エラーを回避する方法

Dim dbs As Database
Dim tdf As TableDef
Dim rst As Recordset
Dim strSQL As String
Dim strTblName As String
Dim strKey As String
Dim astrFldName() As String
Dim iintLoop As Integer

'インポート先テーブル名を設定
strTblName = "wtblインポート"
'主キーフィールド名を指定
strKey = "社員ID"

'とりあえず作業テーブル等にいったんインポート
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
              strTblName, "Excelファイルのパス", False

Set dbs = CurrentDb

'見出し行だけを取り出すSQL文を組み立て
strSQL = "SELECT * FROM " & strTblName & " WHERE F1 = '" & strKey & "'"
Set rst = dbs.OpenRecordset(strSQL)
With rst
  '１レコード目のデータをフィールド名として配列に保存
  ReDim astrFldName(.Fields.Count - 1)
  For iintLoop = 0 To .Fields.Count - 1
    astrFldName(iintLoop) = .Fields(iintLoop).Value
  Next iintLoop
  'そのレコードを削除
  .Delete
  .Close
End With

'テーブルの定義を変更
Set tdf = dbs.TableDefs(strTblName)
With tdf
  For iintLoop = 0 To .Fields.Count - 1
    .Fields(iintLoop).Name = astrFldName(iintLoop)
  Next iintLoop
End With


#533	Excelインポート時の型変換エラーを回避する方法	VBA
ExcelのワークシートをAccessのテーブルとしてインポートする際は、ワークシートの先頭から8行分に保存されているデータが事前にチェックされ、各列のAccessテーブル上でのフィールドのデータ型が決定されます。そのため、数値や文字の両方を含んでいる列（Access上では「テキスト型」にすべきフィールド）であっても、ワークシートの前の方に数値データしか格納されていないと、Access上では「数値型」フィールドと定義され、文字データを含む行をインポートする際にデータ型変換のエラーが発生してしまいテーブルには取り込まれません。 CSVファイルのインポートでは各例をどのようなデータ型として取り込むかをユーザーが設定できますが、Excelのファイル（.xls等）の場合はそのような設定ができないため、何らかの工夫をする必要があります。たとえば、Excel側で事前に先頭8行にあえてデータ型が混在するようにしておくなどの手段があります。しかし、一回限りのインポート操作ならそれが一番簡単なのですが、システム運用でしばしばインポート処理する場合、たとえば何らかの業務実績データを毎日毎日Excelから取り込むような処理をフォームのコマンドボタンに割り付けてあるような場合、毎回Excelのワークシートを確認したり編集したりするのは困難です。そこで、そのような問題を回避するひとつの方法として、VBAを使って次のような考え方で処理を行ってみます。インポートする際は、ワークシート１行目の見出し行をデータとして扱いインポートするふつう見出し行は漢字等の表記が多いはずなので、それによってすべての列が「テキスト型」とみなされます。もしそうでなかった場合にはあらかじめ文字列表記に変えておきますが、１行目は固定化されているはずなので、業務上の不都合は少ないはずです。次に、インポート完了後、見出し行として取り込んだレコード内容をチェックし、各フィールドに保存された内容すなわち「列見出し」でテーブルのフィールド名を構造変更する１行目をデータ行としてインポートした場合、Accessのテーブルのフィールド名は「F1、F2 、F3 ・・・」のようになっています。それを「F1→社員ID」のように変更していきます。なおこのとき、見出し行は１レコード目としてインポートされていそうですが、必ずしもそうなりません。よって見出し行のレコードを見つける際はその点を配慮してプログラミングします。最後に、見出し行のレコードを削除するこれをVBAで実際に表現すると次のようなコードになります。 Dim dbs As Database Dim tdf As TableDef Dim rst As Recordset Dim strSQL As String Dim strTblName As String Dim strKey As String Dim astrFldName() As String Dim iintLoop As Integer 'インポート先テーブル名を設定 strTblName = "wtblインポート" '主キーフィールド名を指定 strKey = "社員ID" 'とりあえず作業テーブル等にいったんインポート DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _ strTblName, "Excelファイルのパス", False Set dbs = CurrentDb '見出し行だけを取り出すSQL文を組み立て strSQL = "SELECT * FROM " & strTblName & " WHERE F1 = '" & strKey & "'" Set rst = dbs.OpenRecordset(strSQL) With rst '１レコード目のデータをフィールド名として配列に保存 ReDim astrFldName(.Fields.Count - 1) For iintLoop = 0 To .Fields.Count - 1 astrFldName(iintLoop) = .Fields(iintLoop).Value Next iintLoop 'そのレコードを削除 .Delete .Close End With 'テーブルの定義を変更 Set tdf = dbs.TableDefs(strTblName) With tdf For iintLoop = 0 To .Fields.Count - 1 .Fields(iintLoop).Name = astrFldName(iintLoop) Next iintLoop End With なおここではインポート先テーブル名と主キーフィールド名を固定としてプログラミングしていますが、ケースによって異なると思いますので、その都度書き換えるか、上記全体をプロシージャ化してそれら２つを引数に設定してもよいかもしれません。
\| Index \| Prev \| Next \|