I'm writing a form that validates a txt file against a list of 100s of validations. Everything's done, but I can't figure out how to check the file for a carriage return at the end.
Currently I use FileSystemObject to read the file line by line and store the lines. It's my understading that this methods ignores Cr, Lf, so I'll just use a seperate procedure to check for a newLine.
All I need is this:
If (fileDoesNotHaveNewLineAtEnd) Then
'Stuff
EndIf
Any ideas?
blank line as in cells A50 to H50 are all blank?
can you attach you xls file?
No, I'm reading a .txt file. I'm not using anything in the spreadsheet at the moment.Originally Posted by chungtinhlak
All I need to do is see if a text file ends with a new line.
If I open a bad file in notepad, it shows as
-----------------
line
line
line
-----------------
A good file is
-----------------
line
line
line
-----------------
If you posted your code, it would be easier to help.
Without doing it the fso way, I would do it like this without any error checking.
[VBA]'inFile: http://www.excelforum.com/excel-prog...-to-excel.html
Sub Test_LastLineBlank()
Dim myFile As String
myFile = ThisWorkbook.Path & Application.PathSeparator & _
"Rough data to excel.txt"
Debug.Print myFile
MsgBox "Last line in file below blank? " & LastLineBlank(myFile) & vbCr & _
myFile, vbInformation, "Blank at End?"
End Sub
Function LastLineBlank(inFile) As Boolean
Dim iStr As String
Dim fni As Integer
fni = FreeFile
Open inFile For Input As #fni
Line Input #fni, iStr
Do While Not EOF(fni)
Line Input #fni, iStr
Loop
LastLineBlank = iStr = vbNullString
End Function[/VBA]
It always returns False. When I print out iStr, it is always the last line of text.
Is there a simple way to return the last character of a file maybe? Theoretically, it should be CrLf, right?
Sub CheckLastLineBlank()
Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\temp\test.txt", ForReading)
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
Loop
If Len(Trim(strLine)) = 0 Then
MsgBox "Last line is blank"
Else
MsgBox "Last line is Not blank"
End If
Set objFile = Nothing
Set objFSO = Nothing
End Sub
My code worked for me. Maybe you have something else going on? The way I tested it was that I opened my txt file in Notepad. Made a change at the end, saved the file and then ran my vba code. Changed the last line again in notepad, saved the file and ran the code again.
vbCrLf consists of 2 characters, Chr(13) and Chr(10) or vbCr and vbLf.
Kenneth, save my attachment as test.txt (had to rename to .doc to attach) and try it with that file. In notepad, you can see an empty line at the end of the file. This is a correct file. If you backspace so that there is no empty line, that should throw the error.
Just a thought, but could you check the length of the last string read in, and see if it's 0?
Paul
Tried it.ReadLine in oFS does not include blank lines.
Nirvana, this also always returns not blank because oFS does not include blank lines.
It will take some work to get what you want.
However, is there really a need for such? QueryTables and normal text reading methods don't care that much. What most people worry about is if the last line only contains vbCrLf. That is what the 2 methods will tell you. The EOF() will get that last line whether it has an ending vbCrLf or just plain text.
If your txt files are not too large, this should work ok. No error checking for drive, folder or file was done.
[VBA]Sub Test_LastCharInFileISvblf()
Dim s As String
Dim myFile As String
myFile = ThisWorkbook.Path & Application.PathSeparator & _
"test.doc"
MsgBox "Last line in file below blank? " & LastCharInFileISvblf(myFile) _
& vbCr & myFile, vbInformation, "vbLf Character at End?"
End Sub
Function LastCharInFileISvblf(ByVal txtFilename As String) As Boolean
LastCharInFileISvblf = Right(OpenTextFileToString(txtFilename), 1) = vbLf
End Function
Function OpenTextFileToString(ByVal strFile As String) As String
Dim hFile As Long
hFile = FreeFile
Open strFile For Input As #hFile
OpenTextFileToString = Input$(LOF(hFile), hFile)
Close #hFile
End Function
[/VBA]
Posting Permissions
Reply With Quote
