ＡccessからＶＢＡでExcellシートのインポート

すみません。教えてください。
AccessVBAでエクセルのシートをインポートしたいのです。
いつも同じ行数ではありませんのでrange指定を埋め込むわけに行きません。
DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", "c:\test\test.xls", True, "a1:p6000"
でインポートできるのですが
DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", "c:\test\test.xls", True, ""
range指定をしないとエラーになります。（出来ると書いてあるが）
sheet1、ワークシート名とかの指定が出来ればと思い、いろいろ試してみたのですが出来ません。
宜しくお願いいたします。

No.6 ベストアンサー 回答者： gatt_mk 回答日時： 2007/02/15 21:05

うまくいかない場合は原因を追及することをお勧めします。

とりあえずマクロで正常に動くかをやってみれば、VBAの構文のミスなのか、それ以外に原因があるのかわかるかと思います。

マクロのアクションに「ワークシート変換」を選択し、下記内容の設定をしてみてください。

変換の種類：　　　　　　インポート
ワークシートの種類：　　Microsoft Excel 8-9
テーブル名： 　　　給与データ報告書
ファイル名：　　　　　　c:\user\test.xls
範囲名：　　　　　　　　給与データ報告書!

このマクロが正常に動くかどうかを確認して、正常に動けば「マクロをVisualBasicに変換」を行ってVBAスクリプトを確認することができると思います。
マクロが正常に動かなければ、シート名を英数だけの名称にするなど簡単なものしてどうなるかを確認してみてください。

少なくともこちらでは同様の内容でインポートできていますので、MDB自身に何か問題があるのかもしれません。その場合一度最適化をすると正常に動くようになるかもしれません。

この回答への補足

>うまくいかない場合は原因を追及することをお勧めします。
ありがとうございます。この言葉に力を得て続けました。

/*このマクロが正常に動くかどうかを確認して、正常に動けば「マクロをVisualBasicに変換」を行ってVBAスクリプトを確認することができると思います。
マクロが正常に動かなければ、シート名を英数だけの名称にするなど簡単なものしてどうなるかを確認してみてください。*/
ようやく解決いたしました。
VBAで9個のXLSファイルを読み込み順次インポートを繰り返すDo--Loopを作成しました。
最初に読み込むエクセルと、他に２つのエクセルファイルが問題でした。
エクセルで見るとみんな同じでPの後にはなにも入っていないように見えます。しかし実際は何か入っていてAccessの項目数より大きかったのです。マクロのアクションに「ワークシート変換」をしたらフィールド１７～フィールド２３（あるものはフィールド２０まで）まで後ろについていました。

これらの項目をエクセルから削除し（正確には新しいワークシートを作成し、そこにコピペしました。）実行したところ
DoCmd.TransferSpreadsheet acImport, 8, "給与データ報告書", Infile, True, ""
が動きました。
ありがとうございました。
見た目と違うエクセルシートでしたので混乱いたしました。
本当にありがとうございました。

ただ、シートを渡しても加工されてかえされるとどうしようもないです。
DoCmd.TransferSpreadsheet acImport, 8, "給与データ報告書", Infile, True, "給与データ報告書!a1:p6000"
でNull飛ばしをした方が安全かも知れません。

長い間お手数をかけました。
ありがとうございました。

補足日時：2007/02/21 16:33

この回答へのお礼

本当にありがとうございます。
今後とも宜しくお願いいたします。

お礼日時：2007/02/21 17:21

No.5 回答者： gatt_mk 回答日時： 2007/02/14 19:27

DoCmd.TransferSpreadsheet acImport, 8 , "給与データ報告書", Infile, True, "給与データ報告書!"

と指定してみてください。

この回答への補足

本当にありがとうございます。
DoCmd.TransferSpreadsheet acImport, 8 , "給与データ報告書", Infile, True, "給与データ報告書!"
駄目でした。

暫定処置として
DoCmd.TransferSpreadsheet acImport, 8 , "給与データ報告書", Infile, True, "給与データ報告書!a1:p6000"
3000データ以上はないと思うので安全を見て6000行までとしインポートをかけました。
インポート先のテーブルに一意のキーを設定しヌル行を読み飛ばすようにしました。

少し悔しいです。

補足日時：2007/02/15 08:45

No.4 回答者： gatt_mk 回答日時： 2007/02/14 15:25

>ワークシート名は”給与データ報告書”です。

であれば、

DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", Infile, True, "給与データ報告書"

でインポートできると思いますが。

試しにご自身で複数ワークシートのあるブックを、インポートウィザードでインポートしてみてください。必ずシート名を選択する画面が出てくると思います。
ACCESSではシート名を指定してインポートできます。

この回答への補足

ご丁寧に教えていただきありがとうございます。
＞複数ワークシートのあるブックを、インポートウィザードでインポー＞トしてみてください。必ずシート名を選択する画面が出てくると思い＞ます。

これは承知していますのですが・・・。

DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", Infile, True, "給与データ報告書!a1:p6010"
上記記述　成功

以下の記述はすべてエラーになります。
DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", Infile, True, "給与データ報告書"
DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", Infile, True, "給与データ報告書!"
DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", Infile, True, "給与データ報告書$"
DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", Infile, True, "!給与データ報告書$"
DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", Infile, True, "!給与データ報告書"

補足日時：2007/02/14 16:46

No.3 回答者： gatt_mk 回答日時： 2007/02/13 21:02

"Sheet1!a1:p10"ではなく、"Sheet1"と書いたらどうなりますか？

普通にインポートできると思いますが。

この回答への補足

お手数をかけます。
DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", Infile, True, "給与データ報告書!a1:p10"
DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", Infile, True, "a1:p10"
ワークシート名は”給与データ報告書”です。
ちなみにワークシート名そのままで”Sheet1"、”Sheet1$”をrengeの箇所に書いても駄目でした。
Infileのところに"c:\test\test.xls"が入りますがここでワークシート名を指定するのでしょうか？
だとすると書き方がわかりません。
"c:\test\test.xls.給与データ報告書"
"c:\test\test.xls.給与データ報告書$"
"c:\test\test.xls!給与データ報告書"
"c:\test\test.xls!給与データ報告書$"
試しましたが駄目でした。

補足日時：2007/02/14 08:54

No.2 回答者： gatt_mk 回答日時： 2007/02/13 17:43

range指定のところでシート名を指定したらインポートできませんか？

range指定のほか、シート名や範囲名の指定でもインポートできるはずですが。

この回答への補足

早速の対応ありがとうございます。
rangeのところに"Sheet1!a1:p10"と書くとエラーになりません。
p10のところが可変なのでWorksheet名でインポートしたいのですが・・・
Sheet1$とかやってみたのですが駄目でした。

補足日時：2007/02/13 18:55

No.1 回答者： suzukikun 回答日時： 2007/02/13 17:28

DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", "c:\test\test.xls", True, ""

で、最後の""がいらない思います。Trueまでで、カンマ無しで終わらせるとどうですか？

この回答への補足

早速の対応ありがとうございます。
駄目でした・・・。（ToT)

補足日時：2007/02/13 19:02