エクセルＶＢＡ 実行時エラー'9'の回避方法について

質問

エクセルＶＢＡ　実行時エラー'9'の回避方法について

たいへん困っています。
あるファイルからキーワードを指定してデータを検索し数値項目に－１を掛けてすべて負数にしてから別のファイルにコピー、そのファイルを保存するという処理です。
ＶＢＡを作成し実行したところ正常に動作します。しかし、下記の条件下では実行時エラー'9'（インデックスが有効範囲にありません）が発生してしまいます。

(1)ＶＢＡを含むエクセルファイル及びデータファイルの保存先をドライブ（Ｓ）に置く。
(2)ドライブＳは他のＰＣからアクセスできる同一ネットワークとして定義されている。
(3)ネットワーク下にある他のＰＣからＳドライブにアクセスしＶＢＡを含むエクセルファイルを開いてこの処理を実行する。

分からないのは、インデックス処理に関係しない箇所でエラーが発生していること、同じＰＣ内で実行すると正常動作するのに、上記の条件下ではエラーが発生することです。
作成したＶＢＡは下記のとおりで、Set コピー元 = Workbooks("前回_情報").Worksheets("Sheet1")の箇所でエラーが発生します。エラーを回避する方法を教えてください。

Sub Macro7()
Dim i, j
Dim コピー元 As Worksheet
Dim コピー先 As Worksheet
Dim 検索値 As String
Application.ScreenUpdating = False
Workbooks.Open "S:￥情報出力データ￥前回_情報.xls"
Workbooks.Open "S:￥情報出力データ￥前回_情報コピー.xls"
Worksheets("計算結果").Cells.Clear '結果が格納されるシートを事前にクリア

検索値 = "unit"
Set コピー元 = Workbooks("前回_情報").Worksheets("Sheet1")　←　ここでエラー'9'が出る
Set コピー先 = Workbooks("前回_情報コピー").Worksheets("計算結果")
For i = 1 To コピー元.Cells(Rows.Count, 1).End(xlUp).Row
If コピー元.Cells(i, 1).Value = 検索値 Then
For j = 22 To 59
コピー元.Cells(i, j).Value = コピー元.Cells(i, j).Value * -1
Next j
コピー元.Rows(i).Copy コピー先.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next i
Set コピー元 = Nothing
Set コピー先 = Nothing

Workbooks("前回_情報.xls").Close SaveChanges:=False
Workbooks("前回_情報コピー.xls").Close SaveChanges:=True
End Sub

回答No.1

myRange · ベストアンサー

＞同じＰＣ内で実行すると正常動作するのに、上

これはフォルダーオプションの
「登録されている拡張子は表示しない」のチェックが外れてるとエラーが出ると思いますが、、、

何れにしろ、提示のコードでは

>Set コピー元 = Workbooks("前回_情報").Worksheets("Sheet1")
>Set コピー先 = Workbooks("前回_情報コピー").Worksheets("計算結果")

この２行には拡張子が省略されていますので、拡張子を入れてみたらどうでしょうか。

Set コピー元 = Workbooks("前回_情報.xls").Worksheets("Sheet1")
Set コピー先 = Workbooks("前回_情報コピー.xls").Worksheets("計算結果")

以上です。

お礼

myRange様、ありがとうございます。いま、ネットワーク環境下にいないためテストできませんが、あとで確認したいと思います。迂闊でしたがご指摘の通りと思います。このような条件の場合にはエクセルファイルであることを明示しないとだめなのでしょうか。このことと実行時エラー'9'が関係する理屈が分かりませんが。

回答No.3

temtecomai2

おそらく #1 さんの回答で合っていると思います。

んで書き方を下のようにするとか。

Dim コピー元Book As Workbook, コピー元Sheet As Worksheet
Dim コピー先Book As Workbook, コピー先Sheet As Worksheet

Set コピー元Book = Workbooks.Open("S:￥情報出力データ￥前回_情報.xls")
Set コピー元Sheet = コピー元Book.Worksheets("Sheet1")

Set コピー先Book = Workbooks.Open("S:￥情報出力データ￥前回_情報コピー.xls")
Set コピー先Sheet = コピー先Book.Worksheets("計算結果")

Worksheet を変数で扱う知識を持っているのなら Workbook も変数で扱うべきでしょう。
Workbooks.Open() メソッドは開いたブックを Workbook オブジェクトとして返してくれるので Workbook 型の変数で参照してしまいましょう。

回答No.2

Wendy02

私なりの書き方に修正してみました。ダイレクトにシートに入れるよりも、それぞれブックを変数に置き換えてみれば、初歩的なミスは少なくなると思います。Bk1, Bk2 とか使いますが、そうでなければ、以下のようにします。しかし、最後に、
Workbooks("前回_情報.xls").Close SaveChanges:=False
Workbooks("前回_情報コピー.xls").Close SaveChanges:=True
としているなら、変数に置き換えたほうが楽ではないでしょうか。

こういう凝った書き方は良いとは思いませんが、あえて近づけてみました。

Dim i As Long, j As Long
Dim コピー元 As Worksheet
Dim コピー先 As Worksheet
Dim 検索値 As String
With Workbooks.Open("S:\情報出力データ\前回_情報.xls")
　Set コピー元 = .Worksheets("Sheet1")
End With
With Workbooks.Open("S:\情報出力データ\前回_情報コピー.xls")
　 Set コピー先 = .Worksheets("計算結果")
End With
Application.ScreenUpdating = False　'←下に持ってきた
Worksheets("計算結果").UsedRange.Clear

なお、正しく動くかどうかのチェックはされていません。

お礼

Wendy02様、ていねいな回答ありがとうございます。単純な間違いをなくすには変数に置き換えたほうがクレバーなやり方ですね。了解しました。今後とも機会があればよろしくご指導お願いします。