Linking to an Excel file : how to find the actual last row ?

[Michel S.]

Hi !

Using Access XP, I created a link to an Excel file containing 3 sheets.

So far I tried different methods like :
- DoCmd.TransferSpreadsheet acLink.....
- Define an ADODB connextion to the Excel file and create a recordset
from the sheet contents.


Unfortunately, the file is generated by a mainframe appplication which
is unable to create named ranges and I don't know the number of the
last active row either.

As a result, I find that the linked table (or the recordset) has 65535
rows (the whole sheet).

I know there's a property "UsedRange" in Excel which returns a range
containing only the active area.

Is there anthing similar I can use directly in Access to retreive only
the active range without having to resort to the Excel library and
object model to find it ?

Thanks !

 

[Michel S.]

Please forget my previous message..

The problem is in the Excel file itself : its UsedRange property points
to row 65535 even if therer are only 3000 actual data rows.

It has nothing to do with Access.

MS.

 

[Dirk Goldgar]

Hi !

Using Access XP, I created a link to an Excel file containing 3
sheets.

So far I tried different methods like :
- DoCmd.TransferSpreadsheet acLink.....
- Define an ADODB connextion to the Excel file and create a recordset
from the sheet contents.


Unfortunately, the file is generated by a mainframe appplication which
is unable to create named ranges and I don't know the number of the
last active row either.

As a result, I find that the linked table (or the recordset) has 65535
rows (the whole sheet).

I know there's a property "UsedRange" in Excel which returns a range
containing only the active area.

Is there anthing similar I can use directly in Access to retreive only
the active range without having to resort to the Excel library and
object model to find it ?

Thanks !

I usually don't trust Excel's idea of how many rows are used. I link to
the spreadsheet and retrieve rows with a query that uses criteria that
exclude blank rows. It's easiest if there's a column that must always
contain some non-blank value to be considered valid.