Importing Excel Data

  • Thread starter Thread starter Mattie
  • Start date Start date
M

Mattie

I am experiencing two scenarios where Access97 is importing more rows than there
is data in an Excel worksheet. Does anyone have a way to prevent this?

Scenario 1
Certain columns are formatted down to row 2000. However, data only occupies the
first 1000 rows. Access creates 2000 records in the import table.

Scenario 2
Data originally filled 2000 rows of the worksheet. The bottom 1000 rows were
highlighted and the Delete key was pressed to delete the data. Access creates
2000 records in the import table.

Of note - if rows 1001 to 2000 are deleted, Access only creates 1000 records in
both scenarios.

Thanks!

Mattie
 
Mattie,

You've almost answered your own question! Access is not looking for data, it
is looking for the end of the worksheet, as marked by Excel. To verify, when
you get this problem open the worksheet in Excel and press Ctrl+End: you
will be taken to a cell in the last row read by Access!
Two ways around it:
1. In Excel, make sure you always delete (not clear contents!) all rows
after the last one with data before you save, or
2. In Access, read the linked worksheet not directly, but through a query
that filters out records where field(s) that should not be blank actually
are (criterion: Is Null).

HTH,
Nikos
 
I am experiencing two scenarios where Access97 is importing more rows
than there is data in an Excel worksheet. Does anyone have a way to
prevent this?

Scenario 1
Certain columns are formatted down to row 2000. However, data only
occupies the first 1000 rows. Access creates 2000 records in the import
table.

Scenario 2
Data originally filled 2000 rows of the worksheet. The bottom 1000 rows
were highlighted and the Delete key was pressed to delete the data.
Access creates 2000 records in the import table.

Of note - if rows 1001 to 2000 are deleted, Access only creates 1000
records in both scenarios.

Thanks!

Mattie
Click to expand...

In both cases there ARE 2000 rows in Excel. Only way I know to get rid of
the extra 1000 is to delete them (the rows not just the data), to save the
file, close the file, and reopen, but I readliy accept there may be a
simpler way.
<rant>Excel is an ugly, pitiful program foisted upon us by Microsoft and a
less than diligent business community which believed their advertising.
Twelve years ago Excel wasn't good enough to carry Quattro Pro's skates
into the arena, but it prevailed and dominated, for no good technical
reason.</rant>
Of course, I believe that if you define a range in Excel, you can import
that range. Access help used to say (maybe still does) it couldn't use
ranges in import but it lied.
 
Back
Top