writing to txt file - carriage return issue

markelly

New Member
Joined
Nov 9, 2007
Messages
47
Hi All

I have a vba procedure that writes out a content of an array to a txt file. I use write function within a for each loop to achieve this.

The problem is that after the last line is written to a file a new line is fed. How could I prevent from cursor being moved to a new line after the last record is written out?

I understand 'write' function move the cursor to a new line. I could use some other function (print?) for the last line to be written out outside the loop. However, 'write' puts the data elements between inverted comas which is my requirement.
Is there a way to achieve what I need?

Appreciate any suggestions.
Thanks
Marek
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Code:
    Write #1, "The End"[COLOR=red];[/COLOR]
 
Upvote 0
I don't have another suggestion, sorry.
 
Upvote 0
Is this what you mean?

Code:
Private Sub OutPutToText()
Dim MyFile As String
Dim fnum
MyFile = "C:\DanTest.txt"
fnum = FreeFile()
Open MyFile For Output As fnum
Print #fnum, "This is my text"
Print #fnum, "This is more text"
Print #fnum, """This Is my last Text"""
Close #fnum
End Sub

Run that then crack open a file on your C drive called DanTest.txt
 
Upvote 0
Adding to DanTest some more code to do the clean up job on the final end of line character (sorry, but I tried the code above and found there was still a carriage return/line feed at the end of the line). I'm curious as to what system complains about this though - it's not an uncommon convention to end lines with an end of line character (I do believe I have encountered this once before but forgotten what the circumstances were...)

Code was borrowing from the scripting guy:
http://blogs.technet.com/b/heyscrip...-carriage-return-linefeed-in-a-text-file.aspx

Code:
Private Sub OutPutToText()
'-----------------------------
Dim objFSO As Object
Dim objFile As Object
Dim intLength As Long
Dim strEnd As String
Dim strFile As String
Const ForReading = 1
Const ForWriting = 2
'----------------------------
Dim MyFile As String
Dim fnum
Const FILE_PATH As String = "C:\DanTest.txt"

MyFile = FILE_PATH
fnum = FreeFile()
Open MyFile For Output As fnum
Print #fnum, "This is my text"
Print #fnum, "This is more text"
Print #fnum, """This Is my last Text"""
Close #fnum

'-------------------------------------------------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(FILE_PATH, ForReading)
strFile = objFile.ReadAll
objFile.Close
intLength = Len(strFile)
strEnd = Right(strFile, 2)
If strEnd = vbCrLf Then
    strFile = Left(strFile, intLength - 2)
    Set objFile = objFSO.OpenTextFile(FILE_PATH, ForWriting)
    objFile.Write strFile
    objFile.Close
End If
'-------------------------------------------------------------------------

End Sub
 
Upvote 0
Thank you both.
Xenou, the code works perfectly. I wonder though why using 'print' as Dan did has still added carriage return at the end of the line.

As for the system this is required for... I've been preparing data for upload for external company and they complained about line feed character at the end of the file. Don't know what system they're using I only got output requirements.
 
Upvote 0
Glad it helps. I don't know about the Print statements - that's the way it works, I guess. There's actually an end of line character after every line - we only notice it at the end where it's causing problems. It's probably not the worst scheme to just lop of the last two characters (CR/LF) this way.
 
Upvote 0

Forum statistics

Threads
1,215,988
Messages
6,128,145
Members
449,426
Latest member
revK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top