Recursive search in nested folders/subfolders and then open file

[jakestyeen7059]

Hello,

I have been searching for a solution to a problem for quite some times now, and I was hoping one of you have a great idea in this forum.

I first like to appreciate any help and any reply to my problem.

My problem is, I have an excel macro that does multiple thing for a report purpose. Everything worked fine but I had to update the macro to prepare for the new year. My code will go through each Dir and find a specific file named "Master.xlsx" and save this as other file name with report in this file. Master file is just a template to be used.

The problem is Master file can be located anywhere in the subfolders under the main directory. Without further ado, here is the partial code I have. After many weeks of searching, this seemed the right code. It came from --> http://www.mrexcel.com/forum/excel-...r-visual-basic-applications-excel-2010-a.html

    If Len(Dir(strDefpath & strBBC & strDirYear, vbDirectory)) = 0 Then
        MkDir (strDefpath & strBBC & strDirYear)
    End If

    If Len(Dir(strDefpath & strBBC & strDirYear & strDirType, vbDirectory)) = 0 Then
        MkDir (strDefpath & strBBC & strDirYear & strDirType)
    End If
    
    If Dir(strDefpath & strBBC & strDirYear & strDirType & "\" & strFilename & ".xlsx") = "" Then
        For Each fdr(1) In CreateObject("Scripting.FileSystemObject").GetFolder(strDefpath).SubFolders
                On Error Resume Next
            For Each target(1) In fdr(1).Files
                If InStr(1, UCase(target(1)), UCase(".xlsx")) > 0 Then
                    If target(1).DateLastModified > dteFile(1) Then
                        If UCase(target(1).Name) = "MASTER.XLSX" Then
                            dteFile(1) = target(1).DateLastModified
                            strFile(1) = target(1)
                            Debug.Print strFile(1)
                        End If
                    End If
                End If
            Next target(1)
        Next fdr(1)
 
        For Each fdr(2) In CreateObject("Scripting.FileSystemObject").GetFolder(strDefpath & strBBC).SubFolders
                On Error Resume Next
            For Each target(2) In fdr(2).Files
                If InStr(1, UCase(target(2)), UCase(".xlsx")) > 0 Then
                    If target(2).DateLastModified > dteFile(2) Then
                        If UCase(target(2).Name) = "MASTER.XLSX" Then
                            dteFile(2) = target(2).DateLastModified
                            strFile(2) = target(2)
                        End If
                    End If
                End If
            Next target(2)
        Next fdr(2)
    
        If dteFile(1) > dteFile(2) Then
            If Len(Dir(strFile(1))) Then
                Debug.Print strFile(1)
            End If
        ElseIf dteFile(2) > dteFile(1) Then
            If Len(Dir(strFile(2))) Then
                Debug.Print strFile(2)
            End If
        ElseIf dteFile(1) = dteFile(2) Then
            If Len(Dir(strFile(1))) Then
                Debug.Print strFile(1)
            ElseIf Len(Dir(strFile(2))) Then
                Debug.Print strFile(2)
            End If
        End If
        
        ActiveWorkbook.SaveAs strDefpath & strBBC & strDirYear & strDirType & "\" & strFilename & ".xlsx", FileFormat:=51
    End If
    
    If Not IsWorkBookOpen(strDefpath & strBBC & strDirYear & strDirType & "\" & strFilename & ".xlsx") Then
        Workbooks.Open (strDefpath & strBBC & strDirYear & strDirType & "\" & strFilename & ".xlsx")
    Else
        Workbooks(strFilename & ".xlsx").Activate
    End If

All declaration is as followings

Dim strFilename As String, strDefpath As String, strDirYear As String, strDirType As String, strPathname As String, strBBC As String
Dim wbMaster As Workbook
Dim fdr(2) As Folder, target(2) As File, dteFile(2) As Date, strFile(2) As String

The problem with this is, under strDefpath & strBBC, there could be many more nested subfolders. I thought the above code will be able to recursively search through each and every one of the folder but as I keep adding debug.print (originally, it was workbook.open command there but just for test purpose, it is replaced with debug), I found out it will search the subfolder, but does not go into the subfolder. It just reads subfolder as another object.

Please note that IsWorkbookOpen is function taken from somewhere long time ago and that works. Also please note that there could be more than one, max of 2 Master.xlsx thus I needed to open it by comparing the modified time/date.

Any help is appreciated!

Yours truly,
Jake

 

[John_w]

Your code isn't recursive, and it seems to look in only the first level of subfolders. A recursive FileSystemObject procedure would loop through all file names in the folder path passed to it and then call itself for each subfolder in that folder. http://www.mrexcel.com/forum/excel-...folders-my-folders-file-loop.html#post2087158 shows an example of a recursive file and subfolders search using FSO, which you'll need to modify for your specific needs.

With a maximum of 2 Master.xlsx files, you haven't said which one should be opened by reference to the modification date time, i.e. the newest or oldest file.

 

[jakestyeen7059]

John_W

Thank you so much for your response and I apologize for the delay; Black Friday got me there...

Anyhow, to answer your question, to be opened is the newest one; the code where dte(1 & 2) is referenced, bottom portion of the code, should be doing that job (I hope, that is).

I think I understand what you are saying, so in this case, it would mean the parent code(function) has to be called after

For Each target(1) In fdr(1).Files

and go into each subfolder when file is subfolder so that target will loop into its subfolder?

I was wondering if you knew of a way to do this without creating function. I have the basic coding here so I wonder if this could be done with just some tweaking. If not, that is totally fine; I just appreciate your help in shedding some light and I think I have an idea! I will post finished code if I get there.

Thank you,
Jake

 

[John_w]

I think I understand what you are saying, so in this case, it would mean the parent code(function) has to be called after

For Each target(1) In fdr(1).Files

and go into each subfolder when file is subfolder so that target will loop into its subfolder?

Yes, but after the Next statement which marks the end of that loop, it would loop through the subfolders in the current folder by calling itself. If you look at the code I linked to, it loops through the files in the current folder and then the subfolders in the current folder, calling itself for each subfolder.

I was wondering if you knew of a way to do this without creating function. I have the basic coding here so I wonder if this could be done with just some tweaking.

The recursive file/folder searching needs to be in a separate function so that it can call itself. Look at my code to get the basic logical structure. In fact, you don't need to loop through the files in the current folder to see if Master.xlsx exists, but instead use the FileExists Method on the current folder path.

 

[Jerry Sullivan]

Hi all,

I added a post to the thread Jake referenced in the OP that you might find useful...
http://www.mrexcel.com/forum/excel-...ic-applications-excel-2010-a.html#post3648977

 

[jakestyeen7059]

John, Jerry,

I appreciate your help and sorry for the very late response every time.

Jerry - unfortunately, I am not too familiar with cmd so I rather not go into it considering this is work related and I do unfortunately have admin access to most of program and folders.

John - I have been trying around but FileExists method would not benefit me as I do have many subfolders but not many files are in the folder so looping won't take long.

I understood the logic behind the recursive search but your way mentioned in the referred post won't be able to compare the modified date/time and open just the one I need as far as my knowledge goes. I tried expanding the array but that wasn't allowed either. I was going to compare it after both are open but that wasn't allowed too for some reason.

Thank you so much for your help and I appreciate any thoughts.

Jake

 

[jakestyeen7059]

John, Jerry,

Thank you two for your help.

I should have updated this post awhile ago when I finish the project.

To anyone may be interested. I accomplished this by using two different functions. One function integrated the recursive search while also looking at lastaccess by MS scriptRT. During the search, I made two array to store full file directory and the other one for the lastaccess date/time. While searching, I made it so it compares the array before entering new data into array then if lastaccess is later(newer), I replaced the same array with new data, if not, don't record. If two file had exact same access time (as it is shared drive), I redim preserve then added another record.

Another function is probably unnecessary for most occasion but for my convenience, it recursively searched within arrays (and I used same array for other function) after determining if the array stores more than one data.

Thank you,
Jake