CODEWITHSUNDEEP
vbaexcel
Philippe K
Philippe K

Reputation: 81

Avoid line break at the end of text file using FSO

I am creating and writing into a text file in VBA using FSO. I am able to create the file as wanted, except that a line break is created at the end of the text file, i.e. if my file has 4 lines, there is a 5th line which is blank. Is there a way to remove this last line?

Here's the code I am using so far:

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile(PathName)
For i = 1 To LastRow
    oFile.WriteLine Sheets("Sheet1").Range("A" & i).Value
Next i
oFile.Close
Set fso = Nothing
Set oFile = Nothing

I tried various things to remove this line break at the end, but I was unsuccessful so far.

Thanks a lot for your help

Upvotes: 0

Views: 3147

Answers (2)

Thomas G
Thomas G

Reputation: 10216

I would advise you to read the official documentation about FSO on Microsoft website: http://msdn.microsoft.com/en-us/library/6tkce7xa(v=vs.84).aspx

You'll find out, in others, all the methods available from FSO.

Writeline method: Writes a specified string and newline character to a TextStream file.

Write method: Writes a specified string to a TextStream file.

Since you are using Writeline method only, you got a newline char at the end of each line, last line included. To not have a new line char at the end of your file, you should test when you are writing the last line, and change the method accordingly:

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile(PathName)
For i = 1 To LastRow
    If i = LastRow then
      oFile.Write Sheets("Sheet1").Range("A" & i).Value    
    Else
        oFile.WriteLine Sheets("Sheet1").Range("A" & i).Value
   End If
Next i          
oFile.Close          
Set fso = Nothing          
Set oFile = Nothing

Upvotes: 2

citizenkong
citizenkong

Reputation: 679

Check that you're writing to the last line (i = LastRow), then use the Write method of the TextStream rather than WriteLine.

Upvotes: 2

Related Questions