PDA

View Full Version : Blank line at end of file



cymblicity
03-26-2009, 08:02 AM
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?

chungtinhlak
03-26-2009, 08:40 AM
blank line as in cells A50 to H50 are all blank?

can you attach you xls file?

cymblicity
03-26-2009, 08:42 AM
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.

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

-----------------

Kenneth Hobs
03-26-2009, 09:39 AM
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.
'inFile: http://www.excelforum.com/excel-programming/676142-macro-for-converting-notepad-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

cymblicity
03-26-2009, 09:52 AM
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?

nirvana_1
03-26-2009, 09:54 AM
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

Kenneth Hobs
03-26-2009, 09:58 AM
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.

cymblicity
03-26-2009, 10:03 AM
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.

Paul_Hossler
03-26-2009, 10:07 AM
Just a thought, but could you check the length of the last string read in, and see if it's 0?

Paul

cymblicity
03-26-2009, 10:09 AM
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.

cymblicity
03-26-2009, 10:11 AM
Nirvana, this also always returns not blank because oFS does not include blank lines.

Kenneth Hobs
03-26-2009, 11:10 AM
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.

Kenneth Hobs
03-26-2009, 12:26 PM
If your txt files are not too large, this should work ok. No error checking for drive, folder or file was done.
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