String width in specified font/size when merging

[Seb]

Hello,

I am merging documents with fields from an Excel sheet.
I need to put some text (from Excel) into boxes pre-printed on the paper,
the boxes have fixed sizes.

The problem is that some datas in the Excel sheet doesn't fit in the boxes,
and because I am using non-fixed fonts the values of the datas are changing
the width of the text (MMMMM vs IIIII).

My goal is to do a macro (in Excel or Word) to tell me which records doesn't
fit in a XX centimeters box when using a specific font/size (and then I can
modify manually the datas in Excel when it's too large).

I searched how to get the real printed width (or any value with a fixed
ratio value/real_printed_width) of a string but I can't find any information
about it.

Thank you VERY VERY much in advance, ANY help is REALLY appreciated.
Seb.

PS: sorry for my bad English, it is not my first language.

 

[Myrna Larson]

In Visual Basic proper (not VBA), the property you would use is TextWidth.
That seems not to be available in Excel VBA or Word VBA, either.

IIRC, one suggestion is to put the text in a cell in a column that is
otherwise empty, then AutoFit the column and determine the width. That's a
real kludge, and would probably be slow, also.

 

[Seb]

The problem is that Excel and Word doesn't display the same text (in the
same font/size) identically.

Example:
If you put 40 "M" and 110 "I" in two cells under Excel in Times New Roman/12
the "M" line is greater than the "I" line. If you do the same under Word the
"I" line is greater than the "M" line.
When you print the two lines (both Excel and Word) the "I" line is greater
than the "M" line.

I don't know if it's a bug in Excel or if it's a "visual effect" but the
conclusion is that I can't trust the column width value.

On the MSDN the TextWidth method is used for Reports, so I need Visual Basic
+ Access to use it and I'm not sure that it is giving good values.

I continue searching, thank you for your suggestions.
Seb.

 

[Seb]

I remarqued something very strange.

In Word (2000 & 2003) the "I" line is greater than the "M" line when viewing
and printing.
In Excel when viewing the "M" line is greater but when printing the "I" line
is greater like in Word !!!

Buggy Excel