Error removing last vbCrLf character from a text file created by FSO CreateTextFile

[Andyza]

I'm using FileSystemObject to open and write to a tab delimited text
file.

First, I connect to a database and select some data. Then I create the
text file and insert each record in the text file. At the end of each
record I insert a vbCrLf character in the text file to create a new
line. Thus each record is in a new line.

The problem is that I get a tab delimited text file where the LAST LINE
in the text file is always a blank line. I need to remove the LAST
vbCrLf character from the text file so that there is no blank line at
the end of the text file.

I've tried checking for AtEndOfStream in a Loop to try and remove the
LAST vbCrLf character, but there's something wrong with the code...

The Do While Not objStream.AtEndOfStream loop is crashing with
"Microsoft VBScript runtime (0x800A0036) Bad file mode" errors.

What have I done wrong?

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
strDataFileName = Server.MapPath("SomeFolder\TextFile.txt")

'Delete any existing Format 01 text file (from previous batches)
If (objFSO.FileExists(strDataFileName)) Then
objFSO.DeleteFile(strDataFileName)
End If

'Create blank text file
strDataPath = Server.MapPath("SomeFolder\")
Set strCreateFile = objFSO.CreateTextFile("TextFile.txt", True)
Set strCreateFile = Nothing

Const ForWriting = 2
Set objStream = objFSO.OpenTextFile(strDataFileName, ForWriting, True)

strSelect = "SELECT X,Y,Z FROM SomeTale WHERE SomeCondition = '12345'"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("connstring")
Set rs = conn.Execute(strSelect)

'Add the data to the text file
Do While Not rs.eof
strText = rs("SURNAME") & VbTab & rs("FIRSTNAME") & VbTab &
rs("DSINITIALS") & VbTab & VbTab & VbTab & "0.00" & VbTab & rs("ID") &
VbTab & rs("BLAH") & VbTab & "X"
objStream.Write (strText & vbCrLf) 'This inserts a vbCrLf
character at the end of each record
rs.MoveNext
Loop

rs.Close : Set rs = nothing
conn.Close : Set conn = nothing

'Remove the last VbCrlf from the text file.
Do While Not objStream.AtEndOfStream
CLine = objStream.ReadLine
CLine = Replace(CLine,VbCrlf,"")
strDataFileName.Write CLine
Loop

objStream.Close : Set objStream = Nothing
Set objFSO = Nothing

[Anthony Jones]

Did you try it like this:-
objStream.Write strText
rs.MoveNext
If Not rs.EOF Then objStream.write vbCrLf
Loop

[Andyza]

Perfect!
Thanks

[Andyza]

> Did you try it like this:-

objStream.Write strText
rs.MoveNext
If Not rs.EOF Then objStream.write vbCrLf
Loop

Thanks. It worked perfectly.

But then I tried moving the code into a Function to make it reusable:

Function PrintCategory(Category)
strText = strRecordNumber & VbTab & rs("somefield") & VbTab &
Category & VbTab & rs("anotherfield") & VbTab & "X"
objStream.Write (strText)
If Not rs.eof Then
objStream.Write vbCrLf
End If
End Function

And I call it from the main body of the page using:

Set rs = conn.Execute("SELECT X1,Y2,Z3 FROM SomeTable WHERE Something
= '12345'")
Do While Not rs.eof
If rs("X1") = "Yes" Then
PrintCategory(01)
End If

If rs("Y2") = "Yes" Then
PrintCategory(02)
End If

If rs("Z3") = "Yes" Then
PrintCategory(03)
End If

rs.MoveNext
Loop
The problem is that I now get a blank line/carriage return at the end
of the file. As in my original post, I don't want the text file to have
this blank line / carriage return after the LAST record.

Where have I gone wrong?

[Anthony Jones]

In my code it was important that the MoveNext method on the recordset be
called before testing EOF.

Your code is no longer doing that.