xlfTextRef from another workbook

[dhoorens]

Hi
I have a little question about xlfTextRef
I've made an XLL in which I have an XL function called TextToRef corresponding to xlfTextRef.

My objective is to build a "special" vlookup function with these parameters:

• lookup_value
• col_index_num
• RAnge_lookup
• array defined by:
  • path
  • filename
  • sheetname
  • rangeName

I have 2 workbooks: Book1.xls and Book2.xls in the folder: c:\data\Excel center\Range other workbook

In book2.xls, I define the range DATABOOK2 in cells Sheet1!B2:C4 which equals to {{a,b,c},{1,2,3}}

In book1.xls, I have tested the 3 following solutions:

1. VLOOKUP("a";Book2.xls!dataBook2;2;FALSE) which works even if Book2.xls is closed, BUT it doesn't suit me because I cannot enter the arrayName (i.e. Book2.xls!dataBook2) as a combination of my parameteres
2. VLOOKUP("a";INDIRECT(TextToRef("D33";TRUE));2;FALSE) which works only if Book2.xls is opened. D33 refers to CONCATENATE(Filename;"!";RangeName)
3. VLOOKUP("a";INDIRECT(TextToRef("D35";TRUE));2;FALSE). D35 refers to CONCATENATE(path;filename;"!";rangename). It doesn't work (obviously :-/ )

My question is:
Is it possible to make work solution #3 (or #2 or smtgh else) even if Book2.xls is closed?

Thanks
David

 

[dhoorens]

I forgot to mention that I don't want( euuhh I would like not to have ) a VBA-based solution if possible