Get value from named ranges in closed workbooks

[Trebormac]

Hi All,

I have searched this forum and the web for code to get the values from several named ranges in closed workbook and paste them into another workbook. I have not found what I am looking for. I have used the code below to get info from a couple hundred workbooks and paste it into another workbook. I found this code in this forum and have mofified it somewhat to fit my situation . It works well most of the time, however, the ranges in some workbooks are not the same. I would like to modify the code to select the data from named ranges rather the specific cell addresses. In other words from named range 'Net_Income' rather than "F79"

Any help would be greatly appreciated,

Best,

Trebormac

Sub ReadDataFromAllWorkbooksInFolder()
    Dim FolderName As String
    Dim wbName As String
    Dim r As Long
    Dim cValue As Variant
    Dim wbList() As String
    Dim wbCount As Integer
    Dim i As Integer
    Dim m As Integer
    Dim c As Integer
    'Dim DateMod As Variant
 
    FolderName = "C:\TestTemplate"
    ' create list of workbooks in foldername
    wbCount = 0
    wbName = Dir(FolderName & "\" & "*.xls")
    While wbName <> ""
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        wbList(wbCount) = wbName
        wbName = Dir
    Wend
 
    If wbCount = 0 Then Exit Sub
    ' get values from each workbook
    Range("A1").Value = "Entity"
    Range("B1").Value = "Month"
    Range("C1").Value = "Net Incom "
    Range("D1").Value = "Total Assets"
    Range("E1").Value = "Total Liabilities & Stockholders Equity"
    Range("F1").Value = ("Country")
    Range("G1").Value = "Assets Minus Liab & SE"
    Worksheets("Sheet1").Range("A1:G1").Font.Bold = True
 
 
    r = 1
 
 
    For i = 1 To wbCount
        r = r + 1
 
        cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Income Statement", "B2")
        'Cells(r, 1).Formula = wbList(i)
        Cells(r, 1).Formula = cValue
        cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Income Statement", "D1")
        Cells(r, 2).Formula = cValue
        cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Income Statement", "F79")
        Cells(r, 3).Formula = cValue
        cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Balance Sheet", "D98")
        Cells(r, 4).Formula = Round(cValue, 0)
        cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Balance Sheet", "D225")
        Cells(r, 5).Formula = Round(cValue, 0)
        Cells(r, 6).Value = Left(Cells(r, 1), 5)
        Cells(r, 7).Value = Cells(r, 4).Value - Cells(r, 5)
        If Cells(r, 7).Value <> 0 Then
            Cells(r, 7).Interior.ColorIndex = 7
        End If
 
    Next i
    Worksheets("Sheet1").Range("C:E").NumberFormat = "#,##0_);[Red](#,##0)"
    Worksheets("Sheet1").Range("A1:F1").Columns.AutoFit
    Worksheets("Sheet1").Range("A9").Columns.AutoFit
 
 
End Sub
Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
    wbName As String, wsName As String, cellRef As String) As Variant
 
    Dim arg As String
 
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & _
        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function