Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Folder Contents (03)

    I have seen code (posted in the Lounge) that will list a folder's content. Is it possible to list the entire folder structure where the folder has sub-folders. This would be similiar to the DOS command for: Dir *.* /S

    Thanks,
    JOhn

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Folder Contents (03)

    Why do you ask this in the Excel forum? It has nothing to do with Excel.

    You could issue

    Dir *.* /s > List.txt

    in a command prompt window. This will create a text file List.txt with the folder structure.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Folder Contents (03)

    Hans,

    I am trying to utilize Excel's vba to produce a clean list of folders and their contents. The DOS command does provide a flat file which I would have to manipulate in Excel.

    I figured the code could produce a nice columnar format such as: Path, FolderName, FileName

    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Folder Contents (03)

    Not quite what you asked, but here is a primitive macro. I'll leave it to you to refine it.

    Sub ListFiles()
    Dim i As Integer
    Dim j As Integer
    Dim strFile As String
    Application.ScreenUpdating = False
    Cells.ClearContents
    With Application.FileSearch
    .NewSearch
    ' Substitute correct path
    .LookIn = "C:Excel"
    .SearchSubFolders = True
    .FileType = msoFileTypeAllFiles
    .Execute
    For i = 1 To .FoundFiles.Count
    strFile = .FoundFiles(i)
    j = InStrRev(strFile, "")
    Range("A" & i) = Left(strFile, j - 1)
    Range("B" & i) = Mid(strFile, j + 1)
    Columns("A:B").AutoFit
    Next i
    End With
    Application.ScreenUpdating = True
    End Sub

    Warning 1: the code will clear the active worksheet!
    Warning 2: don't try to process your entire hard disk. Excel can't handle that many files.

Posting Permissions

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