Access VBAでインポートした際にデータが全て取込まれない

Question

TransferSpreadsheetのacExportで出力したExcelファイルに１行追加し、
acImportでインポートしたときに追加した行がインポートされません。
例えば、１０行のデータを出力しヘッダー込で１１行がExcelに出力され、
１２行目に新しくデータを追加してもインポート時は１０行しか取込まれていません。
インポート時は

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

で実行しており、特に何もおかしなことはしていないのですが、ピンと思い当たる方いませんでしょうか。。

Answer 1

第６引数ですがExcelの範囲名を指定していませんか？シート名は最後に「!」をつける必要があったと思います。

Comments

[syncrock]

回答ありがとうございます。
ご指摘通り、シート名の最後に「!」をつけることで正常に取り込まれました。
特に範囲指定しておらずシート名だけの記載だったのですが、!をつけないといけなかったのですね。。。

Answer 2

第6引数で「シート名」を指定していますが、この引数は厳密には「シート名」を指定するのではなく「範囲(Range)」を指定する引数です。

最初にAccessからExportした際、Excelに出力したテーブル名のシートが作成され、そこにデータが出力されます。
それに加え、そのデータ範囲がExcelの「名前」にも追加されています。

例えば"DATA_01"テーブルから5列10行のデータを出力した場合、
・「DATA_01」シートにヘッダ込み11行のデータが出力される。
・「DATA_01!$A$1:$E$11」のデータ範囲が「DATA_01」という「名前」で追加される。
⇒「数式」タブの「名前の管理」で確認できます。

このシートからデータを取得する際に「DATA_01」という範囲を指定すると、シート名としての「DATA_01」よりも先に「名前」の「DATA_01」が優先して参照されるため、範囲が絞られたインポートになってしまいます。

回避策

回避策は
・「名前」としての「DATA_01」を削除する、または範囲拡張する
・Importの引数をシート名として判断させる
といったところでしょうか。

今回の場合はAccessVBAでの制御だと思いますので、前者のようにExcel側のデータをゴリゴリやるのは面倒かもしれません。

後者であれば、sousukeさんのアドバイスにある通り
DoCmd.TransferSpreadsheet acImport, 10, "Access側テーブル名", "Excelファイル名", True, "シート名!"
のような形式で明示的に指定できます。

参考になれば幸いです。

Comments

[jawa]

入れ違いですでに解決済みされていたようですね。よかったです。

[syncrock]

回答ありがとうございます。
仰るとおりでした、名前の管理でシート名と同名の定義が作成されており、その範囲をみたものと思います。
範囲を指定することは認識しておりましたが、シート名を指定した場合はそのシートを対象に見る。と思ってました。
また、名前が定義されていることは完全に認識外でした。
とても勉強になりました、ありがとうございます。

Answer 3

TransferSpreadsheetのacExportで出力したExcelファイルに１行追加し、

「この１行追加し」というのはユーザーがExcelファイルを開いて1行入力したということですか。それとも、VBAで追加したということでしょうか。
VBAで追加したのなら、そのコードも提示してください。

また、提示のコードは第2引数を数値で指定してますが、ここは名前付き定数で指定するようにしましょう。提示する場合はできる限り実際にものに近い形で提示してください。質問用に編集すると実際のコードに間違いがあっても指摘できません。

例えば、下記のように。

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
"t_個人情報", "c:\test\t_個人情報.xlsx", True, "Sheet1!"

ちなみに、シート名の最後の ! を省略しても問題なくインポートできるようです。
当方の実験ではできましたが、できないこともあるようですね。
実験コードを見直したら、!を付けてました。

Comments

[syncrock]

回答ありがとうございます。

>「この１行追加し」というのはユーザーがExcelファイルを開いて1行入力したということですか。それとも、VBAで追加したということでしょうか。
→「ユーザーが出力したファイルを開いて、１行入力した。」ということです。

>また、提示のコードは第2引数を数値で指定してますが、ここは名前付き定数で指定するようにしましょう。
→こちらについてはコードそのままであり、数値で指定しておりました。
　ただし、「シート名」とかは変数だったりしますのでマスクするなど実際のコードになるように以後気を付けます、ご指摘ありがとうございます。

>当方の実験ではできましたが、できないこともあるようですね。
→インポート自体は出来ますが追加した行が「!」が無いときは取込めず、「!」がある時は取込めており、問題は解決しましたが結局何がいけないのかがはっきりしませんね。。
本当に何も特別なことはしてない（つもり）なのですが。。続きを読む ∨