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

ExcelのワークシートをAccessのテーブルとしてインポートする際は、ワークシートの先頭から8行分に保存されているデータが事前にチェックされ、各列のAccessテーブル上でのフィールドのデータ型が決定されます。

そのため、数値や文字の両方を含んでいる列(Access上では「テキスト型」にすべきフィールド)であっても、ワークシートの前の方に数値データしか格納されていないと、Access上では「数値型」フィールドと定義され、文字データを含む行をインポートする際にデータ型変換のエラーが発生してしまいテーブルには取り込まれません。

CSVファイルのインポートでは各例をどのようなデータ型として取り込むかをユーザーが設定できますが、Excelのファイル(.xls等)の場合はそのような設定ができないため、何らかの工夫をする必要があります。

たとえば、Excel側で事前に先頭8行にあえてデータ型が混在するようにしておくなどの手段があります。

しかし、一回限りのインポート操作ならそれが一番簡単なのですが、システム運用でしばしばインポート処理する場合、たとえば何らかの業務実績データを毎日毎日Excelから取り込むような処理をフォームのコマンドボタンに割り付けてあるような場合、毎回Excelのワークシートを確認したり編集したりするのは困難です。


そこで、そのような問題を回避するひとつの方法として、VBAを使って次のような考え方で処理を行ってみます。
  1. インポートする際は、ワークシート1行目の見出し行をデータとして扱いインポートする

    ふつう見出し行は漢字等の表記が多いはずなので、それによってすべての列が「テキスト型」とみなされます。もしそうでなかった場合にはあらかじめ文字列表記に変えておきますが、1行目は固定化されているはずなので、業務上の不都合は少ないはずです。

  2. 次に、インポート完了後、見出し行として取り込んだレコード内容をチェックし、各フィールドに保存された内容すなわち「列見出し」でテーブルのフィールド名を構造変更する

    1行目をデータ行としてインポートした場合、Accessのテーブルのフィールド名は「F1、F2 、F3 ・・・」のようになっています。それを「F1→社員ID」のように変更していきます。
    なおこのとき、見出し行は1レコード目としてインポートされていそうですが、必ずしもそうなりません。よって見出し行のレコードを見つける際はその点を配慮してプログラミングします。

  3. 最後に、見出し行のレコードを削除する

これを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
  '1レコード目のデータをフィールド名として配列に保存
  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


なおここではインポート先テーブル名と主キーフィールド名を固定としてプログラミングしていますが、ケースによって異なると思いますので、その都度書き換えるか、上記全体をプロシージャ化してそれら2つを引数に設定してもよいかもしれません。
| Index | Prev | Next |

この情報は参考になりましたか?、問題は解決しましたか?、もしまだなら......
T'sWareのワンポイトテクニカルアドバイスをご利用ください。3000円/件〜でご支援します。
スタンドアロンからSQL Server対応まで、オーダーメイドのシステムを短納期・安価でお届けします
 

Copyright © T'sWare All rights reserved