Problems writing to text files

[Iapetus]

In my work, I often have to write VBA code to read data from a text file, perform some calculation or manipulation on it, and then write the results to another text file.

Normally, I try to use do...loop until eof(1) (or do loop...until eof(1)) to work through the source file. However, my code is often (but not always) afflicted by a bug where the last few lines do not get written to the output file.

The only reliable solution I have found is to remove the until eof(1) condition, and let the code run until it hits the end of the file and crashes out.

I've asked about this before, and the solution I was given (use print #2, string & string, rather than print #2, string + string) worked in some cases, but not all.

(If it is of relevance, I am using Excel 2002 SP3, on a computer running WinXP SP2. Yes, I know that's practically stone-age technology, but I don't make the IT policy).

Here is some example code from my latest encounter with this problem:

Private Sub cmdConvert_Click()
    'setup input/output
    Dim Path As String, FileIn As String, FileOut As String
    Path = txtPath
    FileIn = txtFileIn
    FileOut = txtFileOut
    If Right$(Path, 1) <> "/" Then Path = Path + "/"
 
    Close
    Open Path + FileIn For Input As #1
    Open Path + FileOut For Output As #2
 
 
    ' read, convert and write
    Dim POS As Integer
    Dim LAT As Double
    Dim LNG As Double
    Dim CBA As Integer
    Dim c As String
 
    POS = 1
    CBA = 1
    c = ","
 
    Print #2, "POS,LAT,LONG,CBA"
 
    ' For some reason, if we use a "do...loop until eof" to read and write the data, it stops writing before the end
    ' So we just have to use an unending "do...loop", until it hits the and and crashes out.
 
 
    Do
        Input #1, LAT, LNG
        If LAT = 9999 Then
            ' If new polygon, increment CBA rather than writing data
            CBA = CBA + 1
        Else
            ' write positions and additional data
            'Print #2, CStr(POS) & c & Format(LAT, "0.0000000") & c & Format(LNG, "0.0000000") & c & CStr(CBA)
 
            Print #2, CStr(POS); c;
            Print #2, Format(LAT, "0.0000000"); c;
            Print #2, Format(LNG, "0.0000000"); c;
            Print #2, CStr(CBA)
 
 
            POS = POS + 1
        End If
 
    Loop Until EOF(1)
    'Loop
 
    MsgBox ("Done.")
End Sub

Explanation of the code:
The input file is space delimited, containing a list of vertices for one or more polygon (latitude and longitude, decimal degrees). If it contains more than one polygon, then they are separated a "latitude" and "longitude" of 9999 9999.
The output file is comma delimited, containing the same data, but each line is consecutively numbered, and instead of using 9999 9999 to represent a new polygon, each polygon is numbered (the last column).
Everything works correctly, appart from the problem described above.

In this case, using the Print #2, CStr(POS); c; code results in the last few lines not being written to the output file.

If I use the Print #2, CStr(POS) & c & Format(LAT, "0.0000000") & c & Format(LNG, "0.0000000") & c & CStr(CBA) (commented out in the above code), then I get an even stranger problem where the output stops part way through writing a latitude value. (In one test case, the last line in the output file was 37,57.7 when line 37 should read 37,57.7390900 -6.3770350,2. And there should have been 49 lines in total).

 

[VoG]

This may help you http://www.cpearson.com/excel/imptext.aspx

 

[Iapetus]

Ah, I think I might have found the answer.

It was because I hadn't Closed the files at the end.

I see now that the Help for Close says "When you close files that were opened for Output or Append, the final buffer of output is written to the operating system buffer for that file." I hadn't realized that some of the data is stored in the buffer until you Close a file.