Dynamic named ranges

  • Thread starter Thread starter D.Stone
  • Start date Start date
D

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
 
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.
 
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.
Click to expand...
Thanks for the idea Don; unfortunately it doesn't appear that
TransferSpreadsheet supports dynamic named ranges (or does anyone have
contrary evidence?).

Cheers,

Dave
 
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)!
Click to expand...

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?
 
Back
Top