I am creating a text file within excel and I do not know how to create what
I think is needed and that is an end of line character for each line. The
text is all in column A, one record in each cell. The text file that excel
creates appears to be
different to the model I am using in Notepad at the end of the line.
Anyone any experience in this?
Your question is ambiguous both in what you get and what you are comparing
it to, and even whether it makes a difference or not to your usage.
In fact, I don't even know if you are trying to create a CSV file, text in columns,
or just writing records i.e. HTML records..
From with VBA you can use xlCR or CHR(13) for carriage return,
xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)amp;CHR(10) for CRLF.
if producing one long string.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm
quot;Mervyn Thomasquot; gt; wrote in message ...
gt; I am creating a text file within excel and I do not know how to create what
gt; I think is needed and that is an end of line character for each line. The
gt; text is all in column A, one record in each cell. The text file that excel
gt; creates appears to be
gt; different to the model I am using in Notepad at the end of the line.
gt; Anyone any experience in this?
gt;
gt;
Sorry I was not understood. I am seeking to produce an excel file where
each record is in A1,A2 (one record per cell) etc and then saving the file
in text format to be able to import the records into a banking system. Each
record needs to be right padded with blanks to produce a record that is 178
characters wide. I have a user function that produces the right padding OK
but when the txt file is viewed in TEXTPAD it does not have a line feed
character in position 179 and after all the records are shown there appears
to be loads of space after the records have finished which the system does
not like.
I am building the file in VB by copying a function in one cell to the
others - it looks like:
=CONCATENATE(quot;1ROYTEST2.TXTquot;,TEXT(Import!$G$1,quot;000 00000quot;),TEXT(Import!$H$1,quot;000000quot;),RIGHTPAD(quot;
quot;,152),D9)
So how do I build your CHR(10) for example into this and is there another
one to ensure the complete file ends at the last record? Where do I find
documentation on these CHR characters?
Thanks for your patience!
Mervyn
quot;David McRitchiequot; gt; wrote in message
...
gt; Your question is ambiguous both in what you get and what you are
gt; comparing
gt; it to, and even whether it makes a difference or not to your usage.
gt;
gt; In fact, I don't even know if you are trying to create a CSV file, text
gt; in columns,
gt; or just writing records i.e. HTML records..
gt;
gt; From with VBA you can use xlCR or CHR(13) for carriage return,
gt; xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)amp;CHR(10) for
gt; CRLF.
gt; if producing one long string.
gt; ---
gt; HTH,
gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt;
gt; quot;Mervyn Thomasquot; gt; wrote in message
gt; ...
gt;gt; I am creating a text file within excel and I do not know how to create
gt;gt; what
gt;gt; I think is needed and that is an end of line character for each line.
gt;gt; The
gt;gt; text is all in column A, one record in each cell. The text file that
gt;gt; excel
gt;gt; creates appears to be
gt;gt; different to the model I am using in Notepad at the end of the line.
gt;gt; Anyone any experience in this?
gt;gt;
gt;gt;
gt;
gt;
gt;
In your worksheet formula you would include CHAR(10)
-- the use of CHR(10) would be for the same but for use in Visual Basic
to make sure you have right padding
=LEFT(A1amp; REPT(quot; quot;,178),178)
which would not require checking if you are below or past 178 characters.
You would include the equivalent of the above in your formula.
However since your purpose is to create a text file, it would probably be better
to write the file directly with VBA macro code.
BTW, Most people would not use the concatenate worksheet formula
but instead do their concatenation like this
H4: =A4 amp; quot; quot; amp; B4 amp; quot; quot; amp; C4
instead of
H4: =CONCATENATE(A4,quot; quot;, B4, quot; quot;, C4)
means exactly the same thing.:
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm
quot;Mervyn Thomasquot; gt; wrote in message ...
gt; Sorry I was not understood. I am seeking to produce an excel file where
gt; each record is in A1,A2 (one record per cell) etc and then saving the file
gt; in text format to be able to import the records into a banking system. Each
gt; record needs to be right padded with blanks to produce a record that is 178
gt; characters wide. I have a user function that produces the right padding OK
gt; but when the txt file is viewed in TEXTPAD it does not have a line feed
gt; character in position 179 and after all the records are shown there appears
gt; to be loads of space after the records have finished which the system does
gt; not like.
gt;
gt; I am building the file in VB by copying a function in one cell to the
gt; others - it looks like:
gt; =CONCATENATE(quot;1ROYTEST2.TXTquot;,TEXT(Import!$G$1,quot;000 00000quot;),TEXT(Import!$H$1,quot;000000quot;),RIGHTPAD(quot;
gt; quot;,152),D9)
gt;
gt; So how do I build your CHR(10) for example into this and is there another
gt; one to ensure the complete file ends at the last record? Where do I find
gt; documentation on these CHR characters?
gt;
gt; Thanks for your patience!
gt; Mervyn
gt;
gt;
gt;
gt; quot;David McRitchiequot; gt; wrote in message
gt; ...
gt; gt; Your question is ambiguous both in what you get and what you are
gt; gt; comparing
gt; gt; it to, and even whether it makes a difference or not to your usage.
gt; gt;
gt; gt; In fact, I don't even know if you are trying to create a CSV file, text
gt; gt; in columns,
gt; gt; or just writing records i.e. HTML records..
gt; gt;
gt; gt; From with VBA you can use xlCR or CHR(13) for carriage return,
gt; gt; xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)amp;CHR(10) for
gt; gt; CRLF.
gt; gt; if producing one long string.
gt; gt; ---
gt; gt; HTH,
gt; gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt; gt;
gt; gt; quot;Mervyn Thomasquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; I am creating a text file within excel and I do not know how to create
gt; gt;gt; what
gt; gt;gt; I think is needed and that is an end of line character for each line.
gt; gt;gt; The
gt; gt;gt; text is all in column A, one record in each cell. The text file that
gt; gt;gt; excel
gt; gt;gt; creates appears to be
gt; gt;gt; different to the model I am using in Notepad at the end of the line.
gt; gt;gt; Anyone any experience in this?
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
I'm beginning to think that Excel2000 does not save text properly but seems
to pad out each line with a number of quot;tab rightsquot; both for each record and
after the last record as blank lines. I have tried the CHAR(10) as right
now I am trying to get the file right manually(in Excel) rather than VB.
This does NOT put an end of line character in the text file. Would VB work
any different in saving the text file format?
I have now got a work around by stripping out the quot;tab horizontalsquot; in
TEXTPAD (replacing Hex(9)with blanks) but I would like to understand what
Excel is doing!
Thanks for your continuing help
Mervyn
quot;David McRitchiequot; gt; wrote in message
...
gt; In your worksheet formula you would include CHAR(10)
gt; -- the use of CHR(10) would be for the same but for use in Visual Basic
gt;
gt; to make sure you have right padding
gt; =LEFT(A1amp; REPT(quot; quot;,178),178)
gt; which would not require checking if you are below or past 178 characters.
gt; You would include the equivalent of the above in your formula.
gt;
gt; However since your purpose is to create a text file, it would probably
gt; be better
gt; to write the file directly with VBA macro code.
gt;
gt; BTW, Most people would not use the concatenate worksheet formula
gt; but instead do their concatenation like this
gt; H4: =A4 amp; quot; quot; amp; B4 amp; quot; quot; amp; C4
gt; instead of
gt; H4: =CONCATENATE(A4,quot; quot;, B4, quot; quot;, C4)
gt; means exactly the same thing.:
gt; ---
gt; HTH,
gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt;
gt; quot;Mervyn Thomasquot; gt; wrote in message
gt; ...
gt;gt; Sorry I was not understood. I am seeking to produce an excel file where
gt;gt; each record is in A1,A2 (one record per cell) etc and then saving the
gt;gt; file
gt;gt; in text format to be able to import the records into a banking system.
gt;gt; Each
gt;gt; record needs to be right padded with blanks to produce a record that is
gt;gt; 178
gt;gt; characters wide. I have a user function that produces the right padding
gt;gt; OK
gt;gt; but when the txt file is viewed in TEXTPAD it does not have a line feed
gt;gt; character in position 179 and after all the records are shown there
gt;gt; appears
gt;gt; to be loads of space after the records have finished which the system
gt;gt; does
gt;gt; not like.
gt;gt;
gt;gt; I am building the file in VB by copying a function in one cell to the
gt;gt; others - it looks like:
gt;gt; =CONCATENATE(quot;1ROYTEST2.TXTquot;,TEXT(Import!$G$1,quot;000 00000quot;),TEXT(Import!$H$1,quot;000000quot;),RIGHTPAD(quot;
gt;gt; quot;,152),D9)
gt;gt;
gt;gt; So how do I build your CHR(10) for example into this and is there another
gt;gt; one to ensure the complete file ends at the last record? Where do I find
gt;gt; documentation on these CHR characters?
gt;gt;
gt;gt; Thanks for your patience!
gt;gt; Mervyn
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;David McRitchiequot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Your question is ambiguous both in what you get and what you are
gt;gt; gt; comparing
gt;gt; gt; it to, and even whether it makes a difference or not to your usage.
gt;gt; gt;
gt;gt; gt; In fact, I don't even know if you are trying to create a CSV file,
gt;gt; gt; text
gt;gt; gt; in columns,
gt;gt; gt; or just writing records i.e. HTML records..
gt;gt; gt;
gt;gt; gt; From with VBA you can use xlCR or CHR(13) for carriage return,
gt;gt; gt; xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)amp;CHR(10) for
gt;gt; gt; CRLF.
gt;gt; gt; if producing one long string.
gt;gt; gt; ---
gt;gt; gt; HTH,
gt;gt; gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt;gt; gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt;gt; gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt;gt; gt;
gt;gt; gt; quot;Mervyn Thomasquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt;gt; I am creating a text file within excel and I do not know how to create
gt;gt; gt;gt; what
gt;gt; gt;gt; I think is needed and that is an end of line character for each line.
gt;gt; gt;gt; The
gt;gt; gt;gt; text is all in column A, one record in each cell. The text file that
gt;gt; gt;gt; excel
gt;gt; gt;gt; creates appears to be
gt;gt; gt;gt; different to the model I am using in Notepad at the end of the line.
gt;gt; gt;gt; Anyone any experience in this?
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;
Depends on what you are using
Char(10) is LF line-feed used in Excel and a lot of Microsoft things
Char(13) is CR Carriage-return used in Unix
Char(13)amp;Char(10) is CRLF used in text files, and was what was universal
until some systems decided they could save a byte.
Char(9) is TAB
For VBA you can look at Chip Pearson's
Exporting to Text Files
www.cpearson.com/excel/imptext.htm#Export
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm
quot;Mervyn Thomasquot; gt; wrote in message ...
gt; I'm beginning to think that Excel2000 does not save text properly but seems
gt; to pad out each line with a number of quot;tab rightsquot; both for each record and
gt; after the last record as blank lines. I have tried the CHAR(10) as right
gt; now I am trying to get the file right manually(in Excel) rather than VB.
gt; This does NOT put an end of line character in the text file. Would VB work
gt; any different in saving the text file format?
gt;
gt; I have now got a work around by stripping out the quot;tab horizontalsquot; in
gt; TEXTPAD (replacing Hex(9)with blanks) but I would like to understand what
gt; Excel is doing!
gt;
gt; Thanks for your continuing help
gt; Mervyn
gt;
gt; quot;David McRitchiequot; gt; wrote in message
gt; ...
gt; gt; In your worksheet formula you would include CHAR(10)
gt; gt; -- the use of CHR(10) would be for the same but for use in Visual Basic
gt; gt;
gt; gt; to make sure you have right padding
gt; gt; =LEFT(A1amp; REPT(quot; quot;,178),178)
gt; gt; which would not require checking if you are below or past 178 characters.
gt; gt; You would include the equivalent of the above in your formula.
gt; gt;
gt; gt; However since your purpose is to create a text file, it would probably
gt; gt; be better
gt; gt; to write the file directly with VBA macro code.
gt; gt;
gt; gt; BTW, Most people would not use the concatenate worksheet formula
gt; gt; but instead do their concatenation like this
gt; gt; H4: =A4 amp; quot; quot; amp; B4 amp; quot; quot; amp; C4
gt; gt; instead of
gt; gt; H4: =CONCATENATE(A4,quot; quot;, B4, quot; quot;, C4)
gt; gt; means exactly the same thing.:
gt; gt; ---
gt; gt; HTH,
gt; gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt; gt;
gt; gt; quot;Mervyn Thomasquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Sorry I was not understood. I am seeking to produce an excel file where
gt; gt;gt; each record is in A1,A2 (one record per cell) etc and then saving the
gt; gt;gt; file
gt; gt;gt; in text format to be able to import the records into a banking system.
gt; gt;gt; Each
gt; gt;gt; record needs to be right padded with blanks to produce a record that is
gt; gt;gt; 178
gt; gt;gt; characters wide. I have a user function that produces the right padding
gt; gt;gt; OK
gt; gt;gt; but when the txt file is viewed in TEXTPAD it does not have a line feed
gt; gt;gt; character in position 179 and after all the records are shown there
gt; gt;gt; appears
gt; gt;gt; to be loads of space after the records have finished which the system
gt; gt;gt; does
gt; gt;gt; not like.
gt; gt;gt;
gt; gt;gt; I am building the file in VB by copying a function in one cell to the
gt; gt;gt; others - it looks like:
gt; gt;gt; =CONCATENATE(quot;1ROYTEST2.TXTquot;,TEXT(Import!$G$1,quot;000 00000quot;),TEXT(Import!$H$1,quot;000000quot;),RIGHTPAD(quot;
gt; gt;gt; quot;,152),D9)
gt; gt;gt;
gt; gt;gt; So how do I build your CHR(10) for example into this and is there another
gt; gt;gt; one to ensure the complete file ends at the last record? Where do I find
gt; gt;gt; documentation on these CHR characters?
gt; gt;gt;
gt; gt;gt; Thanks for your patience!
gt; gt;gt; Mervyn
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;David McRitchiequot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Your question is ambiguous both in what you get and what you are
gt; gt;gt; gt; comparing
gt; gt;gt; gt; it to, and even whether it makes a difference or not to your usage.
gt; gt;gt; gt;
gt; gt;gt; gt; In fact, I don't even know if you are trying to create a CSV file,
gt; gt;gt; gt; text
gt; gt;gt; gt; in columns,
gt; gt;gt; gt; or just writing records i.e. HTML records..
gt; gt;gt; gt;
gt; gt;gt; gt; From with VBA you can use xlCR or CHR(13) for carriage return,
gt; gt;gt; gt; xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)amp;CHR(10) for
gt; gt;gt; gt; CRLF.
gt; gt;gt; gt; if producing one long string.
gt; gt;gt; gt; ---
gt; gt;gt; gt; HTH,
gt; gt;gt; gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; gt;gt; gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; gt;gt; gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Mervyn Thomasquot; gt; wrote in message
gt; gt;gt; gt; ...
gt; gt;gt; gt;gt; I am creating a text file within excel and I do not know how to create
gt; gt;gt; gt;gt; what
gt; gt;gt; gt;gt; I think is needed and that is an end of line character for each line.
gt; gt;gt; gt;gt; The
gt; gt;gt; gt;gt; text is all in column A, one record in each cell. The text file that
gt; gt;gt; gt;gt; excel
gt; gt;gt; gt;gt; creates appears to be
gt; gt;gt; gt;gt; different to the model I am using in Notepad at the end of the line.
gt; gt;gt; gt;gt; Anyone any experience in this?
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
留言列表
