Consulting

Results 1 to 3 of 3

Thread: Access VBA Problem with Text files

  1. #1

    Access VBA Problem with Text files

    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.

    [VBA]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
    [/VBA]

    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.

  2. #2
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    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:
    [vba]FSOFileRevised.WriteLine (Left(tempStr, Len(FSOFile.ReadLine) - 14))[/vba]
    No value was assigned to tempStr, so that code has the same effect as:
    [vba]FSOFileRevised.WriteLine ""[/vba]
    See if this Do While loop does what you want:
    [vba] 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 [/vba]

  3. #3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •