SaveAs xlText (tab delimited text): delete empty line at the end

[Jules.LT]

Dear all,

I'm using VBA to save a file in tab-delimited text:

InjectFile.SaveAs _
FileName:=InputFile.Path & Application.PathSeparator & "Output.txt", _
FileFormat:=xlText

My problem is that the resulting file has an extra empty line at the end, with just the linebreak.
The software where the file will be injected is very sensitive, and rejects that data.

I try to fix it by reopening the file with

Sub EditTextFile()



Dim strFinal As String 'Final string to print to file
Dim strLine As String 'Line read from original text file
Dim FilePath As String


FilePath = "Output.txt"
FilePath = ThisWorkbook.Path & Application.PathSeparator & FilePath


strFinal = ""


Open FilePath For Input As #1 'open the text file for reading


Do While Not EOF(1)
Line Input #1, strLine
recordCounter = recordCounter + 1
strFinal = strFinal + strLine + vbCrLf 'Linebreak
Loop


strFinal = Left(strFinal, Len(strFinal) - 2) 'Removing the linebreak that I added, plus one extra character


Close #1 'close the file


Open FilePath For Output As #1
Print #1, strFinal
'overwrite the file with the new string
Close #1


End Sub
But even if I remove an additional character, it removes the last char of my data instead of the offending linebreak.

I can't do it manually with a text editor, because this will be used daily by someone else and any slip up could break the database where this file will be injected.

I've been at it for over 2 hours and can't find the solution.
Could a good soul please assist me?

Thanks,
Jules

[p45cal]

How is Injectfile being created/put together?
What is Injectfile?
It's probably that which has the extra character; rather than remove the line/carriage return, let's try not to have it there in the first place.
There are many ways to create a text file in vba and you can usually control pretty much everything that goes in to a text file.

[Jules.LT]

Hi,

InjectFile is a simple Excel table with no line break at the end.
But never mind, the French forum (Developpez.com) gave me the answer :)

In

Open FilePath For Output As #1
Print #1, strFinal
Close #1

You need to add a semi-colon:

Print #1, strFinal;
Or VBA will automatically introduce an extra line break.

Thanks for your help!
Jules

[p45cal]

That's excellent!
Are you still doing this by re-opening the file, or by not placing the character there in the the first place?

(And just a little nudge about cross posting and providing links if you do: http://www.excelguru.ca/content.php?184 )

[snb]

If you use vbcrlf in your file, nothing will be added


With createobject("scripting.filesystemobject")
.createtextfile("G:\OF\output.txt").write replace(.opentextfile("G:\OF\output.txt").readall,vblf,vbcrlf)
end with

[Jules.LT]

That's excellent!
Are you still doing this by re-opening the file, or by not placing the character there in the the first place?
SaveAs seems to add the linebreak whatever I do.

My French crosspost* told me how to read the file in one go instead of line by line and reconstructing it, so I don't have to add yet another linebreak or remove it afterwards.
Btw, strangely enough, I still need to remove two characters to get rid of the linebreak

Str = Input(FileLen(FilePath) - 2, numFile)
Instead of the loop above

Do While Not EOF(1)
Line Input #1, strLine
strFinal = strFinal + strLine + vbCrLf 'Linebreak
Loop


strFinal = Left(strFinal, Len(strFinal) - 2) 'Removing the linebreak that I added, plus one extra character


Re: snb
Thank you. I now have a method that works, but if it breaks I might try this ;)

*sorry about that - also, this board's newbie protection unfortunately prevents me from linking to it

[p45cal]

SaveAs seems to add the linebreak whatever I do.So don't use SaveAs (I couldn't see a vba SaveAs method for a range or a table, only for a worksheet and workbook (and chart)).
Below is one way to export a range:
'xxx = Range("Table1")'if it's an actual Excel Table (as in ListObject)
xxx = ActiveSheet.Range("A1:C5").Value 'your table range
ss = Empty
For rw = LBound(xxx) To UBound(xxx)
ss = ss & Join(Application.Index(xxx, rw, 0), vbTab) 'here you can define the delimiter different from vbTab.
If rw < UBound(xxx) Then ss = ss & vbCrLf 'miss off the last vbCrLf (or whatever oth3er record delimiter you want instead of vbCrLf.
Next rw
'CreateObject("scripting.filesystemobject").createtextfile("C:\Users\Public\Documents\Book1x.txt").write ss
CreateObject("scripting.filesystemobject").createtextfile(InputFile.Path & Application.PathSeparator & "Output.txt").write ssIt'll probably be faster and you'll get no screen flicker.





also, this board's newbie protection unfortunately prevents me from linking to itYes, you can still effectively add a hyperlink as a newbie by removing the http:// part of theh link.

[Jules.LT]

So don't use SaveAs (I couldn't see a vba SaveAs method for a range or a table)

It was indeed a workbook, sorry.
Now that I've got a solution that works, I'll stick with it.
But I'll keep that other one in mind for next time ;)


Yes, you can still effectively add a hyperlink as a newbie by removing the [...] part of theh link.
I do get a different message:

To be able to post links your post count must be 5 or greater. Your post count is 3 momentarily.
Please remove links from your message, then you will be able to submit your post.
Although it looks like the issue should resolve itself soon :-P