Import from Excel

[Gerry Goldberg]

I have written VBA code to read data from an Excel spreadsheet. How can I
tell what is the last line of data on the spreadsheet?

Thanks,

Gerry Goldberg

 

[Rob Oldfield]

It depends on how you're doing it... what's your code?

 

[Brendan Reynolds]

If you're using automation, Excel has a 'UsedRange' property you may find
useful ...

Public Sub LastCell()

Dim r As Range
Set r = ThisWorkbook.ActiveSheet.UsedRange
Debug.Print r.Cells(r.Rows.Count, r.Columns.Count).Address

End Sub

 

[Gerry Goldberg]

Here is my code. I need to know what is the last row containing data (N)

Const conCol="ABC"
dim varData as variant
Dim R as integer,X as integer,N as integer
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objResultsSheet As Excel.Worksheet
Dim objXLRange As Excel.Range
Set objXLBook = GetObject("C:\MySpreadSheet.xls") ' Go
ahead and create an object from the XLS file
Set objXLApp = objXLBook.Parent
' Workbook Parent property ispointer to Excel's Application object
Set objResultsSheet = objXLBook.Worksheets("Sheet1") ' this
is the worksheet name
' N is the last data row in the spreadsheet (this is what I need to find
out for this spreadsheet)
For R=1 to N
For C = 1 to 3
set objXLRange = objResultsSheet.range(mid(conCol,C,1) & chr(R)
& ":" mid(conCol,C,1) & chr(R))
varData = objXLRange.value
' Process this spread sheet cell data here
next C
next R
objXLApp.quit

Thanks...