Finding the length of proportional text strings

[Russ]

I need to combine two text cells in Excel into one character string with
filler dot characters in the middle to produce a character string of a fixed
length in inches that will end up in a Microsoft Word table. The fonts are
proportional and the same in both Excel and the Word table. For example
Left string - “AAAAAAâ€
Right string - “BBBBBâ€
Concatenated string with filler dots - “AAAAAA……BBBBBâ€
The only way I have found so far is to build the string putting one dot at
time in the middle until the string wraps in a cell who’s width is set equal
to the Word table cell width. By testing the height of the cell I can tell
when the cell wraps and the length has been exceeded. Is there a better way
to accomplish this?

 

[Keith74]

I need to combine two text cells in Excel into one character string with
filler dot characters in the middle to produce a character string of a fixed
length in inches that will end up in a Microsoft Word table. The fonts are
proportional and the same in both Excel and the Word table. For example
Left string - "AAAAAA"
Right string - "BBBBB"
Concatenated string with filler dots - "AAAAAA......BBBBB"
The only way I have found so far is to build the string putting one dot at
time in the middle until the string wraps in a cell who's width is set equal
to the Word table cell width. By testing the height of the cell I can tell
when the cell wraps and the length has been exceeded. Is there a better way
to accomplish this?

Public Sub CreateString()

Dim strString1 As String
Dim strString2 As String
Dim intRequiredLength As Integer
Dim strFinalString As String

strString1 = "AAAAAA"
strString2 = "BBBBBB"
intRequiredLength = 15

Do Until Len(strString1) + Len(strString2) = intRequiredLength
strString1 = strString1 & "."
Loop

strFinalString = strString1 + strString2

End Sub

hth

 

[Russ]

Unless I am mistaken your answer only works for fixed length fonts. The
problem is I am using proportional fonts where the length of the string is
character dependent.

 

[Keith74]

As far as i know the Len function returns a number based only on the
no of characters, irrespective of length of the character. Never used
proportional fonts so can't be sure

 

[Helmut Weber]

Hi Russ

I don't think there is much else you can do.
Even if some api functions like GetTextMetrics
which i am not familiar with,
would tell you how wide your text is,
you still would have to calculate the maximum width beforehand,
get the width of a space
und add as many spaces at once to get the perfect width.

I even doubt, whether adding spaces the way you do in Excel,
will always show the desired effect in Word.
Does it? Then I have once again learned something.

How about leaving it all to Word.
Wouldn't be any more elegant, though,
but then you would know, whether the text fits
according to Word's way of calculating it.

In Word you would use Range.ComputeStatistics(wdStatisticLines).
But, i'm sure, there was no need of telling you that.

Have a nice day.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Jon Peltier]

In general I'd guess that this would be very difficult. However, Word's tabs
let you use certain leader lines, which bail you out in this situation.

In the Word table, set up a tab in the cell with dots for the tab leader
line. From Excel, insert AAAAAA and the tab character and finally BBBBB.

- Jon

 

[Russ]

Hi Helmut,
Thanks for you reply. Actually I do leave it to Word when I construct the
finished product. However, all the text is entered in Excel over time and I
want to be sure everything fits before I, once a year, produce the finished
product.
The technique I use works quite well but it is rather kluggy. I guess I
will just have to live with it.
Thanks again.

 

[Russ]

Hi Jon,
Yes Word tabs do the job very nicely once I load my Excel text (without the
dots in the middle). The clumsy way I check in Excel does always manage to
make sure it fits when i get to Word.
Thanks for you help.