Importing Excel spreadsheet

[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 (Apologies if this a no-brainer: I don't
'do' Excel as a rule)!

[Kc-Mass]

Look at this - not my work but what you are looking for:
Dynamic Ranges

It is often useful to create a name that refers to a range of cells, where
the range depends on the content of the cells. For example, you may want a
name that refers to the first N non-blank entries in column A. Excel's
Name tool allows you to do this. For example, creating a name called
DynaRange, referring to

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

If the first 20 rows of column A contain data (and the rest are blank),
DynaRange will refer to the range A1:A20

See the on-line help for the =OFFSET function for a description of the
arguments. Setting the Width argument to 2 will allow us to use this name
in a =VLOOKUP function

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

Then, call VLOOKUP with the DynaRange argument for the lookup range:

=VLOOKUP(C1, DynaRange, 2)

As data is added to columns A and B, the range search by VLOOKUP will extend
to include the new data.

Download a workbook illustrating dynamic ranges.

<D.*****@ed.ac.ukwrote in message
news:11*********************@w3g2000hsg.googlegrou ps.com...

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 (Apologies if this a no-brainer: I don't
'do' Excel as a rule)!

[Keith Wilby]

<D.*****@ed.ac.ukwrote in message
news:11*********************@w3g2000hsg.googlegrou ps.com...

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 (Apologies if this a no-brainer: I don't
'do' Excel as a rule)!

If your data is surrounded by blank cells then this should work:

Dim myRange as Range
Set myRange = Range("A1").CurrentRegion

That assumes that cell A1 is in your region.

Keith.
www.keithwilby.com

[D.Stone]

On 9 Jul, 11:36, "Kc-Mass" <connearney_AT_comcast_PERIOD_netwrote:

Look at this - not my work but what you are looking for:
Dynamic Ranges

It is often useful to create a name that refers to a range of cells, where
the range depends on the content of the cells. For example, you may want a
name that refers to the first N non-blank entries in column A. Excel's
Name tool allows you to do this. For example, creating a name called
DynaRange, referring to

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

If the first 20 rows of column A contain data (and the rest are blank),
DynaRange will refer to the range A1:A20

See the on-line help for the =OFFSET function for a description of the
arguments. Setting the Width argument to 2 will allow us to use this name
in a =VLOOKUP function

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

Then, call VLOOKUP with the DynaRange argument for the lookup range:

=VLOOKUP(C1, DynaRange, 2)

As data is added to columns A and B, the range search by VLOOKUP will extend
to include the new data.

Download a workbook illustrating dynamic ranges.

<[e-mail] in message

news:11*********************@w3g2000hsg.googlegrou ps.com...

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 (Apologies if this a no-brainer: I don't
'do' Excel as a rule)!

Thanks for this, KC-Mass, I thought it sounded just what I need.
BUT... there is a snag! I have set up a named range similar to your
DynaRange using Insert|Name|Define. However, this name does not appear
in the Name box dropdown list, and (more crucially) nor does it appear
in the list of named ranges within Access' Import Spreadsheet wizard.
So, although the name appears to be defined in Excel, it seems I can't
use a dynamic range in Access. (Incidentally, the Import Spreadsheet
wizard doesn't display ranges based on non-contiguous columns,
either).

I wanted to avoid using Office automatiion if there was an easier way,
but hope is fading fast!

Cheers,

Dave