How to tell number of spaces between values in saved text file fromthe original xls file

[peng.gong]

Hi,

I want to save an excel xls file as the format of "formatted text
(space delimited)".
My question is given the excel file (from which, i know the width of
each column, and the value of each range), how do I know the number of
spaces between any two neighboring values in the saved text file? (I
want to be very specific about that number) For example, if in the
original excel file, range("A1") = "0.123" and range("B1") = "abc",
what would be the number of spaces between the "0.123" and "abc" in
the text file?

For column width I can get in vba (columns(1).width), but I guess the
number of spaces apparently also depends on the width of the specific
content within a cell. For example, for a cell with the content
"0.123" or "abc", the width of the content could be (the width of a
single digit * 4 + the width of a decimal point) or (the width of a
letter * 3). It should also depend on the font/format/size of that
cell. After I know the width of the column and the width of the
content, the number of spaces between neighboring values should be a
function of the difference in the widths in excel. But I can't find
any websites/books which would give me this kind of information.

Thanks a lot, greatly appreciate any help or tips.

Just to clarify, I am more interested in the answer to my question
above, rather than a workaround such as saving the file first, then
manually increase/decrease the spaces in the text file.

 

[Joel]

The answer to your question: Count the spaces

I save an excel file in PRN format ("formatted text space delimited) and
found out that excel stripped all the control character out of the file. I
had the text colored and no coloring ended up in the saved file.

To make the data fixed width excel add spaces before each column and made
the text right aligned. to find the width is then simple. first count the
number of spaces. Then count the number of non-spaces. The total of the two
count is the column width size.

It is very simple to write a program that will extract these column widtths
from the file. Just look at the first line of data in the file and perform
the two counts I indicated above in a loop until you get to the end of the
line. Put the results of each count in a worksheet so you can save the
results.