PDA

View Full Version : Continually check for next row, or check once & increment?



yogi_bear_79
10-22-2007, 11:58 AM
I figure this is a no-brainer, but I still seek input.

I am parseing large text files, and each time the array is written the code checks for the next available row. It would seem to be more efficent to check for the next available row at the top of the routine, store it in an integer and increment the integer as the code loops. In one example the loop could run 1000 times, seems the end result would be faster to check one & increment?

Bob Phillips
10-22-2007, 12:02 PM
Public Sub TextReader()
Dim FileName As String, Path As String
Dim InputString As String, FileNum As Integer
Dim i As Long
Dim NextRow As Long
Dim CVSArray() As String

Path = "C:\TEST\"
FileName = Dir(Path & "*.txt")

NextRow = 1

Do Until FileName = vbNullString
FileNum = FreeFile ' next file number
ReDim CVSArray(0) 'clear the array
i = 0 'line counter

Open Path & FileName For Input Access Read Shared As #FileNum

Do While Not EOF(FileNum)

ReDim Preserve CVSArray(0 To i)
Line Input #FileNum, InputString

CVSArray(i) = InputString

i = i + 1 'increment line counter
Loop ' until the last line is read

Sheets(1).Range(Cells(NextRow, 1), Cells(NextRow, i)) = CVSArray
NextRow = NextRow + 1

Close #FileNum
FileName = Dir()
Loop

End Sub