Access VBAにてTransferSpreadsheetを行った際の「#Num!」の回避について

Question

AccessVBA(2010)を使用しています。

DoCmd.TransferSpreadsheet acExport, 10, テーブル名, ファイスパス, True, シート名

で出力されたものを

DoCmd.TransferSpreadsheet acLink, 10, テーブル名, ファイルパス, False, シート名$範囲

の形で取り込んでいます。

その際に、テーブル上はテキスト型の項目で格納されている値は1か0の項目があります。
出力された際には表示形式は標準ですが、左寄せの文字列の形で出力されます。
ただし、表示形式が標準なため1か0を入力したり、フォーカスを一旦あてると右寄せになります。
右寄せになっているセルがその項目に1つでもあった場合で、
上記の形で取込を行うと、右寄せの値の部分は正常に取り込まれ、他の左寄せの値は全て「#Num!」になってしまいます。
もし、出力されたものを何も編集せずそのまま取り込むとうまくいきます。
これを回避することは出来ないでしょうか。
（編集しても問題なく取込めるように出来ないでしょうか）

Answer 1

DoCmd.TransferSpreadsheet acLinkなので、取込しているわけではなくリンクテーブルですね。

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

ですので、エクセルが編集されて状況が変化するリンクテーブルだと、エラーの回避は困難だと思われます。

リンクテーブルではなく、インポートであれば、インポート定義などが使用できますので、すべての項目を文字型で扱っておくようにすれば、エラーは回避できるでしょう。
Excel→Accessへのインポート時のフィールド別型指定

追記

DoCmd.OutputTo メソッドであれば書式も合わせて出力されます。

Comments

[syncrock]

回答ありがとうございます。
リンクテーブルなのですが、処理上リンクテーブルが作成された上でExcelが編集されることはありません。
（厳密にいうと処理としては、リンクテーブル作成→何かしらの処理→リンクテーブル削除が一連の流れです。）
リンクテーブルを作成した段階で「#Num!」が出ている状況です。

[sazi]

編集とはエクセルの操作を含みます。
自動保存になっていれば、保存されるわけですし。
一応エクセルを書式付きでエクスポートすれば文字は文字のままなので、エラー回避されると思われます。

[syncrock]

>編集とはエクセルの操作を含みます。
リンクテーブル作成後にリンク元となるExcelへの操作という意味でしょうか。
リンクテーブル作成後はリンクテーブルへのアクセスはありますが、Excelの操作は行われません。
リンクテーブル作成自体が編集、操作と言う意味で使われていれば認識違いです、すいません。
その場合は無理と言うことですね。。

ご指摘頂いたDoCmd.OutputTo メソッドで出力したExcelであっても同様で、
出力後何もせずにリンクテーブルを作れば問題ないですが、質問に記載させて頂いている動きをしたあとにリンクテーブルを作成すると「#Num!」が出ていました。

[sazi]

あとは、シートの保護とかロックとか

[sazi]

エクスポートしたデータに編集を行わないというなら、エクスポートした元データを参照する様に変更してはどうですか

[syncrock]

結果的に、エクスポート処理の中で、出力されたExcelファイルに対して該当列を文字列に書式設定することで正常に取り込めました。
ありがとうございます。