GetInfoFromClosedFile problem while looking in Subfolders

Waidat

New Member
Joined
May 15, 2013
Messages
1
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:

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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,136,969
Messages
5,678,893
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top