アクセスに項目行なし・1行目からデータのエクセルをインポートしようとするとき、「インポート先のテーブルにF1フィールドがありません」というようなエラーメッセージがでてインポートできない。。。ということに長らく悩んでおりました。
DoCmd.TransferSpreadsheet acImport, , “テーブル1”, CurrentProject.Paht & “\test.xlsx”, False
DoCmdでインポートしてみようとする。
エクセルをインポートするには
DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range)
TransferType は acImport を指定
SpreadsheetType は Excelの種類を指定するが、省略
TableName は インポートしたい先のテーブル
FileName は インポートしたいエクセルファイルのパスを指定
そしてここ！
HasFieldNames は 項目行ありの場合は TRUE 、1行目をデータとして扱う場合はFalseを指定とある。
以下マイクロソフトのヘルプの引用
インポートまたはリンクするときにフィールド名としてワークシートの最初の行を使用するには、True (1) を使用します。 ワークシートの最初の行を通常のデータとして扱うには、False (0) を使用します。 この引数を空白にすると、既定値 (False) と見なされます。 Access テーブルをエクスポートしたり、ワークシートへのクエリ データを選択したりすると、引数に入力する内容に関係なく、ワークシートの最初の行にフィールド名が挿入されます。
引用元：マイクロソフトのヘルプ
で、項目行なしだから False にすればいいんだよね！と False にするが、先述のエラー「F1フィールドがありません」のメッセージが出てインポートできない。
しかたがないので、インポート先のテーブルに合わせた項目行を入れておいたりして対応してました。テキストデータだといろいろ自由が利くのにエクセルのインポートはなんだか苦手だなあと思っています。
◆F1フィールドがありませんのナゾが解き明かされた
ネットで調べても「これ！」といった解決方法に当たることがなく、もんもんとしていたのですが、今日やっと、「F1フィールドのナゾ」はこれだったのか！という記事を見つけました。
以下、サイトより引用
エクセルをアクセスにインポートするには？？ – エクセル・ア… – Yahoo!知恵袋
Q：フィールド名の設定は、エクセルの１行目は項目でなくデータなので「いいえ」にしています。 フィールド名が違うというようにでてくるのですが、フィールド名の設定は「いいえ」にしています。
A：既存のテーブルにインポートしようとしていませんか？ フィールド名の設定が「いいえ」だと、自動的にF1、F2、F3 … というフィールド名を振り当てますので、インポートする先のテーブルのフィールド名がこれと異なると、「貼り付け先のテーブルには’F1’フィールドがありません。」のようなエラーメッセージが出ます。 逆に、F1、F2、F3 … というフィールド名があれば、データはそのフィールドに（A列のデータはF1に、B列のデータはF2に、というように）取り込まれます。F1、F2、F3 … という名前のフィールド以外にはデータは取り込まれません。
引用元：エクセルをアクセスにインポートするには？？ – エクセル・ア… – Yahoo!知恵袋
これを読んだところ、結局フィールド名なしだともともと用意してあるテーブルにインポートするのは無理じゃん！ということになりますね。項目名がF1,F2・・・でないかぎり・・・。
なんだよー。ということで、思いついた回避方法
その１：項目行なしエクセルデータを新規テーブルにインポートし、追加クエリで本来インポートしたいテーブルにデータを追加する
その２：エクセルを開いて配列にがばっと入れて、DAOでテーブルに追加していく
やってみます。
◆回避方法１：項目行なしエクセルデータを新規テーブルにインポートし、追加クエリで本来インポートしたいテーブルにデータを追加する
以下、コードです
Sub 項目行なしインポート()
Dim vPath As String
vPath = CurrentProject.Path
‘①testファイルをインポート。T新規は存在しないので、新規テーブルが作成される
DoCmd.TransferSpreadsheet acImport, , “T新規”, vPath & “\test.xlsx”, False
‘②DAO利用
Dim db As DAO.Database
Set db = CurrentDb
‘③追加クエリのSQL作成
Dim vSql As String
vSql = “INSERT INTO テーブル1 ( ID, 取引先名 ) SELECT T.F1, T.F2 FROM T新規 AS T;”
‘④追加クエリ実行（T新規→テーブル1）
db.Execute vSql, dbFailOnError
‘⑤T新規テーブルの削除
db.TableDefs.Delete “T新規”
‘⑥dbをClose
db.Close
End Sub
①で項目行なしのエクセルファイル「test」をインポートします。インポート先として指定している「T新規」テーブルは存在しないので、新規テーブルを作成し、そこにインポートすることとなります。
①の行を実行すると「T新規」テーブルが現れて、中を見てみるとエクセルデータがインポートされており、項目名は「F1」「F2」となっています！
以降でDAOを利用して追加クエリを実行するつもりなので、②でDAO利用の決まり文句を入れておきます。
③で追加クエリのSQL文を変数vSqlに入れておきます。「T新規」の内容を本当に入れたい先の「テーブル１」に追加するというSQL文です。
④で追加クエリを実行します。
⑤次回もこのコードを実行するときに「T新規」が新規テーブルになるように「T新規」テーブルを削除しておきます。
⑥DAOの最後の決まり文句データベースをクローズします。
うまくいきました。それにしてもエクセルのインポートは面倒ですね。
なお、項目行はあるがインポート先のテーブルと違ってインポートできない、という場合もこの回避方法は利用できます。
その場合、項目行を除いて2行目からインポートしますよ、と指定します。
①のコードを以下赤字の部分を変更します
DoCmd.TransferSpreadsheet acImport, , “T新規”, vPath & “\test.xlsx”, False, “Sheet1!A2:B”
◆回避方法２：エクセルを開いて配列にがばっと入れて、DAOでテーブルに追加していく
以下コードです。
Sub 項目行なしインポー2ト()
Dim vPath As String
vPath = CurrentProject.Path & “\test.xlsx”
‘①エクセルをオブジェクト型変数に入れてVBAで操作できるようにする
Dim myExcel As Object
Set myExcel = CreateObject(“Excel.Application”)
Dim arr As Variant
With myExcel
.Visible = False
.Workbooks.Open vPath ‘②エクセルファイルを開く
arr = .Range(“A1”).CurrentRegion.Value ‘③配列にデータを入れる
.Workbooks.Close
.Quit
End With
Set myExcel = Nothing
‘④DAOを利用
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
‘⑤データを入れたいテーブルをrsにセット
Set rs = db.OpenRecordset(“テーブル1”)
‘⑥配列の内容ををテーブルに追加
Dim i As Long
For i = LBound(arr) To UBound(arr)
rs.AddNew
rs!ID = arr(i, 1)
rs!取引先名 = arr(i, 2)
rs.Update
Next i
‘⑦DAO終わりの処理
rs.Close
db.Close
End Sub
①でエクセルを操作できるようにしています
②インポートしたいエクセルファイルを開きます
③開いたエクセルのデータを配列arrに入れています
④DAOを利用してインポート先テーブルにデータを入れていきますのでDAO最初の決まり文句を入れておきます
⑤データを入れたいテーブルをrsにセットします
⑥配列の内容を1行ずつテーブルに追加していきます。
⑦DAO終了の決まり文句を入れます。
おしまい
もっといい方法があると思いますが、私が思いついたのはこんな感じです。
そもそもテキストデータの方がインポートしやすいように思いますが、システムの都合などでどうしてもエクセルで吐き出されちゃうんだよ～というデータを処理したい場面がありましたので、考えてみました。
お読みいただきありがとうございました。
コメント