Dynamic named ranges

[D.Stone]

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.

 

[Gord Dibben]

See Debra's site for Dynamic Range naming.

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


Gord Dibben MS Excel MVP

 

[D.Stone]

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.

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]

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)!

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?