複数のファイルよりシートの検索を行うには

質問

お世話になっております。

以下のような事をおこないたいのですが、ＶＢＡで可能でしょうか？

(1)ファイルＡのボタンを押す
(2)複数のファイルを開く
(3)入力した案件コード名のシートが存在するファイルを選択する
(4)存在したシートの値をファイルＡにコピーする

(1)及び(2)、(4)については個々の動きではありますが
実行できる事はわかったのですが、(3)の部分に
ついてはどうやったらいいのかが分からない状態です。

背景
現在、会社にて部署別の案件管理をしているのですが、現在は、対象部署が１つしかないので、ファイルＡのＶＢＡ上に対象ファイルを記述しているのですが、今後対象部署が増えるので今後の事も考慮し、対象ファイルをソース上に記入した場合、組織改正毎に修正するのが大変なので、絶対に重複しない案件コードで対象ファイルを選択して情報をコピーする事ができれば、メンテナンスの面からも楽だと思い、
このような事ができないかと思案しております。

決まった１ファイルのみを選択して、入力したコード名のシートを選択しコピーする事はできるのですが、複数のファイルを開いた場合どのようにＶＢＡを記述すればよいかわからなく、困っております。
お忙しいところ申し訳ありませんが、宜しくお願い致します。

回答No.2

matsu_jun · ベストアンサー

ごめんなさい。昨日より不調のmatsu_junです。以下のように訂正します。

For i# = 1 To Workbooks.Count
For j# = 1 To Workbooks(i).Worksheets.Count
If Workbooks(i).Worksheets(j).Name = Sh_Name Then GoTo NEXTSTEP
Next j
Next i
NEXTSTEP:

Workbooks(i).Worksheets(j) が目的のシートです。

お礼

ありがとうございます。
おかげで、問題が解決致しました。
ただ、また別用件が生じてしまいましたが、
当初課題でありました当問題は解消できました。

回答No.3

noname#240783

下記のコードで参考になりませんか。
Cドライブのtestというフォルダがあり、その中に
複数のExcelファイル（.xls）があるという前提で。
Dirメソッドでファイル名を取得して、それを開いて
For Eachでbook内のシートを全部なめて表示し、bookを閉じ、
次のExcelファイルを探して上記の動作を繰り返す、というものです。

book内のシートを全部舐めるところで、
目的のシート名と照合させればいいんじゃないでしょうか。

Private Sub CommandButton1_Click()
Dim i As Integer
Dim target As String
Dim ws As Worksheet
i = 1
target = Dir("C:\test\*.xls")
If target = "" Then
Exit Sub
End If

Cells(i, 1).Value = target
Workbooks.Open target

For Each ws In Workbooks(target).Worksheets
Cells(i, 2).Value = ws.Name
i = i + 1
Next ws
Workbooks(target).Close

Do
target = Dir()
If target = "" Then
Exit Do
Else
Cells(i, 1).Value = target

Workbooks.Open target
For Each ws In Workbooks(target).Worksheets
Cells(i, 2).Value = ws.Name
i = i + 1
Next ws
Workbooks(target).Close
End If

Loop

End Sub

お礼

ありがとうございます。
当初課題でありました、当問題は解消致しました。

回答No.1

matsu_jun

検索したいシート名を、変数 Sh_Name ( As String ) に格納するとします。その上で、以下の記述を行ってください。

For Each wb In Workbooks
For Each sh In Worksheets
If sh.Name = Sh_Name Then GoTo NEXTSTEP
Next sh
Next wb
NEXTSTEP:

オブジェクト変数「wb」に目的のシートを含むブックが、同じくオブジェクト変数「sh」に目的のシートがセットされます。
試しにその下の行に

MsgBox wb.Name
MsgBox sh.Name

と記述して実行してください。検索したいシートを含むブックの名前がメッセージボックスに表示されます。OKをクリックすると次に検索されたシート名がメッセージボックスに表示されます。例えば
sh.Cells(1, 1).Value
とすれば、検索したシートのセルA1の値が取得できたりします。