excel:あるsheet Ｂからその中の情報で別のsheetを表示する。

質問

(問題概要）あるsheet　Ｂからその中の情報で別のsheetを表示する。

Ｍｙdocの中のＡというフォルダに、ExcelのBOOKが複数個(A1～Ａ5とＢ）あり、また各ｂｏｏｋには複数個のシートが入っています。
ブックＢの中に表形式で
　　Ｂ.xlsのsheet1
　　(表）
各book名　各sheet名
Ａ1.xls　　　 　Ａ1-1sheet　
Ａ1.xls Ａ1-2sheet
Ａ1.xls Ａ1-3sheet
Ａ2.xls Ａ2-1sheet
～
Ａ5.xls　　 Ａ5-1sheet
Ａ5.xls　　 Ａ5-2sheet
などと入っています。
やりたいことは、この表のsheetの所などをクリックしたときに(方法はプログラムを作り安い方法でよいのですが）、当該シートを開き表示するようにしたいが、マクロがいいですか。その場合マクロはどんなふうに書けばいいでしょうか。

またＢの表を作るため、ＢＯＯＫ内のsheet名の情報を採集する(コピー）方法はありますか

回答No.2

pauNed · ベストアンサー

ごめんなさい。式間違ってますね^ ^;正しくは
=HYPERLINK("[C:Ｍｙdoc\Ａ\"&A2&"]'"&B2&"'!a1",B2)
『&』が１コ余計でした...

ついでに。
マクロが置いてあるBookと同じフォルダ内のxlsBookをリストアップします。
とりあえずサンプルなので新規Sheetを追加して書き出します。
Sub sample()
　　Dim fol As String
　　Dim fnm As String
　　Dim sh As Worksheet
　　Dim i As Long, n As Long
　　Dim v(), x()
　　fol = ThisWorkbook.Path & "\"
　　If MsgBox(fol & " の処理を行います。OK?", vbOKCancel) = vbCancel Then Exit Sub
　　With Application
　　　　.ScreenUpdating = False
　　　　.EnableEvents = False
　　　　.Calculation = xlCalculationManual
　　End With
　　fnm = Dir(fol & "*.xls")
　　Do Until Len(fnm) = 0&
　　　　If Not fnm Like ThisWorkbook.Name Then
　　　　　　With Workbooks.Open(Filename:=fol & fnm, updatelinks:=False, ReadOnly:=True)
　　　　　　　　i = i + 1
　　　　　　　　For Each sh In .Worksheets
　　　　　　　　　　n = n + 1
　　　　　　　　　　ReDim Preserve v(1 To 1, 1 To n)
　　　　　　　　　　ReDim Preserve x(1 To 1, 1 To n)
　　　　　　　　　　v(1, n) = fnm
　　　　　　　　　　x(1, n) = "=HYPERLINK(""[" & fol & fnm & "]'" _
　　　　　　　　　　　　　　　　& sh.Name & "'!a1"",""" & sh.Name & """)"
　　　　　　　　Next sh
　　　　　　　　.Close savechanges:=False
　　　　　　End With
　　　　End If
　　　　fnm = Dir()
　　Loop
　　If n > 0 Then
　　　　With Worksheets.Add
　　　　　　.Range("a1").Resize(n).Value = Application.Transpose(v)
　　　　　　.Range("b1").Resize(n).Formula = Application.Transpose(x)
　　　　End With
　　End If
　　With Application
　　　　.Calculation = xlCalculationAutomatic
　　　　.EnableEvents = True
　　　　.ScreenUpdating = True
　　End With
　　MsgBox i & " Books & " & n & " Sheets" & vbLf & "処理終了"
End Sub
うまくいかなかったらごめんネ^ ^;

お礼

うまくいきすぎました。目的達成１５０％
すばらしいですね。
どうもありがとうございました。

回答No.1

pauNed

こんにちは。表ができている状態であれば、HYPERLINK関数を使えば比較的簡単だと思います。
C2セルに=HYPERLINK("[C:Ｍｙdoc\Ａ\&"&A2&"]'"&B2&"'!a1",B2)
な感じで。

マクロ処理の場合も式を埋め込むようにしたらいいかと思います。
>sheet名の情報を採集
するところからつくるとすれば、
ブック数、シート数がそれほど多くなければ
1)特定のフォルダに対してVBAのDir関数を使って１つずつExcelブックを開く。
　この時、画面更新やイベントを抑制してマクロの実行速度をあげる。
2)開いたブック内の全シートをForEach...Nextステートメントを使ってループ処理する。
3)書き出しシートを変数に格納しておいて、２行目からBook名と関数を埋め込んでいく。
　関数部分は例えば
　シート.Cells(i,2).Formula = "=HYPERLINK(""[C:Ｍｙdoc\Ａ\" & ファイル名 & "]'" _
　　　　　　　　　　　　　　　& シート名 & "'!a1"",""" & シート名 & """)"
な感じで。
また、配列を使うスキルがおありなら、さらに処理時間は短縮できると思います。

お礼

回答２でのフォローごていねいに、ありがとうございます。
少し間違いがあったほうがこちらも調査し勉強になります。