You don't need to open Excel to read a named range. The following function will put the named range in an array, and you can loop through the array to find your value
Code:
Option Explicit
Const strWorkbook As String = "C:\Path\WorkbookName.xlsx" 'The path of the workbook
Const strRange As String = "RangeName"
Private Function xlFillArray(strWorkbook As String, _
strRangeName As String) As Variant
Dim RS As Object
Dim CN As Object
Dim iRows As Long
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strRangeName & "]", CN, 2, 1
With RS
.MoveLast
iRows = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(iRows)
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function
For example, using the data file from my Chart Merge add-in, this has a range called "MergeData"
You could then look for a name in the first column of the array, and report (say) the value associated with the name in the second column e.g. as follows. You would of course have to set the values of the workbook and the range name in the declarations above
Code:
Sub Test()
'Look for the value 'Al Gore' in the first column of the array
'And report the value in the second column
'Which here should report - 14132
Dim arr() As Variant
arr = xlFillArray(strWorkbook, strRange)
On Error GoTo lbl_Exit
For iRows = 0 To UBound(arr, 1)
sResult = arr(0, iRows)
If sResult = "Al Gore" Then
MsgBox arr(1, iRows)
End If
Next iRows
lbl_Exit:
Exit Sub
End Sub