Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Ontario, Canada
    Posts
    94
    Thanks
    0
    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
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
    Cells.Clear
    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
    Loop
    End Sub

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 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
  •