Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Toronto, Ontario, Canada
    Thanked 0 Times in 0 Posts

    Folder Files (XP)

    I have about 100 files in a folder in My Documents that I would like to have itemized in an Excel spreadsheet. Is there an easy way to import the file names?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Folder Files (XP)

    Copy the following code into a standard module, then run the ListFiles macro. It'll prompt you for a folder, then list the files in the active worksheet.
    Note: the worksheet will be cleared first!

    'BrowseFolder from Don Ceraso:

    Public Function BrowseFolder(Optional Title As String = "Select a Folder", _
    Optional RootFolder As Variant) As String
    On Error Resume Next
    BrowseFolder = CreateObject("Shell.Application").BrowseForFolder _
    (0, Title, 0, RootFolder).Items.Item.Path
    End Function

    Sub ListFiles()
    Dim strFolder As String
    Dim strFile As String
    Dim i As Integer
    strFolder = BrowseFolder
    If strFolder = "" Then Exit Sub
    If Not Right(strFolder, 1) = "" Then
    strFolder = strFolder & ""
    End If
    strFile = Dir(strFolder & "*.*")
    Do While Not strFile = ""
    i = i + 1
    Cells(i, 1) = strFile
    strFile = Dir
    End Sub

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: Folder Files (XP)

    I still use the old DOS dir command for this sort of thing. If you open a command window (Start>Run..., cmd) and navigate to your folder, you can run something like this to get just the file names into a file:

    dir /b >filelist.txt

    Since dir has a lot of options, you might run dir /? to see whether you want to change the default sort order, etc., although it's easy to sort in Excel.

    From Excel, I believe there is a menu item to open a text file with a dialog that asks you about the encoding and any delimiters. I forget where that is, but that is how I would get the filelist.txt data into Excel.

    If you are looking for a code solution, search for posts on the FileSystemObject and you probably will find some which read all the file names. At least for Word... not sure about getting the names into cells in Excel.

Posting Permissions

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