Access2000にExcel2000のデータを読み込みたい。

質問

Access上のVBAでExcelよりデータを読み込もうと思います。
最終行を求めるに xlUp を使用しようと思いましたが、

#実行時エラー '1004'
#Rangeクラスの Select メソッドが失敗しました。

となります。

どこをがおかしいでしょうか？

　　Dim wb As Excel.Workbook
　　Dim ws As Excel.Worksheet

　　Set wb = GetObject(C:\abc.xls")　　'---オープン確認
　　Set ws = wb.Worksheets("Sheet1")

　　ws.Range("A65536").Select　　　'----実行時エラー
　　ws.Selection.End(xlUp).Select　　

また、abc.xlsにマクロが含まれているためにマクロを有効にするかどうかの確認メッセージがでます。これを有効、無効どちらでもいいですが、メッセージ無しに開くにはどうすればいいでしょうか？

回答No.2

mach999 · ベストアンサー

Selectメソッドは選択しようとしているセルを含むワークシートがアクティブではないと失敗するため、ほかのメソッドの実行に成功するような場合でもSelectメソッドはエラーになる可能性があります。実際、GetObject関数ではワークブックを非表示状態で開いているため、Selectメソッドが失敗しているのです。このため、Selectメソッドを実行する前に
wb.Application.Windows("abc.xls").Visible = True
としてワークブックを再表示しておく必要があります。

＞メッセージ無しに開くにはどうすればいいでしょうか？
　確認メッセージはGetObject関数を実行すると直ちに表示されるので、ActiveXオブジェクトを開いた後に確認メッセージを抑止するための処理を記述する余地はありません。また、GetObject関数はActiveXオブジェクトを開くための汎用的な関数であり、個別のActiveXオブジェクト用のオプションをつけることはできないため、ActiveXオブジェクトを開くときに確認メッセージを抑止する手段もありません。ですから、GetObject関数を使う限りは確認メッセージ無しにExcelワークブックを開くことは不可能だと思います。

　というわけで、素直にDoCmd.TransferSpreadsheetメソッドでテーブルにデータを読み込むのが無難です。
　　DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "テーブル名", "C:\abc.xls", False
（末尾のBooleanは、先頭をフィールド名として読み込むのであればTrueにして下さい）

回答No.4

imogasi

＃３です。
似た目的の
d = ws.Range("A1").currentregion.rows.Count
MsgBox d
は旨く行きました。
＃３も本件も、ACCESSのモジュールから実行してます。
定数XlUpがエクセル定数（ｘｌ）が何か関係あるのかな。
いまやって見ると
d = ws.range("a65536").end(-4162).row
MsgBox d
で旨く行くようです。
これが原因かも。
-4162はエクセルVBE画面で
Sub test01()
MsgBox xlUp
End Sub
を実行して判りました。

お礼

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

最終的には DoCmd.TransferSpreadsheet が一番楽そうなのでこれで
行こうと思います。

また、最終行の取得には d = ws.range("a65536").end(-4162).row
が動きましたので、これを使わせてもらいたいと思います。

どうもありがとうございました。

回答No.3

imogasi

判らないのに投稿して済みません。
Sub test0a()
Dim ex As Object
Set ex = CreateObject("excel.application")
ex.Application.Visible = True
ex.Workbooks.Open FileName:="c:\My Documents\aaa14.xls"
Set ws = ex.Worksheets("Sheet1")
ws.Activate
'MsgBox ws.Range("A65536")
ws.Range("A65536").select
'.End(xlUp).Row
MsgBox ws.Range("a1")
ex.Workbooks.Close
ex.Quit
Set ex = Nothing
End Sub
では旨く行きます。
だのに
MsgBox ws.Range("A65536").End(xlUp).Row
や
MsgBox ws.Range("A65536").End(xlUp).select
ではエラーに成ります。
Set rg = ws.range("A65536")
d = rg.Row
MsgBox d
などは旨く行きます。
End(xlUp)に原因がないか調べ中です。私の力では判りそうもありませんが。

回答No.1

snoopy64

エラーになる原因はちょっとわかりませんが・・・
そのエラーの直前で
MsgBox ws.Range("A65536").End(xlUp).Value
とやると、最終セルの値がちゃんと表示されますね。

このことから想像するに、GetObjectはあくまでもオブジェクトを取得するだけで、ブックをオープンしているわけではないので、Selectのような「メソッド」（表示系）は使えないのかな～と。
Valueのような「プロパティ」（値系）なら使えるのでしょうかね・・・
あくまでも感覚的なものですが。