Ａ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 · ベストアンサー

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

とりあえずマクロで正常に動くかをやってみれば、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飛ばしをした方が安全かも知れません。

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

回答No.5

gatt_mk

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

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

補足

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

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

少し悔しいです。

回答No.4

gatt_mk

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

であれば、

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, "!給与データ報告書"

回答No.3

gatt_mk

"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!給与データ報告書$"
試しましたが駄目でした。

回答No.2

gatt_mk

range指定のところでシート名を指定したらインポートできませんか？
range指定のほか、シート名や範囲名の指定でもインポートできるはずですが。

補足

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

回答No.1

suzukikun

DoCmd.TransferSpreadsheet acImport, , "給与データ報告書", "c:\test\test.xls", True, ""
で、最後の""がいらない思います。Trueまでで、カンマ無しで終わらせるとどうですか？

補足

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