Excel VBA、ファイルのオープンについて

質問

どうにも解決が付かず、困ってしまっています、どなたかご回答をお願いいたします。

デスクトップ上にＡというフォルダがあり、その中に二つのファイルａ.xls、ｂ.xlsがあるとします。
今、ａ.xlsを開いて、その中に「データ入力表」というシートを作り、そこに入力したデータをマクロを貼り付けたボタン一つでｂ.xlsへデータ蓄積できるようにしようと考えています。蓄積データが大量になることが予想されるので、別ファイルとしたかったのですが、そうすると、何度もｂ.xlsにデータを転記する必要があるので、ｂ.xlsはその都度開いたり閉じたりせずに開いたままにしておきたいわけです。
そこで、ボタンに貼り付けるマクロには、
・最初にデータ転記する時にｂ.xlsを開く
・以降のデータ転記時にはｂ.xlsは開いたままにする
ように、IF文で分岐を付けたいのですが、最初にファイルを開くのは
　Set wb = Workbooks.Open("ｂ.xls")
で良いわけですが、「ｂ.xlsを開いたままデータを転記する」場合がどうにもうまくいきません。
ファイルをオブジェクト変数にセットするように、
Set wb = Workbook("ｂ.xls")
とすると「インデックスが有効範囲にありません」と怒られ、パスの設定か？と思い、
myDir.Workbook("ｂ.xls")
とすれば「オブジェクトが必要です」
と断られてしまいます。
この、転記先となるｂ.xlsを開いたままデータを転記する場合には、オープンしてあるｂ.xlsはどうセットしたら良いのでしょうか？、というのが今回の質問です。

まだ初心者のため要点が掴みづらい文章となってしまい、お答えしにくいかとは思いますが、どなたかこの混迷状態をご理解頂けた方、ご返答を宜しくお願いいたします。

回答No.4

Wendy02 · ベストアンサー

#3さんのつけたしになっちゃいますが、変数の使い方がきちんと分かっていれば、モジュールレベルの変数という方法もあると思います。 Public 変数だと、残し忘れがありますが、どちらでも良いと思います。

Dim wb As Workbook　'モジュールレベルの変数
Sub test_open()
'開ける時
If wb Is Nothing Then
　Set wb = Workbooks.Open("b.xls")
End If
End Sub

Sub Sheet_Copy()
'コピーするとき
If Not wb Is Nothing Then
　ThisWorkbook.Sheets(1).Cells.Copy wb.Sheets(1).Range("A1")
'ここは工夫してください。
End If
End Sub

Sub Test_Close()
'閉じる時(ユーザーが閉じてしまった場合に、エラー・トラップを入れておく）
On Error Resume Next
　If Not wb Is Nothing Then
　　wb.Save
　　wb.Close
　End If
Set wb = Nothing
End Sub

お礼

ありがとうございました。
No.3さんとNo.4さんのご回答で、ようやくエラーの謎が解けました。変数のセットについてもう一度勉強し治してくる必要がありますね、ほんとうにご回答有り難うございました。

回答No.5

quit123

No.3です。
No.4のWendy02さんの回答で何故エラーが出るのかお分かりですよね。

If wb Is Nothing を使うときは、オブジェクト変数wbは
モジュールレベルかパブリックレベルでないといけません。
そこを気付いてもらうためにわざとその説明を抜かしました。

この際に”変数の摘要範囲”をしっかりと理解するようにしてください。
プログラムを書くためには必須ですから。

お礼

No.4さんへのお礼にも書きましたが、お二人のおかげでエラーの謎が解けました。本当にありがとうございました。
なかなか成書にも問題解決の糸口となるヒントが見つからず、付け焼き刃のプログラム書きではやはり基本的な部分、今回のような変数の適用範囲のようなエラーの解決には限界がありました。
おっしゃるとおり、もう一度基本的な変数の使用法に関して勉強しておかなければならないことを痛感させられました。
本当にご回答ありがとうございました。

回答No.3

quit123

こんなんでも出来るのでは？

If wb Is Nothing Then
　　Set wb = Workbooks.Open("ｂ.xls")
End If

お礼

ご回答有り難うございます。
これだと、やはりｂ.xlsを開いたままの状態でマクロを実行すると、「ｂ.xlsはすでに開かれています。二重に開くと・・・」のアラートが出てしまいます。
これが出ないようにしたいんですけどね～・・・

回答No.2

papayuka

エラーは#1さんの回答で回避出来ると思います。

開くファイル名やパスが同じなら、サブルーチン化してあげるとか。
開いているブック内から b.xls を探して、あれば変数にセット、
無ければこのマクロが書かれたファイルと同じフォルダ内にある
b.xls を開いてセットします。

'--------------------------------------------
Sub Test()
Dim wb As Workbook
　Call BookOpenCheck(wb)
　MsgBox wb.Name
End Sub

'--------------------------------------------
Sub BookOpenCheck(wb As Workbook)
Dim twb As Workbook, Flg As Boolean
　Flg = False
　For Each twb In Workbooks
　　　If twb.Name = "b.xls" Then
　　　　　Set wb = twb
　　　　　Exit Sub
　　　End If
　Next twb
　If Not Flg Then
　　 Set wb = Workbooks.Open _
　　　　 (ThisWorkbook.Path & "\b.xls")
　End If
End Sub

お礼

ご返答有り難うございます。
一度開いてあるファイルをもう一度オープンしようとするとエラーが出ますよね？最初にデータを転記した後はｂ.xlsは開いたままになっているので、次のデータを入力・転記する時には、ｂ.xlsは開いたままになっていますからこの状態で（ｂ.xlsをオープンせずに）変数にセットしたい、ということで、IF分岐を考えたのですが・・・
サブルーチンを使ってファイルを探してあれば変数にセット、というのは思いつきませんでした。大変参考になりました、ありがとうございました。

回答No.1

BLUEPIXY

>Workbook("ｂ.xls")
Workbooks("ｂ.xls")
かと思います

お礼

ご返答ありがとうごじました。
初歩的ミスで申し訳ありませんでしたが、
Workbooks("ｂ.xls")
と、"s"を入れてある状態で「インデックス範囲が有効範囲にありません」というエラーメッセージがでるのですね～・・・
どうしてこういうエラーが出るのかが・・・。