Import excel named range into Access error 3011

[Kate]

Hi, I've read a few posts on this topic, but none addresses my
particular problem.

I use Office 2003 on an XP SP3 machine.
I am using transferspreadsheet to import a named range in an excel file
into Access.
This has worked beautifully until suddenly it stopped working.

I find now that if I extract the address of the named range from within
the Access VBA code,
it looks like this:

=#REF!$A$1:$Z$166

rather than this, which is what it should be:

=Data!$A$1:$Z$166

When I open the spreadsheet and get the address of the named range in
the immediate window,
it also has the #REF error. However, looking at the sheet named Data in
the Object Explorer window,
it appears normal.

How do I correct this reference error?

I copied the sheet named Data into the workbook from a template workbook
from which it was originally made, and deleted all references to that
template
so that the sheet would collect data from the workbook in which it was
copied to, rather than the one it was copied from.

I had to do this because the data sheet has begun to mysteriously
disappear from the workbooks that are created from this
template workbook. I can't even begin to imagine how this happens, but
it began to occur after I installed the Office 2007 converters.

Thanks to all,
Kate

 

[pshepard]

Hi Kate,

One way that a range name can change to =#REF!$A$1:$Z$166

is when an existing named range is on a worksheet that is deleted.

In Excel 2003, if you make a copy of a workbook with named ranges, and
subsequently move a worksheet from the original workbook to the new copy -
Excel would ask you if you want to use the named range definition from the
source or destination workbook.

In Excel 2007, you are able to reuse a name for ranges. The first time a
name is used it defaults to applying to the workbook. The subsequent times it
is limited to the worksheet it refers to.

Try deleting the names if they are limited to a worksheet and recreate the
named ranges if necessary so that they apply to the workbook.

You can edit the named ranges, to change the REF to Data.

Hope this helps.

Peggy