I guess you'll want to use the FileSystemObject.
You need to add a reference in your Project to something called "Microsoft Scripting Runtime" (find it under Tools -> References...). If you can't find it in the list it should be located somewhere along the lines of "C:\WINNT\System32\scrrun.dll".
Having referenced your project the following basic (& untested!) code should be pretty much what you want:
Code:
Sub test()
Dim scrFSO As Scripting.FileSystemObject
Dim scrMasterFolder As Scripting.Folder
Dim scrSubFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim ws As Worksheet
Const strMasterFilePath As String = "C:\Something"
Set scrFSO = New Scripting.FileSystemObject
Set scrMasterFolder = scrFSO.GetFolder(strMasterFilePath)
For Each scrSubFolder In scrMasterFolder.SubFolders
Set ws = ThisWorkbook.Worksheets.Add
ws.Name = scrSubFolder.Name
For Each scrFile In scrSubFolder.Files
' Put your links in ws
Next scrFile
Next scrSubFolder
End Sub
Hope this helps,
Maccas
Organise Work Sheet Respect to folder structure
