Why can't I link an Access table to Excel range names

[Guest]

When I try to link Access to an Excel range name, Access does not recognize
the range name.

 

[John Nurick]

More information please. Are you using TransferSpreadsheet or the Access
GUI? If the former, show us minimal code that produces the problem. If
the latter, please describe clearly what's happening. Either way, what
error number and message are you getting?

 

[Guest]

Using GUI from the File Menu, I select External Data. From there I select
Link and the Exel file I want to link. When the wizard opens it lists the
worksheets. When I select the Range Names, It lists the Range Names but an
error message comes up saying that it does not recognize any of the range
names. The names are listed in the format (Sheet Name)$TABLE, but none of
the Range Names that I have entered appear. I have approximately 150
worksheets in the workbook.

 

[John Nurick]

I can't reproduce what I think you're describing, though you're not the
first person to have had the problem.

The spreadsheet import/link wizard will normally only list range names
that refer directly to fixed, contiguous ranges on a worksheet, i.e.
ones whose RefersTo property is a simple range reference such as
"=Sheet1!A1:C5"

As far as I can make out, these are the same names that are listed in
Excel's Edit|Goto dialog. Do your names show up there? If not, there may
be a problem with the way you created them: I find it hard to remember
the subtleties of sheet-level names vs workbook-level names, and it's
also easy to get a RefersTo property like this, which doesn't work:
="Sheet1!A1:C5"

 

[Guest]

I think that you have answered the question since the range that I entered
was non-contiguous.

Thank You.