This is a list of files and sheets (not hyperlinked though):
[vba]Sub ListFilesAndSheets()
Dim FS As FileSearch
Dim lngCounter As Long, lngRow As Long, lngOutputrow As Long, lngSheetCount As Long
Dim wbk As Workbook, wks As Worksheet
Dim rngData As Range
Dim strParentFolder As String
Dim varSheets
strParentFolder = GetFolder()
If Len(strParentFolder) = 0 Then Exit Sub
Application.ScreenUpdating = False
lngOutputrow = 1
Set FS = Application.FileSearch
With FS
.NewSearch
.LookIn = strParentFolder
.SearchSubFolders = True
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
' Loop through all the found files
For lngCounter = 1 To .FoundFiles.Count
varSheets = ListSheetsInFile(.FoundFiles(lngCounter))
lngSheetCount = UBound(varSheets)
Cells(lngOutputrow, 1).Value = .FoundFiles(lngCounter)
Range(Cells(lngOutputrow, 2), Cells(lngOutputrow + lngSheetCount - 1, 2)).Value = Application.Transpose(varSheets)
lngOutputrow = lngOutputrow + lngSheetCount
Next lngCounter
End With
Set FS = Nothing
Application.ScreenUpdating = True
End Sub
Function ListSheetsInFile(ByVal strFile As String) As String()
Dim xlConn As Object 'ADODB.Connection
Dim xlSheets As Object 'ADODB.Recordset
Dim astrSheets() As String, strSheet As String
Dim lngSheetCounter As Long
' On Error GoTo err_handler
'connect to the file
Set xlConn = CreateObject("ADODB.Connection")
With xlConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0;IMEX=1"
.Open strFile
End With
lngSheetCounter = 1
'see sheet names in the immediate window
Set xlSheets = xlConn.OpenSchema(20) '20=adSchemaTables
With xlSheets
Do While Not .EOF
strSheet = .Fields("TABLE_NAME").Value
If InStr(1, strSheet, "$") > 0 Then
If Right$(Replace$(strSheet, "'", ""), 1) = "$" Then
ReDim Preserve astrSheets(1 To lngSheetCounter)
astrSheets(lngSheetCounter) = Replace$(Left$(strSheet, InStr(1, strSheet, "$") - 1), "'", "")
lngSheetCounter = lngSheetCounter + 1
End If
End If
.MoveNext
Loop
End With
clean_up:
On Error Resume Next
ListSheetsInFile = astrSheets()
xlSheets.Close
Set xlSheets = Nothing
xlConn.Close
Set xlConn = Nothing
Exit Function

err_handler:
MsgBox Err.Number & ": " & Err.Description
Resume clean_up
End Function
Function GetFolder() As String
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
If dlg.Show = -1 Then
GetFolder = dlg.SelectedItems(1)
End If
End Function
[/vba]