Loop through all .xls files in subdirectories of a directory

[jackbergersen]

Hello All,

I am having a bit of trouble with a bit of code. I have a bunch of code that I want to run on all excel files that live one level down from my main directory.

I have a general directory (orders) which contains a variable amount of subfolders each month (in picture below, 'Dan', 'Frank', 'Steve'), and I would like to look through all the excel files in 'Dan', 'Frank', 'Steve', but not in the main directory (orders), or any subdirectories within 'Dan', 'Frank', 'Steve'.

I am able to loop through all the excel files in the subdirectories individually if I hardcode the directory names, but as they are variable, I would like to make my code accomodating.... Any thoughts? Thanks all!



~ Jack

 

[pboltonchina]

Try this

Sub LoopThroughFolder()
    Dim folderPath As String
    Dim filename As String
    Dim WB As Workbook
    
    folderPath = "C:\Orders\Dan\"
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xls")
    Do While filename <> ""
        Set WB = Workbooks.Open(folderPath & filename)
        
        'Call a subroutine here to operate on the just-opened workbook
        Call YourMacro(WB)
        
        WB.Close False
        filename = Dir
    Loop
    
End Sub

See if it does what you want in 'Dan' and the copy the code again for Frank and Steve.

 

[jackbergersen]

Thanks for the reply, pboltonchina.

I already have similiar code written that does pretty much the same thing, and I could repeat it for the other sub-directories. However, I want to make my code as dynamic as possible, and accomodate for changing folder names without having to hard code them.

Although I have been banging my head on this issue for a few hours now, so if I don't figure something in the next few days I think I will end up going the hardcoded route.


Thanks again!

 

[Norie]

Jack

This might seem a stupid suggestion/question, but why have all these subfolders?

It seems to me like a pretty awkward setup to deal with when trying to analysis/collate data.

 

[John_w]

Something like this:

'Requires reference to Microsoft Scripting Runtime

Option Explicit

Public Sub Process_Orders()
    
    Dim Fso As Scripting.FileSystemObject
    
    Set Fso = New Scripting.FileSystemObject
    Process_XLS_Files Fso, "C:\Orders"
    
    Set Fso = Nothing

End Sub


Private Sub Process_XLS_Files(Fso As Scripting.FileSystemObject, folderPath As String)
   
    Dim Folder As Scripting.Folder, Subfolder As Scripting.Folder, File As Scripting.File
    Dim wb As Workbook
    
    Set Folder = Fso.GetFolder(folderPath)
    
    For Each Subfolder In Folder.subfolders
        For Each File In Subfolder.Files
            If InStr(File.Name, ".xls") Then
                Debug.Print File.Path
                'Open File.Path workbook here and call your code on it
            End If
        Next
    Next

End Sub

 

[xenou]

The FileSystemObject can allow you to search the main folder (orders) and get the names of all the subfolders (so can DIR, for that matter). Once you create that list, you could execute code on the folders returned. What have you tried so far?

Edit: scratch that. See above post ...

 

[jackbergersen]

Hey Norie, thanks for the reply (and no it's not stupid- I initially brought up this same point when scoping the project).

Unfortunately it is a necessity from a business process standpoint, for a variety of reasons.

 

[xenou]

Nota bene:
the code from John requires a reference to the scripting runtime library (as noted) ...

in case you aren't familiar that means:
1) Open the visual basic editor
2) Goto Tools | References...
3) Find the checkbox for Microsoft Scripting Runtime and check it

Once you have successfully tested the code and everything is ready for deployment, you will be better of converting the code to "late binding". But while writing the code early binding is best.

 

[Norie]

I kind of understand the 'business scope' thing but does whoever said that things should/could be approached in this way actually lose money/time?

I don't know why but in the past when I've come across this sort of approach and pointed that out people seem to change their minds.

 

[jackbergersen]

Thanks All! I was able to use the code John posted to get this up & running! Thanks for your help!