How to create dynamic link to another workbook range

[Dave]

Hi,

I am trying to create a user application where the user inputs a date and a
cell formula will use that date to link to a range on a different (closed)
workbook. Must be done without using a macro.

For example: User chooses date 12/15/2002
There is a range on the closed workbook named "myrange021215" that I want to
link to to pull in the data.

My array formula would be :
{'C:\Mydirectory\Myfilename.xls'!myrange021215}

This works fine if I hard-code it in the cell but I cannot get it to work by
changing the range name via formula like:
='C:\Mydirectory\Myfilename.xls'!&myrange&Text(Mydate,"yymmdd")

Any solutions/ideas greatly appreciated.

 

[Frank Kabel]

Hi
if the other workbook is closed no chance (AFAIK) to access a defined
range name. This defined name only exists if the workbook is open

 

[Jamie Collins]

if the other workbook is closed no chance (AFAIK) to access a defined
range name. This defined name only exists if the workbook is open

Incorrect. Defined Names in a closed workbook are visible as tables to
the Excel odbc driver, so SQL.REQUEST could be used in the array
formula e.g.

=SQL.REQUEST("DSN=db_xls",,1,"SELECT MyKeyCol, MyDataCol FROM
BookLevelName")

Of course, 'BookLevelName' could be replaced by a cell reference etc.

The odbc add-in requires macros to be enabled, which may be a show
stopper for the OP. I'm not sure but MS Query might be made to work
dynamically without using macros.

Jamie.

--

 

[Avner]

Hi,
You can use also Data>Get External data with excel driver (with M
Query).
No need to enable macros.

HTH
Avne

 

[Jamie Collins]

You can use also Data>Get External data with excel driver (with MS
Query).
No need to enable macros.

But can this be made to work *dynamically*, i.e. change the name of
the Excel table in the query, without macros?

Jamie.

--