-
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)
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.
-
Try this for the loop.
Dim x as Long
Do Until (xlssheet.Cells(x, 1) = "")
<<your code>>
x = x + 1
Loop
-
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.
Similar Threads
-
By Magic in forum VB Classic
Replies: 26
Last Post: 12-15-2005, 10:53 AM
-
By dmb-job in forum VB Classic
Replies: 0
Last Post: 06-10-2005, 06:00 PM
-
Replies: 146
Last Post: 08-12-2002, 10:40 PM
-
By David in forum VB Classic
Replies: 1
Last Post: 09-04-2001, 07:38 AM
-
By Tomer Cagan in forum ASP.NET
Replies: 1
Last Post: 07-24-2001, 09:01 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|