find the last cell in an Excel Sheet using VBA

[DougP]

We get a daily Excel sheet from our Legacy system which
I am import into our Access database.
The Excel sheet has different amounts of information each day.
Sometimes the last cell is at cell H2345 sometimes it’s H1345
I currently use H10000 to get it all.
I would like to interrogate from Access VBA the last cell somehow.
Here is my code so far to import

    Dim ExcelRange As String
    Dim ExcelLocation As String
    ExcelLocation = "\\Pdc1\daceasy\oe\sampdata\test\SHIPMENTS.XLS"
    
    ExcelRange = "A5:G10000"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DailyShipment", ExcelLocation, True, ExcelRange

TIA


DougP, MCP, A+

 

[Loomah]

Hi
Have a look in the FAQ section as there are at least 2 covering last cell/row/column.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​