フォルダ内の複数のエクセルファイルのシート名一覧を取得してハイパーリンクで開く方法を紹介します。

検索したシート名はエクセルシートの2行目から下に表示されます、表示されたシート名をクリックするとファイルを開いてシートを選択することができます。

Sub シート名一覧を取得してハイパーリンクで開く()

 Dim folder_name As String
 Dim file_name As String
 Dim 行 As Long
 Dim 列 As Long
    
 列 = 1                                   'ファイル名を表示する列は1列目から始めます。
 
 folder_name = ThisWorkbook.Path & "\フォルダ\"        '検索するフォルダを指定します。
    
 file_name = Dir(folder_name & "*.xlsx")                        'Dir関数で検索します。
    
 Do While file_name <> ""  'フォルダ内にエクセルのファイルが見つかる間は処理をします。
    
    Workbooks.Open folder_name & file_name                       'ファイルを開きます。
    
    行 = 1                                                '行番号の初期値を1にします。
               
    Dim all_sheet As Worksheet
      
    For Each all_sheet In Worksheets                     'シート名を順番に取得します。
          
       行 = 行 + 1                  'ファイル名を表示する行を1つずつ増やしていきます。
       
       With ThisWorkbook.Worksheets("Sheet1")            'ハイパーリンクの設定します。
        
        .Hyperlinks.Add anchor:=.Cells(行, 列), _
                        Address:=folder_name & file_name, _
                        SubAddress:="'" & all_sheet.name & "'" & "!A1", _
                        TextToDisplay:=all_sheet.name
       End With
          
    Next
         
    Workbooks(file_name).Close savechanges:=False                'ファイルを閉じます。
        
    file_name = Dir()                          'フォルダ内の次のファイルを検索します。
        
    列 = 列 + 1              'ファイル名を表示する列を1列目から右に1つずつ移動します。
 
 Loop
    
 End Sub