PDA

View Full Version : Losing last line when readig ASCII file into Array



yogi_bear_79
10-22-2007, 06:50 AM
Why do I lose the last line of text, with this code? If I check the variable InputString, before I write my array to the worksheet it contains the last line, yet, my array doesn't contain it?


Public Sub TextReader()
Dim FileName As String, Path As String
Dim InputString As String, FileNum As Integer
CVSArray() As String

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

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

NextRow = Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).Row
Sheets(1).Range(Cells(NextRow, 1), Cells(NextRow, i - 1)) = CVSArray

Close #FileNum
FileName = Dir()
Loop

End Sub

Bob Phillips
10-22-2007, 07:15 AM
Works fine for me. Can you post the offending text file?

yogi_bear_79
10-22-2007, 07:24 AM
Even something as simple as this:
1
2
3
4

Cells A:C are populated, but Cell D which should contain the number 4 is blank

Bob Phillips
10-22-2007, 07:53 AM
I see what it is, it is in the code to drop it into the worksheet ( I only tested upto loading the array previously)



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


should be



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

yogi_bear_79
10-22-2007, 07:55 AM
Thanks, lord only kows why that was there, remnants of a previous rendition of this code I presume! thanks

Bob Phillips
10-22-2007, 07:59 AM
My thought was that you did it because you knew that the array was zero based (but of course cells are not), a simple error (done the same sort of thing many times myself).

yogi_bear_79
10-22-2007, 11:19 AM
Yeah in hindsight that was the reason. I've been thru so many iterations of this code trying to get it to properly parse the data, that I started to lose track.