Import Named Ranged

[Ed]

I have an Excel spreadshet with a range that varies in length. To name the
range, I used the formula:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),8)

When I call this range, "ImportRange" in my Access 2007 macro (using the
TransferSpreadsheet macro), I get the error, "The Microsoft Access database
engine could not find the object "ImportRange". Make sure the object exists
and that you spell its name and path name correctly.

I know the name and path name are correct, because if I make the named range
a specific range. ex: "=Sheet1!$A$1:$G$10", the import works fine.

Is there an issue with importing a range based on a formula? I am pretty
sure my formula is correct as I tested it with 1 column width in a combo box.

Thanks for the Assistance,
Ed

 

[Jarah Nin]

I have an Excel spreadshet with a range that varies in length. To name
the
range, I used the formula:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),8)

When I call this range, "ImportRange" in my Access 2007 macro (using the
TransferSpreadsheet macro), I get the error, "The Microsoft Access
database
engine could not find the object "ImportRange". Make sure the object
exists
and that you spell its name and path name correctly.

I know the name and path name are correct, because if I make the named
range
a specific range. ex: "=Sheet1!$A$1:$G$10", the import works fine.

Is there an issue with importing a range based on a formula? I am pretty
sure my formula is correct as I tested it with 1 column width in a combo
box.

Thanks for the Assistance,
Ed

__________ Informace od ESET NOD32 Antivirus, verze databaze 4314
(20090807) __________

Tuto zpravu proveril ESET NOD32 Antivirus.

http://www.eset.cz

Ahoj lidi



__________ Informace od ESET NOD32 Antivirus, verze databaze 4314 (20090807) __________

Tuto zpravu proveril ESET NOD32 Antivirus.

http://www.eset.cz

 

[Ken Snell [MVP]]

For the Range argument, use "ImportRange$".

 

[Ed]

That is giving me the same error.

For the Range argument, use "ImportRange$".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Ed]

I tried something else as well. I named another range, "Range" and had it
=ImportRange. In Excel, when I clicked in the formula, it put the box around
the correct range of cells, but again received the same error in Access.

Just to confirm, I did select just the group of cells I was importing and
named them, and it worked properly again. Just seems to error when I use the
=offset(...) formula in my naming.

Thanks.

 

[Ken Snell [MVP]]

You may want to ask your question about using an Offset formula for a Range
in one of the EXCEL groups in order to find out if such ranges are handled
differently by EXCEL and thus may not be readable by ACCESS.

I don't have any other suggestions for you at this time, sorry!

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Ed]

Ken,

Thanks for your assistance. I will ask in the Excel discussion board to see
if anyone is aware of this. It isn't a huge deal as I can just import
columns A:H, I was just trying to do it more efficiently so that the import
didn't have to look through a million rows, just the ones with data.

Thanks Again.

 

[Mark Han[MSFT]]

Hi Ed

after reviewing Ken Snell and Jarah Nin's reply; I think they have given
you a detail answer; if you have any other questions on the issue, please
let me know

Regards
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================