Word, Excel. String Width Partners nz dec01

[Neal Zimm]

Hello,
I am an intermediate VBA programmer using Excel. I posted the questions in
the Excel Background section at the end of this posting.

After reading some Word postings here re: characters to width in inches
calculations, it appears possible to do what I need in Word. I've done NO VBA
in word, and don't know the data model at all. And I'm assuming I can
write a called Sub in Excel that will open a word document in another window.

What are your comments on these 'steps' for a potential method? regarding
CAN IT BE DONE?

1. Open Excel workbook, existing App processing
1a. Make character string, How many rows are needed to hold a string?
1a.1 1st time, open word doc, hide an not active window from user.

2. Use Word to calc its physical width.
2a. find a way to pass font size and font name to doc from Excel.
2b. find a way to write text string from Excel as 1 sentence to MSWord
2.B.1 user word to calc witdth of string in inches.
2c. get width in inches from word into excel ??
2d. clear sentence for doc, end of word 'call'.
2d. Do what's needed in Excel with the width.
3. repeat from step 1a as needed, if word doc is open, use same doc.
4. Close word doc and window when no more 1a string width needs.


Excel Background:
The worksheets in my app must be printed on paper about 7 inches wide in a
font size of at least 18 or 20 to be read in very dim lighting. Column
widths are fixed in the worsheet.

If a macro generated text string does not 'fit' into the designated cells
in 1 row, I use more than 1 row to hold the text after spliting it into
'words'.

I'm trying to AVOID building a function to return the width of a character
string in inches, for a few font sizes and 1 to 3 font names.

.Shrinktofit on its own won't help.

1. I can't find a way to test, If OneCell.Shrinktofit = true then Did
Excel actually shrink the cell contents ? This would help.

2. Is a call available to MSword that returns the width in inches of a
string, where its input is the font name and size of the character or
characters? (It's gotta be there, but is it available to mortals? I've also
posted this on the Word bulletin board.)

3. What might other 'stuff' be that I might be able to use?

Thanks.

 

[macropod]

Hi Neal,

Whilst is is possible to calculate string widths in Word, that might no help you much, since it's quite possible that Word and Excel
will calculate what 'fits' a cell will differ. In Excel, for example, what 'fits' can even vary according to your zoom %.

If all you need to do is to wrap the Excel text to the next line if it's too long the fit the available width, is there a reason yoy
can't use the 'wrap text' cell formatting property? If there is a reason this won't do (eg the overflow text has go in a cell on the
next row, perhaps the best way to test your string width for Excel's purposes is to autofit the column width and test the fitted
width against your target width. If it's less, simply re-set to the target width. If it's more, then trim characters off until it
fits and put the excess into the cell on the next row - repeat the process until everything fits.

 

[Neal Zimm]

Thank you MacroPod. It's amazing how brains work. I'm embarrassed to tell
you for how long I'been thinking about this problem without coming up with
you autofit column, test before and after method.

It WILL work well, and within the context of what I have already coded, will
work simply.
--
Neal Z

Hi Neal,

Whilst is is possible to calculate string widths in Word, that might no help you much, since it's quite possible that Word and Excel
will calculate what 'fits' a cell will differ. In Excel, for example, what 'fits' can even vary according to your zoom %.

If all you need to do is to wrap the Excel text to the next line if it's too long the fit the available width, is there a reason yoy
can't use the 'wrap text' cell formatting property? If there is a reason this won't do (eg the overflow text has go in a cell on the
next row, perhaps the best way to test your string width for Excel's purposes is to autofit the column width and test the fitted
width against your target width. If it's less, simply re-set to the target width. If it's more, then trim characters off until it
fits and put the excess into the cell on the next row - repeat the process until everything fits.

--
Cheers
macropod
[MVP - Microsoft Word]

Hello,
I am an intermediate VBA programmer using Excel. I posted the questions in
the Excel Background section at the end of this posting.

After reading some Word postings here re: characters to width in inches
calculations, it appears possible to do what I need in Word. I've done NO VBA
in word, and don't know the data model at all. And I'm assuming I can
write a called Sub in Excel that will open a word document in another window.

What are your comments on these 'steps' for a potential method? regarding
CAN IT BE DONE?

1. Open Excel workbook, existing App processing
1a. Make character string, How many rows are needed to hold a string?
1a.1 1st time, open word doc, hide an not active window from user.

2. Use Word to calc its physical width.
2a. find a way to pass font size and font name to doc from Excel.
2b. find a way to write text string from Excel as 1 sentence to MSWord
2.B.1 user word to calc witdth of string in inches.
2c. get width in inches from word into excel ??
2d. clear sentence for doc, end of word 'call'.
2d. Do what's needed in Excel with the width.
3. repeat from step 1a as needed, if word doc is open, use same doc.
4. Close word doc and window when no more 1a string width needs.


Excel Background:
The worksheets in my app must be printed on paper about 7 inches wide in a
font size of at least 18 or 20 to be read in very dim lighting. Column
widths are fixed in the worsheet.

If a macro generated text string does not 'fit' into the designated cells
in 1 row, I use more than 1 row to hold the text after spliting it into
'words'.

I'm trying to AVOID building a function to return the width of a character
string in inches, for a few font sizes and 1 to 3 font names.

.Shrinktofit on its own won't help.

1. I can't find a way to test, If OneCell.Shrinktofit = true then Did
Excel actually shrink the cell contents ? This would help.

2. Is a call available to MSword that returns the width in inches of a
string, where its input is the font name and size of the character or
characters? (It's gotta be there, but is it available to mortals? I've also
posted this on the Word bulletin board.)

3. What might other 'stuff' be that I might be able to use?

Thanks.