Excel UDF: retrieving value from arbitrary, closed, external workbook

Question

I am looking for a way to return the value from an arbitrary workbook (the workbook will also not be open at the time of running the UDF), defined based on calculations in the UDF.

Pseudo code:

Start by calling =someFunc(currentCell) in any cell

Function someFunc(adr As Range)
region_eval = "C" & Range(adr).Row ' where column C contains string entries, all of which have a corresponding sub-dir (see fileReference).
networkLocation = ActiveWorkbook.Path
networkPath = networkLocation & "\Locations\"
fileReference = networkPath & region_eval & "\ProductList.xlsx"

Workbook.Open fileReference readonly
    Perform index/match call against some sheet in this workbook
someFunc = returned value
Close workbook and end function

This is the desired behavior.

The logic to return the desired values is OK, I have tried it in a simpler formula, and in a UDF that relies on the file being opened manually:

INDEX(locationlist_$A$5000, MATCH(masterlist_A1, locationlist_$B$5000))  

I have, after hours of hair-pulling, discovered that this functionality is not directly available in a UDF designed to work on workbooks that aren't opened manually, and that this is intended from Microsoft's side. But I have also discovered that there is a possible workaround!

Ref:
1. https://stackoverflow.com/a/27844592/4604845
2. http://numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

These solutions require hardcoded file paths, which defeats the purpose for my intended usage.

Is there anyone who has insight about how to achieve what is achieved in any of the two above links, but with an arbitrary filepath (as in, contained in a cell neighbouring the cell where the UDF is being called from)?

Note: I tried doing the heavy lifting in a sub, and just call the sub as the first line in the UDF, set the result as a global var, and set the UDF return value to the same var after the sub finished, but either I crashed and burned pretty heavily or Excel saw through my trick and denied it.

EDIT:

Here's the sub/func combo.

Option Explicit

Public networkLocation As String, networkPath As String, fileReference As String, c_formula As String
Public sheet_src As Worksheet, sheet As Worksheet, wb_src As Workbook, wb As Workbook
Public region_eval As String, sheetName_src As String, sheetName As String, regionPath As String, fileName As String

Sub findProductStatus(adr As Range)
    networkLocation = ActiveWorkbook.Path
    networkPath = networkLocation & "\Locations\"
    sheetName_src = "Sheet1"
    sheetName = "Sheet1"

    Set wb_src = ThisWorkbook
    Set sheet_src = wb_src.Sheets(sheetName_src)

    region_eval = Range("I" & adr.Row)
    regionPath = networkPath & region_eval
    'fileReference = regionPath & "\ProductList.xlsx"
    fileName = "ProductList.xlsx"
    ChDir regionPath

    Workbooks.Open fileName:=fileName, ReadOnly:=True

    'Set wb = Workbooks.Open(fileName:=ThisWorkbook.Path & "\Locations\Test\ProductList.xlsx", ReadOnly:=True)
    Set wb = Workbooks("ProductList.xlsx")
    Set sheet = wb.Sheets(sheetName)

    c_formula = Application.WorksheetFunction.Index(sheet.Range("$K$2:$K$5000"), Application.WorksheetFunction.Match(sheet_src.Range("A" & adr.Row), sheet.Range("$A$2:$A$5000"), 0))

End Sub


Function getProductStatus(adr As Range) As String

    Call findCourseStatus(adr)
    getCourseStatus = c_formula
    wb.Close

End Function

I haven't tested the sub/func combo against an open file, but when all of the code was inside the Function and the file in question was opened manually, it worked flawlessly. Stepping through the code and using Debug.Print, I see that even though "Workbooks.Open ..." goes through without any discernible error, the workbook doesn't actually get opened, and thus, when we try to use the workbook object to set the sheet, the function/sub terminates.

Answer 1

This can be achieved with a combination of a UDF() and an Event macro.

To retrieve data from a closed workbook, we need four things:

1. the path
2. the filename
3. the sheetname
4. the cell address

The only thing the UDF will do is to display these items in a very specific format:

Public Function someFunc() As String
    Dim wbPath As String, wbName As String
    Dim wsName As String, CellRef As String
    Dim Ret As String

    wbPath = "C:\TestFolder\"
    wbName = "ABC.xls"
    wsName = "xxx"
    CellRef = "B9"

    someFunc = "'" & wbPath & "[" & wbName & "]" & _
          wsName & "'!" & Range(CellRef).Address(True, True, -4150)
End Function

Take note of the position of the single quotes.

We then use a Calculate event macro to detect the UDF's execution and retrieve the data:

Private Sub Worksheet_Calculate()
  Dim r1 As Range, r2 As Range
  Set r1 = Range("C3")
  Set r2 = Range("C4")
  r2.Value = ExecuteExcel4Macro(r1.Value)
End Sub

The Calculate macro needs to know where the UDF is returning the string (C3) and it also needs to know where to put the retrieved data (C4).