Jules.LT
02-10-2016, 11:27 AM
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
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