Select files from multiple folders that are nested 3 levels

[lhara2010]

Hi all,
I need to write a macro that can open up multiple excel sheets in multiple folders, perform a series of calculation and close those files. If it can perform the calculation without opening the book (to the user) that would be even better.

The excel files are located within two subfolders. The root folder could contain as many as 15 sub folders, and within ea sub folder would be two more folders, and within ea of those two folders would be one and then the excel file. (sorry this is difficult to write in words )

For instance:
.....\Root Folder\Sub folder 1\sub sub folder 1\folder\*.xls
.....\Root Folder\Sub folder 1\sub sub folder 2\folder\*.xls

.....\Root Folder\Sub folder 2\sub sub folder 1\folder\*.xls
.....\Root Folder\Sub folder 2\sub sub folder 2\folder\*.xls

....
....
....
and so on.

So far I have a macro that can allow me to pick a specific file, that will perform a series of calculation based on a recorded macro. But my macro only allows me to pick one file from the folder.

I would like to be able to include the above functionality to batch process a lot of data. I have ~80 excel files in each of the folder (last level) listed above, and my program currently only does calculation on one of those excel files.

I would greatly appreciate any help you experts can give me. I have dug through many forums and found snippets that does things close enough, but not to this level of flexibility. . I am a noobie in VBA and so the help portion is not very helpful.

Thanks in advance...

 

[Weaver]

Welcome to the board.

Something like this ought to get you started

Sub processAllFilesInFolder()
    fldr = "c:\drawer\xl\"
    d = Dir(fldr & "*.xls")
    Application.ScreenUpdating = False
    Do While d <> ""
        Set wb = Workbooks.Open(fldr & d)
        With wb
            'do all your calculations here
            .Close savechanges:=True
        End With
        d = Dir
    Loop
End Sub

It loops through a given folder, finding any xl spreadsheets. It then opens them, at which point you'd perform your actions, then closes the book, saving any changes. The user would not see any of this onscreen, due to the

Application.ScreenUpdating = False

line, although if your toolbar is visible, you can see sheets are being opened and closed.

You could code it as a generic subroutine by changing

Sub processAllFilesInFolder()
    fldr = "c:\drawer\xl\"

for

Sub processAllFilesInFolder(fldr)

and call it with the required folder as an argument

HTH

 

[wigi]

And here is logic to run through all folders and subfolders and also to check the level deep:

Sub test()

    Call MAINroutine("C:\MrExcel")

End Sub
Private Sub MAINroutine(sSourceFolder As String)

    Set fldr = CreateObject("scripting.filesystemobject").Getfolder(sSourceFolder)
    
    For Each fl In fldr.Files
        Call DoOneFile(fl.Path)
    Next

    For Each SubFolder In fldr.SubFolders
        MAINroutine SubFolder.Path
    Next

End Sub

Private Sub DoOneFile(sFullFileName As String)

    If UBound(Split(sFullFileName, "\")) = 3 Then
    
        Debug.Print sFullFileName
    
    End If

End Sub

Execute the procedure test after having changed the root folder. Look in the Immediate Window for the output (Ctrl-G). Lastly, the 3 in the procedure "DoOneFile" is very important, that is the levels deep you want to check. Play around with it.

Wigi

 

[Ruddles]

The first thing to tackle is that the normal GetOpenFilename only allows you to do this from a single folder. My solution would be to prompt the user to select files from a single folder at a time, then loop round prompting him to select additional files from other folders, storing their names in an array until he hits 'Cancel'.

The second step will be to open the files whose names are in the array - but that can wait. Let's get the first part working first.

Create a new standard code module and paste the following code into it:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub MultiSelectFiles()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim sFileArray As Variant
  Dim sFileName As Variant
  Dim sFilesToProcess() As String
  Dim iPtr As Integer
  
  ReDim sFilesToProcess(0)
  
  ChDrive Left(ThisWorkbook.Path, 2)
  ChDir Mid(ThisWorkbook.Path, 3)
  sFileArray = Array("")
  Do Until Not IsArray(sFileArray)
    sFileArray = Application.GetOpenFilename(FileFilter:="All file types (*.*), *.*", MultiSelect:=True)
    If IsArray(sFileArray) Then
      For Each sFileName In sFileArray
        ReDim Preserve sFilesToProcess(UBound(sFilesToProcess) + 1)
        sFilesToProcess(UBound(sFilesToProcess)) = sFileName
      Next sFileName
      iPtr = InStrRev(sFileArray(1), "\")
      ChDrive Left(sFileArray(1), 2)
      ChDir Left(sFileArray(1), iPtr)
    End If
  Loop
  
[COLOR=green]  ' the names of all the selected files are now in sFilesToProcess
  
  ' loop round processing them - this is some sample code for testing
[/COLOR][COLOR=blue]  Debug.Print CStr(UBound(sFilesToProcess)); " files selected:-"
  For iPtr = 1 To UBound(sFilesToProcess)
    Debug.Print sFilesToProcess(iPtr)
  Next iPtr[/COLOR][/FONT]
[FONT=Fixedsys]     
End Sub
[/FONT]

Run the code. A file dialog box will open: navigate to a folder and select one or more files, then click 'Open'. The file dialog box will continue to re-open so that you can navigate to different folders and select more files. When you've selected enough, click 'Cancel'. Finally open the Immediate window (Ctrl-G) and check that all your files are listed.

When you've got that working to your satisfaction, you can replace the testing code (in blue) with some code to process the actual workbooks.

 

[wigi]

I know that several helpers are working next to each other, but with the code below gives you all files in the folders that are 3 levels deep in a specified root folders (change on top of the code):

Const sRootFolder As String = "C:\MrExcel\"

Sub test()

    Call MAINroutine(sRootFolder)

End Sub

Private Sub MAINroutine(sSourceFolder As String)

    Set fldr = CreateObject("scripting.filesystemobject").Getfolder(sSourceFolder)
    
    For Each fl In fldr.Files
        Call DoOneFile(fl.Path)
    Next

    For Each SubFolder In fldr.SubFolders
        MAINroutine SubFolder.Path
    Next

End Sub

Private Sub DoOneFile(sFullFileName As String)

    If LevelsDeep(sRootFolder, sFullFileName) = 3 Then
    
        Debug.Print sFullFileName
    
    End If

End Sub

Function LevelsDeep(sHigherFolder As String, sLowerFolder As String) As Integer

    If InStr(1, sLowerFolder, sHigherFolder, vbTextCompare) = 1 Then
    
        LevelsDeep = UBound(Split(Right(sLowerFolder, Len(sLowerFolder) - Len(sHigherFolder)), "\"))
        
    Else
    
        LevelsDeep = 0
    
    End If

End Function

Again, look in the Immediate Window for the output (Ctrl-G).

This just leaves you with processing 1 file, to which you have the full path. Change the procedure "DoOneFile" and you're done.

Wigi

 

[Ruddles]

I know that several helpers are working next to each other...

It's good to demonstrate the different approaches and maybe also the slightly different interpretation of what the OP (believes he) requires.

 

[lhara2010]

Hi All,

Thanks for your prompt and great responses. I was able to verify both wigi's and weaver's codes.
Wigi, when I tried your code on a new workbook, it worked ok. However when I pasted the code in my original work book, under a command button, I get an error.
"Compile Error. ByRef argument type mismatch"

The debugger highlights "sRootFolder". In your code the sRootFolder is the constant that is defined outside of the sub routines, however when I paste your code, that particular line of code automatically copies inside of the commandbutton_click.

FYI, I only use the command button to call the subroutine MAINroutine. Here is the sample of the code ...

Private Sub CommandButton3_Click()
Const sRootFolder As String = "C:\Users\........"
    Call MAINroutine(sRootFolder)
End Sub

Weaver, your code works great once I go down the folder structure and have only the excel files to deal with. For my own understanding could you explain why you use

d = Dir

Ruddles, thanks for your reply. I have way too many files at this point to go in an manually pick. I think your solution would be great for select files from each folder.

 

[Ruddles]

Ruddles, thanks for your reply. I have way too many files at this point to go in an manually pick. I think your solution would be great for select files from each folder.

Thanks for the feedback - it's appreciated. I'm sure the other replies will lead to a very satisfactory solution for you.

 

[wigi]

Yoiu should only have:

Private Sub CommandButton3_Click()
    Call test
End Sub

The rest of the code goes into a normal module, as always.

Wigi

 

[lhara2010]

wigi,
Perhaps I am too much of a newbie, but I still cant figure out how to do my operations on all the xls files that get listed.
Running your code with the immediate window open shows a list of every file with its full path. How do I run the macro for each excel file?
Do I need to open each workbook and then do my macro and then close it again?

To summarize, here are my questions:
1. Running the program now lists all the files in the folder structure. I have both excel and another file format. I only care about the excel files. Separately I have a macro to perform an operation on each of those workbooks, how do I call that macro to perform this on every work book that is listed by sFullFilename?

2. I tried referencing the first workbook as sFullFilename(1),but was told this is not an array . So if this is just a string, how do I open each of these workbooks? or do I even need to do that?

Thanks for your time and effort in helping me with this problem.