VBA - How to refer a closed workbook named range

Question

I'm trying to do a conditional count "COUNTIF" from closed workbook named range. Like the example below, count the amount of "fruit_count" if the "fruit_names" is "apples":

=SUMIF('C:\folder\test.xlsm'!fruit_names;"apples";'C:\folder\test.xlsm'!fruit_count)

I've found the getvalue() formula but it looks like doesn't work on a named ranges:

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function  

The perfect would be to create a function that permits to be executed on the the excel ribbon formula bar but the "ExecuteExcel4Macro" doesn't allow that, or a way to combine the getvalue function with named ranges...

I saw many issues and questions about this topic but not many answers with named ranges.

Thanks in advance, av9

Answer 1

That's because you don't reference a sheet when you reference a named range, the Name of the range should be enough. To do that your arg variable should look something like 'C:\folder\thisworkbook.xlsx'!this_range or 'C:\folder\[Someotherfile.xlsx]'!that_range for named ranges to work.

Now you are feeding ExecuteExcel4Macro() with a wrong string, that's why this function breaks.

This

GetValue(path, file, sheet, ref)

Should be this

GetValue(path, file, named_range)

And this

arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

to this

arg = "'" & path & "[" & file & "]" & "'!" & named_range
'or
arg = "'" & path & "\" & file & "'!" & named_range

This way it will return you range. I've tested it.

Cheers.