Loop thru an excel file thru VB

[Sinni]

I have an application to read an excel file and do some validation with few fields and write the data to an output file. Everything works fine but I am not able to stop when it reaches to the end of the excel file.
My code is

Dim xl As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlwbook As Excel.Workbook

Set xlwbook = xl.Workbooks.Open(ImportFile)
Set xlsheet = xlwbook.Sheets.Item(1)

introw = 2
intcol = 1

Y = FreeFile
Open App.Path & "\OutFile.txt" For Output As #Y

Z = FreeFile
Open App.path & “\file.xls” For Input As #Z

Do While Not EOF(Z)

BranchNr = xlsheet.Cells(introw, intcol)
ProductNr = xlsheet.Cells(introw, intcol + 1)
Acct = xlsheet.Cells(introw, intcol + 2)
Name1 = xlsheet.Cells(introw, intcol + 3)
Name2 = xlsheet.Cells(introw, intcol + 4)

ValidateProductNr
ValidateAcct

Stroutput = BranchNr & ProductNrNew & AcctNew & Name1 & Name2

Print #Y, Stroutput
Stroutput = ""

introw = introw + 1
Loop

In my “file.xls” file there are 10216 rows, but when I reach the row “10216” the program should stop since it is the end of file and exit out of the loop but it is not. 10216 is not a constant, for each file the number of rows may change.
How do I determine the end of the excel file. Please help.

[mdb002]

Try this for the loop.

Dim x as Long

Do Until (xlssheet.Cells(x, 1) = "")
<<your code>>
x = x + 1
Loop

[RobDog888]

You might not want to have everything depending on it detecting a empty string in a cell. What if the data contains a row with a nullstring but the next rows have more data to read.

This will detect the last used row in Excel
MsgBox Workbooks(1).Sheets(1).UsedRange(Sheet1.Rows.Count, 1).End(xlUp).Row

so get that value and then do a For Next loop with its upper bonds the value of the last used row.