Results 1 to 4 of 4
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    List of Files in SubFolders (VBA/Excel/XP)

    My problem is to get a list of folders, subfolders and filenames.

    I have many, many subfolders. First I want to obtain the list of files and their subfolder paths to a spreadsheet. Later I will be writing something that opens them based on naming convention criteria to make changes.

    I am programming in XP but my customers could be in 97, XP, or 2003.
    Alan

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List of Files in SubFolders (VBA/Excel/XP)

    You can write a little script (VB or J or use COM objects from VBA) that uses recursion to get what you want.
    Attached is my version of same. (rename .txt to .vbs)
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List of Files in SubFolders (VBA/Excel/XP)

    You could use this (in a normal module):

    Public lCount As Long
    Public sFilelist() As String

    Sub Example()
    dim lLocalCount as Long
    FindFiles("C:WINDOWS","*.xls")
    MsgBox "Found: " & lCount & " files"
    For lLocalCount=1 to lCount
    msgbox sFiles(lLocalcount)
    Next
    End Sub

    Sub FindFiles(sLocalPath As String, sSpec As String)
    Dim lLocalCount As Long
    Application.StatusBar = "Scanning " & sLocalPath & " for " & sSpec
    With Application.FileSearch
    .NewSearch
    .LookIn = sLocalPath
    .SearchSubFolders = True
    .Filename = sSpec
    .MatchTextExactly = False
    .FileType = msoFileTypeAllFiles
    If .Execute() > 0 Then
    For lLocalCount = 1 To .FoundFiles.Count
    ReDim Preserve sFilelist(lCount + 1)
    lCount = lCount + 1
    sFilelist(lCount) = .FoundFiles(lLocalCount)
    Next
    End If
    End With
    Application.StatusBar = False
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: List of Files in SubFolders (VBA/Excel/XP)

    Turned out that I already had a cool piece of code. It had a fatal error (I killed the path stream in an intermediate step).

    Thanks to everyone that tried. BTW: I could not get my user to turn on Scripting; against company policy.
    Alan

Posting Permissions

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