Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File search on sub directories (Excel 2000)

    I have a situation that a file with the same name (abc.xls) is saved under 12 subdirectories. The subdirectories is per month. I need to combine all the abc.xls file into 1 summary file. What code searches a subdirectory and the opens each instance of the file one by one.

    I also would like to report the complete directory where the abc.xls was found in order to subtotal the summary per month.

    Thanks

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

    Re: File search on sub directories (Excel 2000)

    You can use the FileSearch object for this. Here is some example code:

    Dim i As Integer
    With Application.FileSearch
    .NewSearch
    .LookIn = "C:Year2002"
    .SearchSubFolders = True
    .FileName = "abc.xls"
    .MatchTextExactly = True
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
    For i = 1 To .FoundFiles.Count
    ' Code to process file goes here, for instance:
    MsgBox .FoundFiles(i)
    Next i
    End If
    End With

    In the lookIn argument, substitute the folder you want to start searching in.
    In the FileName argument, substitute the actual name of your workbook.
    The FoundFiles array will contain the complete names of all files found, including the full path.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File search on sub directories (Excel 2000)

    It works great

    Thanks Hans

Posting Permissions

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