"For Each" looping through Windows Explorer

[Ninjagecko]

Hey,

I'm trying to automate a data processing algorithm that involves searching through folders in Windows Explorer. For archiving purposes, the data files are stored as

Sample folder\date\time\_____.xls

For each sample, there are multiple date folders, each containing multiple time folders. The files at the end of the tree all have the same names. Opening the files in a SINGLE folder is easy enough - what I am hoping to do is, for a given sample, have excel seek out every date/time combination and crunch the files. Conceptually, I'm thinking something like

For Each folder in Sample Folder
For Each folder in date
(load files and run macro)
Next
Next

Is this feasible in VBA? Thanks!

 

[Andrew Fergus]

Hi and welcome to MrExcel

The following code does most of what you ask. Given a starting folder it will search for all files with an 'xls' suffix, including searching in all subfolders. Once you have loaded and amended the code, run the macro called 'StartUp'.

Option Explicit
 
'Set a reference as follows:
'select VBA menu option Tools > References > find and tick 'Microsoft Scripting Runtime'
 
Dim i As Long
 
Sub StartUp()
 
Const MyStartFolder As String = "S:\WORKAREA\Andrew"
 
  i = 1
  GetFiles (MyStartFolder)
  MsgBox "Finished"
 
End Sub
 
 
Private Sub GetFiles(Location As String)
 
Dim fso As New FileSystemObject
Dim fsoFolder As Folder
Dim fsoFile As File
 
'Search this folder for Excel files
For Each fsoFile In fso.GetFolder(Location).Files
    If LCase(Right$(fsoFile.Name, 3)) = "xls" Then
        'Insert your own code in this section
        Sheets(1).Cells(i, 1).Value = fsoFile.Name
        Sheets(1).Cells(i, 2).Value = fsoFile.Path
        i = i + 1
    End If
Next
 
'Search this folder for more folders
For Each fsoFolder In fso.GetFolder(Location).SubFolders
    Call GetFiles(fsoFolder.Path)
Next
 
End Sub

Important
You will need to set the starting folder manually on this line:

Const MyStartFolder As String = "S:\WORKAREA\Andrew"

You also need to set a reference within VBA as stated in the code, and you will need to change the two lines after this line to do whatever it is you want to do:

'Insert your own code in this section

Andrew

 

[Ninjagecko]

Thanks this is exactly the start I needed. What exactly is the function of a FileSystemObject, and specifically why would you ever need multiple?

 

[Andrew Fergus]

You're welcome. The file system object is an API that allows you to access files, folders, drives and their properties. The following webpage explains it better than I can:
http://www.techbookreport.com/tutorials/fso1.html

It is a handy tool for doing exactly the sorts of things you described. You can also use it to copy and delete files, access file, folder & drive properties etc etc. It doesn't do everything but often it is enough to get started.

Sorry - I'm not sure what you mean by 'why would you ever need multiple?'.

Andrew