+ Reply to Thread
Results 1 to 4 of 4

macro to list files in folder and sub folders has stopped working

  1. #1
    Registered User
    Join Date
    03-18-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    12

    macro to list files in folder and sub folders has stopped working

    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

  2. #2
    Registered User
    Join Date
    03-18-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    12

    Re: macro to list files in folder and sub folders has stopped working

    Hi All,
    I Have just been told that someone took it upon themselves to ever so slightly change the name of the folder so I have got it working again

  3. #3
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: macro to list files in folder and sub folders has stopped working

    Hi Lme10611,

    This will check the existence of the 'startFolderPath' directory and if it doesn't exist it will alert the user and quit:

    Please Login or Register  to view this content.
    HTH

    Robert

    PS - Please wrap your code as I have done.
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  4. #4
    Registered User
    Join Date
    03-18-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    12

    Re: macro to list files in folder and sub folders has stopped working

    Thanks Robert,
    I have added the reminder as per your post.
    Sorry I didn't wrap the code I'm still new to this.
    Thanks again much appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Move Files from a Folder to Newly created folders based on value in a cell
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2015, 08:04 PM
  2. [SOLVED] Recursive List Folder include hidden & system folders and files
    By CobraLAD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2015, 03:45 PM
  3. Replies: 0
    Last Post: 10-15-2014, 04:37 AM
  4. Needs macro to copy excel files from folders, subfolders to new folder
    By genetist in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2014, 08:53 AM
  5. [SOLVED] Macro to open and close all files in a folder and sub folders
    By BillDoor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-31-2012, 06:51 AM
  6. [SOLVED] Macro lists files in folders & subfldrs - Need to add ability for user to select folder
    By BeachBum08 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-31-2012, 12:15 PM
  7. Replies: 0
    Last Post: 07-13-2012, 10:51 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1