Actual dimensions for a string of text

[canadatct]

I am fetching information from an Access DB and populating this data to a Word doc. The data is for a hard cover phone directory of over 30,000 people. I am formatting the word document on the fly from within VB, but I am having trouble with one crucial measurement.

I need to find a way to get the actual dimensions of a string of text. I'm not talking about the length of a string in characters [e.g "len(myString)" ], I am wanting to know how many points that string will occupy when printed on the page. This value will need to change dynamically depending upon what font is being used, as well as the font size. If I have that calculation and it's accurate, I can properly format the rest of the document before sending the data for output.

I feel like I am close in my efforts, but I am getting nowhere with this. Before the formatting code occurs, I am opening a recordset and grabbing one phone number, which I'm using to perform the calculation. This is what I am currently using to calculate this measurement:

sngLength = InchesToPoints(Len(Right(rs!Phone1,8)))


Does anyone have a clue? Am I way off track? Can I simplify this process in any way?

Any help would be appreciated!!

Thanks in advance.

 

[RobBroekhuis]

I know you really don't want to go through Excel to do this, but maybe you can find a similar way in Access. Here's a function that will do it in Excel:

Function pointwidth(s As String) As Double
Range("A1&quot = "w" & s
Columns("A&quot.AutoFit
pointwidth = Columns("A&quot.Width - 12
End Function

You'd have to make sure your cell A1 is formatted with the font and point size you need, and replace the 12 in the function return value assignment with the actual width of a column with a single "w". Maybe there is some similar roundabout way in Access where you can use an autofit method?


Rob

 

[VBAjedi]

Rob - Since I've never seen that approach anywhere else, methinks thou hast possibly learned something from ME! (thread707-538568) Now THAT'S a role reversal!

[LOL]



VBAjedi

 

[CajunCenturion]

Have you tried using the GetTextExtentPoint32 API? The API function calculates the actual length and width of a given string.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[VBAjedi]

Cajun,

But does your suggestion return an answer in Microsoft "points", and does it do so after considering the font, font size, and formatting?

By formatting A1 in Rob's code to match the formatting of the source cell, you can get a width calculation based on an exact match of all of those croteria.

VBAjedi

 

[MichaelRed]

Printer.TextWidth?

Printer.TextHeight?

What am I missing?

WHY woulud it need to be in "points"?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[VBAjedi]

Michael,
I'm not familiar with .TextWidth and .TextHeight, and I don't see them in VBA help. Are they Word-specific VBA, or were they introduced in a later Office version than mine (Office 97)? What unit of measurement is the value they return in?

Although canadatct doesn't say exactly WHY in his initial post, he specifically requests a way to find out "how many points that string will occupy". The Excel AutoFit approach that Rob suggested tells you just that.

canadatct, we need you to speak up here and let us know whether this answers your question or not (and if not, why). . .


VBAjedi

 

[CajunCenturion]

Yes, VBAJedi, the GetTextExtentPoint32 does take into account the font settings when calculating the string height and width. The first parameter to the API is the Device Context handle, and it is from that device context where the font settings are determined and used in the calcs. The return value is in pixels. You can convert that to twips by multiplying by 15, but, the MS use of twip is not accurate from the true definition of a twip, because if the twip were truely 1/20 of a point, with 72 points per inch, a 17 inch monitor would have more twips than a 15 inch monitor, but alas, such is not the case. Hence, what Microsoft calls a twip is not what a professional Printer would call a twip.

The .TextWidth and .TextHeight methods are not exposed in VBA except from a Report object. In VB6, these methods can be use on a form, and some controls, such as a text grid.

But, you should also note that these two methods are nothing more that wrappers to the GetTextExtentPoint32 API.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Sorry, not text grid, I meant the Flexgrid control, which does, at a cell by cell level, provide the .TextWidth and .TextHeight methods.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[RobBroekhuis]

Jedi,
This is a case of great minds think alike - I don't think I read your original thread (or maybe I did and it subliminally stuck ). At any rate, the original questioner appears to have disappeared.


Rob