Linking to Dynamic Ranges in MS Excel

  • Thread starter Thread starter Andibevan
  • Start date Start date
A

Andibevan

Hi All,

I am having problems linking a table to a dynamic named range in an excel
worksheet. I can link fine to static named ranges but when I try to link to
a dynamic named range I get the error "The Microsoft Jet Database engine
could not find the object
'Lessons'$Z_504832EE_BBE5_4981_A61C_2C9B269B7607_.wvu.pr'?

Is there any way to get around this as I presume that dynamic named ranges
are fairly important in these kind of situations?

Thanks

Andi
 
As far as I know you can't link to a dynamic named range.

AIUI the routines that the Jet database engine uses to link to data in
Excel workbooks only have access to values that are stored in the
workbook and cannot evaluate formulas.

If static ranges won't do your job you'll need to write your own code to
work out the ranges you need to link to and either define static named
ranges accordingly or simply pass the sheet and cell reference to the
query.

(Remember that linking to Excel ranges has just become dramatically less
useful as the latest Office 2003 and XP updates have removed the ability
to update tables linked this way.)
 
Thanks John - that was what I feared but just needed it confirming.

Any sensible reason why they removed this functionality? I know I've only
just discovered it but it seems like in some situations it could be very
useful. What would be the best method in Office 2003? I presume to convert
XL to a CSV and then link to that?
 
It seems to have been the result of legal action over a patent. Look at
Klatuu's recent post in this group "RE: linked Excel spreadsheet's in
Access editabiliity" for a couple of links.

You can still link to Excel sheets and ranges, but you can't update the
values in Excel this way. Linking to a CSV file instead is no help
because those links never have been updatable.

The official work-rounds are (1) import the data to an Access table,
update it, and export it back to Excel; and (2) use Automation.
 
Back
Top