EOF and Excel

[Prozeen]

Hi, how do I use the EOF function with an Excel spreadsheet? I'm thinking that the EOF needs a file number. But in my code below, I don't have a file number. What do I need to do? Any links, code, info available? Thanks.

Dim app As excel.Application
Dim book As excel.Workbook
Dim sheet As excel.Worksheet
Set app = New excel.Application
zza = "C:\ ... Data.xls"
Set book = app.Workbooks.Open(zza)
Set sheet = book.Worksheets(1)

(INSERT EOF CODE HERE.)

Set sheet = Nothing
book.Close SaveChanges:=True
Set book = Nothing
app.Quit
Set app = Nothing

[kb.net]

Hi,

EOF will be reading the cells or the sheets ??

[hopalongcassidy]

EOF is a function that is used with files you have open in Excel. It has nothing to do with the spreadsheet itself.

To do what you want to do, you need to designate a column that tells where the end of the spreadsheet is. It should be a column that has a value for each row of the spreadsheet. Use the IsEmpty function to test the cell in this column in any given row.

Consider the following code:

Dim mySheet as Worksheet
Const COL = 1

Sub main()
Dim i as Integer

Set mySheet = Worksheets("Sheet1")
i = 1
Do While Not IsEmpty(mySheet.cells(i,COL)
.
.
.
Loop
End Sub

[Prozeen]

Thanks for the help guys, here's what I came up with...

Dim rownum As Integer
rownum = 1
Do
    If IsEmpty(sheet.Cells(rownum, 1)) Then
    MsgBox "No more data"
    Exit Do
    Else
    MsgBox sheet.Cells(rownum, 1).Value
    rownum = rownum + 1
    End If
Loop