Access VBA Problem with Text files

[sdmmkd]

I have a problem where I need to go through a text file, line by line, and based upon a criteria, selectively edit the lines. The code that I have, opens one file, reads the lines individually, and then writes the lines to a new file. The issue that I'm running into, is that the code is not reading the lines in sequential order, so the output file is coming out in a separate order from the original. In this case, the order has to be identical.

FYI: I am attempting to do this in Access 2003.

Sub text2()
Dim FSO, FSOFile, FSOFileRevised
Dim FilePath As String, FilePathRevised As String, a As Integer
Dim tempStr As String
Set FSO = CreateObject("Scripting.FileSystemObject")
Let FilePath = "C:\TestExp.txt"
Let FilePathRevised = Left(FilePath, Len(FilePath) - 4) & "_Revised" & Right(FilePath, 4)
'Set FSO = New FileSystemObject

If FSO.FileExists(FilePath) Then

Set FSOFile = FSO.OpenTextFile(FilePath, 1, False)
Set FSOFileRevised = FSO.OpenTextFile(FilePathRevised, 2, True)
Do While Not FSOFile.AtEndOfStream
If Mid(FSOFile.ReadLine, 2, 4) = "APIF" Then
FSOFileRevised.WriteLine (Left(tempStr, Len(FSOFile.ReadLine) - 14))
Else
FSOFileRevised.WriteLine (FSOFile.ReadLine)
End If
Loop
FSOFile.Close
FSOFileRevised.Close
Else
MsgBox (FilePath & " does not exist")
End If
End Sub


The text that it is processing, looks like the following (in this order):

"APIF","CITY0010","01 60699300_S1","15875132","4299.88","0"
"APDF","","","","","","","","","","","0035-000-6456.00","","","","","","3069.89"
"APDF","","","","","","","","","","","0035-000-6455.00","","","","","","1229.99"
"APIF","CITY0010","01 60699300_S2","15875132","2418.68","0"
"APDF","","","","","","","","","","","0031-000-6456.00","","","","","","1726.81"
"APDF","","","","","","","","","","","0031-000-6455.00","","","","","","691.87"
"APIF","CITY0010","01 60699300_S4","15875132","20155.72","0"
"APDF","","","","","","","","","","","0022-000-6456.00","","","","","","14390.12"
"APDF","","","","","","","","","","","0022-000-6455.00","","","","","","5765.60"

The behavior that I am getting is that it reads in the following order:
"APDF","","","","","","","","","","","0035-000-6456.00","","","","","","3069.89"
"APDF","","","","","","","","","","","0031-000-6456.00","","","","","","1726.81"
"APDF","","","","","","","","","","","0022-000-6456.00","","","","","","14390.12"
etc, etc...

How do I get it to read the lines in sequential order?

Any help would be greatly appreciated.

[hansup]

Each time through the Do While loop, the code reads 2 lines and writes one. That's because the code calls Readline twice whether the If expression is True or False. Each call to Readline reads another line from FSOFile.

Another problem is this line:
FSOFileRevised.WriteLine (Left(tempStr, Len(FSOFile.ReadLine) - 14))
No value was assigned to tempStr, so that code has the same effect as:
FSOFileRevised.WriteLine ""
See if this Do While loop does what you want:
Do While Not FSOFile.AtEndOfStream
tempStr = FSOFile.ReadLine
If Mid(tempStr, 2, 4) = "APIF" Then
FSOFileRevised.WriteLine (Left(tempStr, Len(tempStr) - 14))
Else
FSOFileRevised.WriteLine (tempStr)
End If
Loop

[sdmmkd]

The tempstr was something left over from me simplifying the code for this site, but the use of the readline command twice was the issue here. I didn't realize that readline not only read the line but advanced to the next line each time its used. I used some code that was very similar to your suggestion and it worked great.

Thanks a lot.