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.
Try this for the loop.
Dim x as Long
Do Until (xlssheet.Cells(x, 1) = "")
x = x + 1
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.
By Magic in forum VB Classic
Last Post: 12-15-2005, 10:53 AM
By dmb-job in forum VB Classic
Last Post: 06-10-2005, 06:00 PM
Last Post: 08-12-2002, 10:40 PM
By David in forum VB Classic
Last Post: 09-04-2001, 07:38 AM
By Tomer Cagan in forum ASP.NET
Last Post: 07-24-2001, 09:01 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL