Problem encountered when saving worksheet as a text file

G

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top