excel vba についての質問です。book名を変更したときのマクロ

excel vba についての質問です。
請求書と営業売上のそれぞれ違うbookがあるのですが、請求書に入力した値を営業売上管理のbookの売上シートと連動させています。
当たり前ですが、bookの名前が変わると今までに作ったマクロが動作しなくなります。それに対応できるソースがあれば教えて頂きたいです。簡単なbookとシート間の説明です。

workbook("請求書").worksheet("請求書")
workbook("営業売上管理").worksheet("売上")

と二つのbookがあります。
bookの名前が二つともかわっても対応できるようなマクロがあれば教えていただきたいです。
宜しくお願いします。

No.4 回答者： x415f484f 回答日時： 2008/03/10 23:08

まだ締め切られておられないようなので、ご質問やお答えになられている

方々のお話を参考にさせていただきこんなコードを書いてみました。

1. ブックは二つしか使わないから全て開くことは可能
2. ユニークな文字列でシートを判定

マクロがどちらのファイルに入るのかは不明ですが、どちらに入れても
動作すると思います。

動作確認はブックを二つ用意して、シート名に「売上」や「請求」を
入れておいて一度保存しておいたものを二つとも開きます。
標準モジュールを作成してコードを記述します。

お使いの環境の記述がないようなので使えるかどうかはわかりませんが、
ご参考になれば……

※ 注意: 見易さの為に全角文字でインデントしてあります。

--------------------------------------------------------------
Sub SearchSheetInBook()

Rem 識別用の文字列
Const Bill_key = "請求"
Const Salse_Key = "売上"

Rem ブックとシートの名前用変数
Dim Sales_Book As String
Dim Sales_Sheet As String
Dim Bill_Book As String
Dim Bill_Sheet As String

Dim wb As Workbook
Dim ws As Worksheet

Rem ブックが揃ってない場合の処理
If Workbooks.Count <> 2 Then
　MsgBox "もうひとつファイルを開いてください"
　Exit Sub
End If

Rem ブックから目的のデータのあるシートを探し変数に格納
For Each wb In Workbooks
　For Each ws In wb.Worksheets
　　If InStr(ws.Name, Bill_key) > 0 Then
　　　Bill_Book = wb.Name
　　　Bill_Sheet = ws.Name
　　　Exit For
　　Else
　　　If InStr(ws.Name, Salse_Key) > 0 Then
　　　　Sales_Book = wb.Name
　　　　Sales_Sheet = ws.Name
　　　End If
　　　Exit For
　　End If
　Next ws
Next wb

Rem 取り敢えず検証のためのコード
MsgBox "請求: " & Bill_Sheet & " in " & Bill_Book & vbCrLf & _
　 "売上: " & Sales_Sheet & " in " & Sales_Book

Rem シート名は変数に置き換える
Rem 既存の WorkBooks("請求書").WorkSheet("請求書") から
Rem WorkBooks(Bill_Book).WorkSheets(Bill_Sheet) に

Rem 既存の WorkBooks("営業売上管理").WorkSheet("売上") から
Rem WorkBooks(Sales_Book).WorkSheets(Sales_Sheet) に

End Sub
--------------------------------------------------------------

No.3 回答者： okormazd 回答日時： 2008/03/07 13:08

できないことはないと思います。

一般的にも不可能とは思いませんが、無理してそうするメリットがあるかどうかの問題でしょう。
対象のBOOKの特定のセルにユニークな名前をつけておいて、その名前でBOOKを特定すればいいでしょう。
制限はそのセルを変更されないようにすること。PCの全部のフォルダを検索するのは時間がかかるので、この作業をするフォルダはいくつかに限定すること。コピーした場合は候補を挙げて処理するBOOKを選ばせるか、一方の特定セルを変更するか、検索対象外のフォルダに移動させるか。
SheetのあるBOOKも同様ですが、BOOKを保護してSheet構成を変えないようにするか、BOOK検索と同様に特定セルのユニークな名前で検索させればいいでしょう。、

この回答へのお礼

book自体は二つしかないので、検索するのはとても簡単なのですが、
営業ごとにファイルを分ける必要があり、そこでどうしてもbookの名前を変更しなくてはなりません。

なかなか複雑なシート管理なので困ってます。
ご回答ありがとう御座いました。

お礼日時：2008/03/07 13:52

No.2 回答者： kokorone 回答日時： 2008/03/07 12:40

そのものずばりのマクロではないですが、

１）ブック検索
Sub Sample20()
Dim buf As String, i As Long
buf = Dir("C:\\text_pdf\*.xls")
Do While buf <> ""
i = i + 1
MsgBox (buf)

buf = Dir()
Loop
End Sub

２）シート検索
sck = 0
For Each sheet_name In Worksheets
If sheet_name.Name = ("売上") Then
sck = 1
Exit For
End If
Next
これを組み合わせれば、可能ですね。

No.1 回答者： kokorone 回答日時： 2008/03/07 11:09

はっきり言って無理ですね。

１）２つのブックが同じフォルダもしくは、固定のフォルダにあると仮定した場合
そのフォルダ内のすべてのブックを検索して、請求書シートを見つけなければなりません。
ブック名を変更した場合は、それで対応できますが、請求書ブックをコピーして、コピー
先を正とするような場合、旧ブックが残ったままですので、対応できません。
また、請求書シートが全く異なるブックに存在する場合も対応できません。

２）請求書ブックを任意のフォルダに移動した場合
どこを、どう探すのですか？？

この回答への補足

bookをコピーせずにbookの名前だけを変更し、そのbookを使っていく場合、フォルダの中の売上シートを検索して該当するbookを開く事は可能でしょうか？
そうすれば解決しそうな気もするんですが・・・

請求書シートは値を入力する際に、必ず開かなければいけないので
なんとかマクロに記述できましたが・・

どうでしょうか？？

補足日時：2008/03/07 11:12