List of all files in a folder

S

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
288
Office Version
  1. 365
Hi,

Is there any code that would output all the files (excel and pdf) that are in a folder and it's sub-folders - I can't even think of how to start this ?

The folder is called Q:\DealFiles
and has sub-folders called Austria, Belgium etc....UK

I would like to get a list of all files that are saved in these folders / sub-folders....any ideas ?

thanks
Steve
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Try this, which requires a reference to Microsoft Scripting Runtime:

Code: 
Sub TestListFilesInFolder()
    ThisWorkbook.Worksheets.Add
    With Range("A1")
        .Formula = "Folder Name"
        .Font.Bold = True
    End With
    With Range("B1")
        .Formula = "File Name"
        .Font.Bold = True
    End With
'***Change to suit***
    ListFilesInFolder "C:\FolderName\", True
End Sub
 
Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("A65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        ' display file properties
        Cells(r, 1).Formula = SourceFolderName
        Cells(r, 2).Formula = FileItem.Name
        r = r + 1 ' next row number
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Columns("A:H").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub
 
Upvote 0
Hi Andrew,

Thanks for the code, but please excuse my ignorance but what does "requires a reference to Microsoft Scripting Runtime" and how do I action this ?

The code runs through to

Dim FSO As Scripting.FileSystemObject - giving an error message
Compile error:User-defined type not defined

thanks
Steve
 
Upvote 0
In the Visual Basic Editor click your workbook in the Project Window and choose Tools|References from the menu. Check Windows Scripting Runtime and click OK.
 
Upvote 0
Thanks Andrew,
Found it and the macro runs perfectly, thanks for your help
Steve
 
Upvote 0
Andrew Poulsom said:
In the Visual Basic Editor click your workbook in the Project Window and choose Tools|References from the menu. Check Windows Scripting Runtime and click OK.
Click to expand...

Is there a way do do this without requiring Windows Scripting Runtime lib reference?
e.g. change declaring dimensions to types :
Code: 
Dim FSO As Scripting.FileSystemObject
to this
Code: 
Dim FSO As Object 
Set FSO = CreateObject("Scripting.FileSytemObject")

But this line still gives an error without scripting reference lib?
Code: 
Set FSO = New Scripting.FileSystemObject

I'm trying to do this without Windows Scripting Runtime lib reference:
Code: 
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("A65536").End(xlUp).row + 1
    For Each FileItem In SourceFolder.Files
        ' display FileItem properties
        'i.e. Path, Name, Dize, Type, DateCreated, DateLastAccessed,
        '     DateLastModified,Attributes, ShortPath, ShortName
        Cells(r, 1).Formula = FileItem.path
        Cells(r, 2).Formula = FileItem.Name
        Cells(r, 3).Formula = FileItem.Size
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 5).Formula = FileItem.DateLastAccessed
        Cells(r, 6).Formula = FileItem.DateLastModified
        Cells(r, 7).Formula = FileItem.Type
        r = r + 1 ' next row number
    Next FileItem
endif

I'm sure I'm missing something, but need some of the bright minds here to help educate me...
 
Upvote 0
If you have this:

Code: 
Set FSO = CreateObject("Scripting.FileSytemObject")

you don't need this:

Code: 
Set FSO = New Scripting.FileSystemObject
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Permision Denied-VBA Code to list sub-folders
Replies
4
Views
168
howard
H
N
  • Question Question
VBA search for files in multiple folders from Cell input
Replies
6
Views
151
nogarth
N
N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
176
NKUNZI
N
S
  • Question Question
Move files from folder to another folder based on listbox file names Selected
Replies
7
Views
173
John_w
J
R
  • Solved
Export excel file as PDF into a targeted pc folder based on cell value
Replies
2
Views
309
Ramadan2512
R

Forum statistics

Threads
1,225,156
Messages
6,183,230
Members
453,152
Latest member
ChrisMd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top