Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Carriage return character
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Carriage return character
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 "alrastro" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Carriage return character
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 "Pflugs" wrote: 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 "alrastro" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Carriage return character
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? "alrastro" wrote: 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 "Pflugs" wrote: 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 "alrastro" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Carriage return character
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Carriage return character
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 "alrastro" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Carriage return character
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Carriage return character
Dear Pflugs
It works; that was awesome, thank you you rocks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Carriage return character
My pleasure. The fact that it worked for you, too, makes my day. :-)
"alrastro" wrote: Dear Pflugs It works; that was awesome, thank you you rocks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Carriage return character in combobox | Excel Discussion (Misc queries) | |||
What are the character codes of a carriage return entered in a comment? | Excel Programming | |||
How to remove or replace a carriage return character in a cell? | Excel Discussion (Misc queries) | |||
Function to return Character Position of Xth character within a string | Excel Programming | |||
How do I ignore newline character/carriage return while importing | Excel Discussion (Misc queries) |