0

I would like to loop through folder that contains multiple subfolders to find excel files that contain specified name, and do stuff with those found excel files. Any suggestions how can I achieve that?

I have found something like this but it's not working:

Application.ScreenUpdating = False
 Application.DisplayAlerts = False

 With Application.FileSearch
 .NewSearch
 .LookIn = "C:\temp 1" ' your drive / directory here
 .SearchSubFolders = True
 .FileName = ".xls" ' all files ending in xls
 .FileType = msoFileTypeExcelWorkbooks
 If .Execute() > 0 Then
 ' how many files are there in the selected folder?
 MsgBox "There were " & .FoundFiles.Count & " file(s) found."

 For i = 1 To .FoundFiles.Count
 Workbooks.Open .FoundFiles(i), 0
 '
 ' code 
 '

 '
 ActiveWorkbook.Save
 ActiveWorkbook.Close
 Next i
 Else
 MsgBox "There were no files found."
 End If
 End With


 Application.ScreenUpdating = True
 Application.DisplayAlerts = True

 MsgBox "All Done!"
Community
  • 1
  • 1
Pawel
  • 417
  • 1
  • 6
  • 25

3 Answers3

1

So for just the files in the directory specified:

https://msdn.microsoft.com/en-us/library/dk008ty4(v=vs.90).aspx

Dim MyFile, MyPath, MyName As String 
' Returns "WIN.INI" if it exists.
MyFile = Dir("C:\WINDOWS\WIN.INI")   

' Returns filename with specified extension. If more than one *.INI 
' file exists, the first file found is returned.
MyFile = Dir("C:\WINDOWS\*.INI")

' Call Dir again without arguments to return the next *.INI file in the 
' same directory.
MyFile = Dir()

' Return first *.TXT file, including files with a set hidden attribute.
MyFile = Dir("*.TXT", vbHidden)

' Display the names in C:\ that represent directories.
MyPath = "c:\"   ' Set the path.
MyName = Dir(MyPath, vbDirectory)   ' Retrieve the first entry.
Do While MyName <> ""   ' Start the loop.
      ' Use bitwise comparison to make sure MyName is a directory. 
      If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then 
         ' Display entry only if it's a directory.
         MsgBox(MyName)
      End If   
   MyName = Dir()   ' Get next entry.
Loop

Or for all sub folders too, take a look here:

Loop Through All Subfolders Using VBA

Dim FileSystem As Object
Dim HostFolder As String

HostFolder = "C:\"

Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)

Sub DoFolder(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        ' Operate on each file
    Next
End Sub
Community
  • 1
  • 1
Preston
  • 7,399
  • 8
  • 54
  • 84
  • Yup, I saw that thread while looking for an answer. I'm not quite sure what should I add to "operate on each file" ( only excel file) ? – Pawel Oct 12 '16 at 09:24
  • and what about the second case? – Pawel Oct 12 '16 at 09:47
  • File is the variable you need here, so workbook.open file – Preston Oct 12 '16 at 09:49
  • n the first case, variable myName will contain something like 'CostSheet2016.xlsx', so you can use workbook.open myName to open it and go from there i guess if that's what you need? – Preston Oct 12 '16 at 09:50
  • OK, Thanks so much. Could you please take a look on the code I've just posted in my original question, why i'ts not working? – Pawel Oct 12 '16 at 09:52
1

the best way I know is to use a batch:

dir /b "D:\mondossier\" >"C:\tempfolder\TEMP.txt"

then read it as text : 

CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\tempfolder\TEMP.txt")

way faster than juste FSO, unless mistaken

Pierre
  • 1,046
  • 7
  • 21
-2
void DirSearch(string sDir) 
{
    try 
    {
       foreach (string d in Directory.GetDirectories(sDir)) 
       {
        foreach (string f in Directory.GetFiles(d, txtFile.Text)) 
        {
           if(f.EndsWith("MySearchPattern.xlsx"))
           lstFilesFound.Items.Add(f);
        }
        DirSearch(d);
       }
    }
    catch (System.Exception excpt) 
    {
        Console.WriteLine(excpt.Message);
    }
}

See: de: https://support.microsoft.com/de-de/kb/303974

en: https://support.microsoft.com/en-us/kb/303974

TripleEEE
  • 499
  • 3
  • 11