Carriage return character

[Guest]

Hi,
I'm importing a text file into excel no problem there, here is my question.
I need to know the last line of the text file, this files ends in a empty
blank line that only have a carriage return or enter character or blank line.
When i'm trying to read this row in cell is a blank line, no difference
bettewn that an a blank excel cell; that doesn't help, I would like to know
if there's a way to read that carriage return or enter or blank line of this
text file on

Thank you so much.

 

[Guest]

I'm having a little trouble understanding the problem, but I can tell you
that the characters Chr(10) and Chr(13) are probably at the source. You can
check for those characters or replace them before copying the text to the
sheet.

Try this code written by Tom Ogilvy:

Sub ReplaceLittleSquares()
Cells.Replace What:=Chr(10), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Cells.Replace What:=Chr(13), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

Good luck,
Pflugs

 

[Guest]

Hi:
thank you for your response.

Maybe i wasn't clear on my problem but the end of carriage is in the last
line of the import text file, the chr(13) is the last line of the text file,
when it goes to excel it disappear, I couldn't find it, and I need to find
that last line of the import file in excel.
I need to know where this import file ends.
Thanks

 

[Guest]

Are you importing the text through VBA or through the import wizard? If you
have written code, could you post the importing part so that we can look at
it. I understand your problem now, and I'll be able to provide a better
answer if I see the code.

Thanks,
Pflugs

P.S. If the last character in the file is ALWAYS a Chr(13), couldn't you
just add one to the row number of the last row? Or are you looking for
something more specific?

 

[Guest]

Thak you Pflugs
I create macro recording the import wizard.
This is my importing file code

sub macro4()
Workbooks.OpenText Filename:="S:\Utilities\Flu\files\SALARIES.txt", Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
2), _
TrailingMinusNumbers:=True
end sub

the thing with teh last line is that some files has it and some don't and
some have more than one, the ones that doesn't have the carriage return or
more than one are invalid I need to validate that, I need to know if is a
valid file or not.

 

[Guest]

Thanks. I am working on a piece of code for you, but my computer just
crashed during its last test. I will reply back with it as soon as I can.

Thanks,
Pflugs

 

[Guest]

Alrastro,

Well, it was harder than I thought, but I finally came up with the correct
code. The problem with your request it that the best way to import text
files is line by line using the "obj.ReadLine" method (see the Help file).
However, this method ignores the New Line character AND the Carriage Return
character. Thus, this method cannot be used in your case.

Instead, we have to import the text character by character. My code will do
that, add the characters to a string until it finds a Carriage Return
(chr(13)), and then copy the string to the active cell. Finally, it will end
the code on activecell that corresponds to the last character of the text
file. For example, if the last character of the file was a "t," the final
activecell would contain the "t." If the last charcter was a Carriage
Return, the final activecell will be an empty cell.

Also, the macro will report whether the file is invalid or not given your
requirements of one carriage return = valid (that could be changed).

Rather than try to put all the code in this tiny window, I posted it to my
blog on Yahoo 360. Here's the link:

http://blog.360.yahoo.com/pflumm30

Note that the code also contains two subroutines, so make sure those are
formatted correctly.

Play around with it and modify it to work with your situation.

Hope this helps!
Pflugs

 

[Guest]

Dear Pflugs
It works;
that was awesome, thank you
you rocks

 

[Guest]

My pleasure. The fact that it worked for you, too, makes my day.