Dynamic named ranges

[D.S...@ed.ac.uk]

I'm trying to import an Excel spreadsheet into an existing Access
table using Office 2003. Ultimately, the plan is to do it
programmatically using TransferSpreadsheet, but to check that the file
has no problems, I've done it manually with the Import Spreadsheet
wizard.

The worksheet has 43 rows, and I import a named range defined as
"=Sheet1!$C:$E". The import works, but I get a table with 64K rows,
all but 43 being blank!

Is there a way to define the range so that only filled rows will be
imported? I don't want to use the last row's address explicitly,
because I will be importing worksheets of varying lengths.

Any help gratefully received (I don't 'do' Excel as a rule)!

Dave

[Don Guillett]

goto the sheet>insert>name>define>name it>in the refers to box type in
=offset($c$1,0,0,counta($c:$c),3)
and try this instead. The sheet1 will be automatically added for you.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
<D.S...@ed.ac.uk> wrote in message
news:1185460929....@b79g2000hse.googlegroups.com...

[Gord Dibben]

See Debra's site for Dynamic Range naming.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Gord Dibben MS Excel MVP

[D.S...@ed.ac.uk]

On Jul 26, 4:16 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> goto the sheet>insert>name>define>name it>in the refers to box type in
> =offset($c$1,0,0,counta($c:$c),3)
> and try this instead. The sheet1 will be automatically added for you.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software

> dguille...@austin.rr.com<D.St...@ed.ac.uk> wrote in message
>
> news:1185460929....@b79g2000hse.googlegroups.com...
>
Thanks for the idea Don; unfortunately it doesn't appear that
TransferSpreadsheet supports dynamic named ranges (or does anyone have
contrary evidence?).

Cheers,

Dave

[Harlan Grove]

<D.S...@ed.ac.uk> wrote...

I don't believe you can use dynamic named ranges for this. Excel itself
can't/won't resolve defined names referring to formulas (i.e., anything
other than simple, constant range references) in closed workbooks. I suspect
the same would be true for any other application.

How about running an update query on the imported table just after importing
it, deleting all rows with NULL fields?