GetInfoFromClosedFile problem while looking in Subfolders

[Waidat]

Hello all and thank you in advance for any help you can give.

My overall goal is to have a MasterLog worksheet that contains summary data from individual files. There will regularly be subfolders added and individual excel files within those subfolders. The individual files are all formatted the same.

I am using the GetInfoFromClosedFile function that I have found in various places and adapted it some to fit my needs. It works well when the file is in the folder with an individual workbook, but not for subfolders. It still finds the workbooks inthe subfolders, but the cell values are "#REF!". Is there a way to fix this?

I had adapted some code previously to silently open and close the files but it took a long time on these slow computers even when just looking at a couple of individual files.

Thanks again for any help. Here is the code:

Option Explicit
Dim wbList() As String
Dim wbCount As Long

Sub ReadDataFromAllWorkbooksInFolder()
    Dim FolderName As String
    Dim cValue As Variant, bValue As Variant, aValue As Variant
    Dim dValue As Variant, eValue As Variant, fValue As Variant
    Dim i As Long, r As Long
    Dim sheet As String
    
    FolderName = ThisWorkbook.Path & "\Individual Files\"
    ProcessFiles FolderName, "*.xl*"
    sheet = "Sheet1"
    If wbCount = 0 Then Exit Sub
    r = 3
    For i = 1 To UBound(wbList)
        
        r = r + 1
        cValue = GetInfoFromClosedFile(wbList(i), sheet, "c5")
        bValue = GetInfoFromClosedFile(wbList(i), sheet, "e14")
        aValue = GetInfoFromClosedFile(wbList(i), sheet, "h14")
        dValue = GetInfoFromClosedFile(wbList(i), sheet, "g5")
        eValue = GetInfoFromClosedFile(wbList(i), sheet, "i13")
        fValue = GetInfoFromClosedFile(wbList(i), sheet, "g10")
        Cells(r, 1).Value = cValue
        Cells(r, 2).Value = bValue
        Cells(r, 3).Value = aValue
        Cells(r, 4).Value = dValue
        Cells(r, 6).Value = eValue
        Cells(r, 5).Value = fValue
        Cells(r, 7).Value = wbList(i)
     Next i
End Sub

'~~> This function was taken from
'~~> [URL="http://www.vbaexpress.com/kb/getarticle.php?kb_id=245"]VBA Express : Multiple Apps - Loop through all/selected files in a folder and its subfolders[/URL]
Sub ProcessFiles(strFolder As String, strFilePattern As String)
    Dim strFileName As String, strFolders() As String
    Dim i As Long, iFolderCount As Long
    '~~> Collect child folders
    strFileName = Dir$(strFolder & "\", vbDirectory)
    Do Until strFileName = ""
        If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
            If Left$(strFileName, 1) <> "." Then
                ReDim Preserve strFolders(iFolderCount)
                strFolders(iFolderCount) = strFolder & "\" & strFileName
                iFolderCount = iFolderCount + 1
            End If
        End If
        strFileName = Dir$()
    Loop
    '~~> process files in current folder
    strFileName = Dir$(strFolder & "\" & strFilePattern)
    Do Until strFileName = ""
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        wbList(wbCount) = strFolder & "\" & strFileName
        strFileName = Dir$()
    Loop
    '~~> Look through child folders
    For i = 0 To iFolderCount - 1
        ProcessFiles strFolders(i), strFilePattern
    Next i
End Sub

Private Function GetInfoFromClosedFile(ByVal wbFile As String, _
wsName As String, cellRef As String) As Variant
    Dim arg As String, wbPath As String, wbName As String
    GetInfoFromClosedFile = ""
    wbName = FunctionGetFileName(wbFile)
    wbPath = Replace(wbFile, "\" & wbName, "")
    arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
    
End Function
'~~> Function to get file name from the full path
'~~> Taken from [URL="http://www.ozgrid.com/VBA/GetExcelFileNameFromPath.htm"]Parse/Extract File Name from Full File Name and Path[/URL]
Function FunctionGetFileName(FullPath As String)
    Dim StrFind As String
    Dim i As Long
    Do Until Left(StrFind, 1) = "\"
        i = i + 1
        StrFind = Right(FullPath, i)
        If i = Len(FullPath) Then Exit Do
    Loop
    FunctionGetFileName = Right(StrFind, Len(StrFind) - 1)
End Function