VBA Tips - List Files In A Folder

cccc

Hello,

I've found 2 similar threads on this forum, but neither help out.

I have some folders and I would like a macro that would list the file names (and dates if possible) within any given folder. To make it easier, I would prefer the macro would simply run from within the folder to get the list for vs entering the path of a folder.

Can anyone help?

I would like to list all the files in a given folder (for this excersise I have used c:\test\)

However I don't want to just list .xls files.

There will be .mp3, .txt, .jpg & .doc files as well.

I would like to list names in range A:A

Thanks
Kevin

Hi,

I am trying to modify a little bit of code that lists all the files in a folder, the code works well and places the list in Column A, what I would like it to do is place the code in Column P but i'm struggling with this. If anyone can help I would be really grateful.

This code is actually listed on the site in an archive somewhere from 2001

Thanks.
Alec.

Code:

Sub DirList()
  Const ListDir = "C:\Data\"
  If Not Dir(ListDir & "*.xls") = "" Then
    FList = Dir(ListDir & "*.xls")
    R=1
    Do Until FList = ""
      Cells(R,1).Value = ListDir & FList
      R = R + 1
      Flist = Dir
    Loop
  End If
End Sub

Good afternoon all,

I have a folder that has a lot of documents in it. I also have an excel file that lists all the documents and the file paths. Basically an index that allows a user to select a document from a listbox and open it.

What I want to do is automate the updating of this list. I want to have a button that I will press, on a userform, that will then compare the files in the folder to the list in my spreadsheet. Any new files will be added to a listbox for the user to select and provide additional details.

What I am not sure is how to grab the file name of the files in the folder without opening the open dialog box. Ideas?

Thank you in advance!!!
dw

Evening,
I have a folder C:\My Documents that contains excel & Pdf files.
Is it possible to list all the files by name contained in this folder in a excel spreadsheet i.e one filename one cell?

RIk

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

Tried looking for this online but what I have found does too much or doesn't work at all. I am trying to get a list of filenames from within a folder. I only want the filename, no other attributes. The folder will be referenced from a cell on the workbook B5. The list is start at A8. I'm not going to put the code I found in here because I think there may be better way of doing it than what I have found and don't want a patched up version of it.

Greetings one and all
Everyone must start somewhere so I choose to start on this old post since it happens to applicable to my stumbling block. I too needed to list all the files, folders and subfolders in a given directory and was able to find a pair of macros on the web which gave me most of what I needed (plagiarism is a wonderful thing) and then with a bunch of modification I came up with the macros below. My problem is in the second macro (ListFilesInFolder). I have been unable to get Microsoft to give up the file "owner" (Person who created the file) to my spreadsheet. I can see it just fine when I am in that folder, I just can't get it into my excel file. The line that is giving me the trouble is:

Code:

Cells(r, 6).Formula - FileItem.Owner

Does anyone know what I need to do to make this work?
Randy P



Here are the macros:

Code:

Sub TestListFilesInFolder()
'    Workbooks.Add ' create a new workbook for the file list
    ' add headers
    
    
'Clear out existing data
    ActiveWindow.Panes(1).Activate
    Columns("A:S").Select
    Selection.Delete Shift:=xlToLeft
    
'Set column headers
    With Range("A1")
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A1").Formula = "File Name:"
    Range("B1").Formula = "Path:"
    Range("C1").Formula = "File Size:"
    Range("D1").Formula = "Date Created:"
    Range("E1").Formula = "Date Last Modified:"
    Range("F1").Formula = "Owner:"
    Range("H1").Formula = "Path:"
    Range("I1").Formula = "File:"
    Range("I1").Formula = "File:"
    Range("J1").Formula = "Programmer:"
    Range("K1").Formula = "Charged Out:"
    Range("L1").Formula = "Update Date:"
    Range("M1").Formula = "Days old:"
    Range("N1").Formula = "EC1:"
    Range("O1").Formula = "EC2:"
    Range("P1").Formula = "EC3:"
    Range("A1:P1").Font.Bold = True
    Range("A2:P2500").Font.Bold = False
    
'Add comments
    Range("N1").Select
    Selection.ClearComments
    Range("O1").Select
    Selection.ClearComments
    
    Range("N1").AddComment
    Range("N1").Comment.Visible = False
    Range("N1").Comment.Text Text:="Is this charged out" & Chr(10) & "in MOM?" & Chr(10) & ""
    Range("N1").Select
    
    Range("O1").AddComment
    Range("O1").Comment.Visible = False
    Range("O1").Comment.Text Text:="Has it been charged out" & Chr(10) & "more than 30 days?" & Chr(10) & ""
    Range("O1").Select
    
    
    ListFilesInFolder "M:\NCW\", True
End Sub

Code:

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 = FileItem.Name
        Cells(r, 2).Formula = FileItem.Path
        Cells(r, 3).Formula = FileItem.Size
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 5).Formula = FileItem.DateLastModified
'       Cells(r, 6).Formula = FileItem.Owner
        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:G").ColumnWidth = 4
    Columns("H:I").AutoFit
    Columns("J:L").ColumnWidth = 12
    Columns("M:P").ColumnWidth = 8
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

Hello,

Is there a way list load all the files names in a folder in to a listbox in a userform

Thanks