hi All,
I have been using the below macro (copied from a forum on here but I cant find it again sorry) to list all the names of files in all subfolders of a folder. It has been working until today when i get the error message Run-Time Error '76' Path Not Found.
All I have been doing in the past is changing the startFolderPath name and it has worked but today tried to change the 2012 to a 2013.
When I go into Debug the highlighted line is "If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")"
Can you please help this macro saves me hours of work.
Option Explicit
Public Sub Hierarchical_Folders_and_Files_Listing()
Dim startFolderPath As String
Dim startCell As Range
startFolderPath = "J:\BULLETIN - JJ-ZT\BulletinArticles\2012"
Set startCell = Sheets("Sheet1").Range("A1")
startCell.Parent.Cells.Clear
List_Folders_and_Files startFolderPath, startCell
End Sub
Private Function List_Folders_and_Files(folderPath As String, destCell As Range) As Long
Static FSO As Object
Dim thisFolder As Object, subfolder As Object
Dim fileItem As Object
Dim n As Long
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set thisFolder = FSO.GetFolder(folderPath)
'Add hyperlink for this folder
destCell.Parent.Hyperlinks.Add Anchor:=destCell, Address:=thisFolder.Path, TextToDisplay:=thisFolder.Name
'List subfolders and files in this folder
For Each subfolder In thisFolder.SubFolders
Set destCell = destCell.Offset(0, 1)
Set destCell = destCell.Offset(List_Folders_and_Files(subfolder.Path, destCell), -1)
Next
'Add hyperlink for each file in this folder
n = 0
For Each fileItem In thisFolder.Files
destCell.Offset(n, 1).Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, 1), Address:=fileItem.Path, TextToDisplay:=fileItem.Name
n = n + 1
Next
List_Folders_and_Files = n
End Function
Bookmarks