Loop thru an excel file thru VB
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)
Stroutput = BranchNr & ProductNrNew & AcctNew & Name1 & Name2
Print #Y, Stroutput
Stroutput = ""
introw = introw + 1
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.