Problem encountered when saving worksheet as a text file

[Ginge1968]

Hi,
not sure if anyone can help on this.
I have a worksheet that contains a header row.
When saving the data in this worksheet as a tab delimited file, excel would
normally add the delimiters to equal the longest record (row of data).
E.g.
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8
a b c d e f
a b c d e f
a b c d e f
a b c d e f
a b c d e f

Saving as a Tab Delimited or Comma Delimited file would result in the
following .txt file (I have used comma delimited format as this is easier to
demostrate the problem!)

Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,

This works whether there is a header row or not. If row 3 is the longest
record, then the delimiters will be added to equal the longest row for the
rows before and after the longest record.

However, the problem I have encountered is this only works for the first 15
rows.
From row 16, no delimiters are added after the last cell with any data in
for that row.

Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f,,
a,b,c,d,e,f
a,b,c,d,e,f
a,b,c,d,e,f
a,b,c,d,e,f

This does not happen when saving the file as part of a VB macro.

Can anyone explain why this is happening and whether anything can be done
about it. I need the delimiters for all rows in the file as it is being
loaded into an application that requires a fixed length record.

Thanks

 

[Pete_UK]

This article describes the problem:

http://support.microsoft.com/default.aspx?scid=77295

The workaround suggested is to ensure that you have some data (eg
space) in the final column.

Hope this helps.

Pete

 

[Ginge1968]

Hi Pete_UK,

thanks for this. I have been hunting around to see if this was a known bug,
but unless you word the search correctly, you've got no chance. We were
trying to avoid having to have a column denoting the EOR, but seems we do not
have a choice...

Cheers
Doug

 

[Pete_UK]

Glad to be of help - thanks for feeding back.

Pete