How to Format Numeric Values in Bookmarks in Word Doc.

[hareshusa]

Hi All,

I have a Word template with Bookmarks in it. I have linked that to an
Excel worksheet (which has my data) and I am populating Bookmarks on
the Template with VBA Code
based upon some conditions.

The data is already formated in Excel Worksheet but when I place them
in Bookmark, they loose the format.

The Numeric data in Excel cell are formated as "Accounting" type with 2
decimal places.

eg : $ 2,345.90 | $ 137,700.38

But when they are placed in Bookmarks, I only see them as :
137700.38
2345.90

So as you can see the $ sign is lost and they are not right align as
numeric fields should be.

Now I also tried to format them with "$ #,##0.00" , with that it
helped a bit because now they have $ sign but they are still not right
aligned:

They show like:

$ 137,700.38
$ 2,345.90

So they are not align the way I want them. I want to see them as:
$ 137,700.38
$ 2,345.90

Is it possible? any help would be really appreciated.

Many Thanks,
HJ

 

[Jezebel]

Instead of using bookmarks, use document properties and DocProperty fields.
Several advantages:

1. The values are easier to set from code:
myDoc.CustomDocumentProperties(PropertyName) = ...

2. DocProperty fields can have a format specification: { DOCPROPERTY \#
"$###,##0.00" PropertyName}

3. You can display the same property more than once in the document (as
opposed to having to set multiple bookmarks).

4. The displayed values can't be modified inadvertently.

 

[Helmut Weber]

Hi Jezebel,

still not right justified,
or am I missing something?

I thought about filling up the string
with leading spaces.
Two for proportional fonts for each digit and thousands seperator,
one for monospaced fonts for each digit and thousands seperator.

Like this, for monospaced fonts:

Dim sNum As Single
Dim sStr As String
Dim lMax As Long
Dim c As Long
lMax = 12 ' 12 characters
sNum = 14366634.06 ' that's what you get from Excel
sStr = Format(sNum, "#,###.00")
For c = Len(sStr) To 12
sStr = " " & sStr
Next
sStr = "$ " & sStr

Longest possible number must be known, of course.

By the way, about CV, there was a post lately,
from someone who wanted help on how to complain
about mildew in her living room. ;-)


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[Jezebel]

Why not use a right- or decimal tab?

The answer to the mildew problems is obvious: To every ?-consistent
recursive class ? of formulae there correspond recursive class signs r, such
that neither v Gen r nor Neg(v Gen r) belongs to Flg(?) (where v is the free
variable of r).

 

[Peter Jamieson]

<<
Now I also tried to format them with "$ #,##0.00" , with that it
helped a bit because now they have $ sign but they are still not right
aligned:
If you use a decimal tab between the $ and the # , e.g. using

\#"$,<tab>0.00"

where <tab> is actually just a tab character, and set the appropriate
decimal tabs in the paragraphs the numbers appear in (or if possible, in the
paragraph style), then you should get the layout you want.

Peter Jamieson

 

[Helmut Weber]

Hi Peter,

what if the bookmarks are floating with changings
of the surrounding text?

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[hareshusa]

Hi Peter,

When you say "If you use a decimal tab between the $ and the # , e.g.
using \#"$,<tab>0.00"

Where <tab> is actually just a tab character, and set the appropriate
decimal tabs in the paragraphs the numbers appear in (or if possible,
in the
paragraph style), then you should get the layout you want.
--------------------------------------------------------------------------------------------------------------------------------------------
The idea sounds great but I am new to VBA and not much familier with
WORD, If you could
explain in detail about decimal tab that would really help.

Appreciate your help, please let me know.
Thanks so much
HJ

 

[Peter Jamieson]

Then I suppose the poster may decide to tell us.

Peter Jamieson

 

[Peter Jamieson]

The idea sounds great but I am new to VBA and not much familier with

WORD, If you could
explain in detail about decimal tab that would really help.

To experiment, try the following:
a. open a new document.
b. open the Format|Tabs menu and set a decimal tab at (say) 3cm. It should
be obvious how to do that
c. select and copy the paragraph a few times
d. insert some fields, e.g.

{ SET A 123.45 }{ SET B 234.56 }
then in separate rows

{ REF A \#"$,<the tab character>0.00" }
{ REF B \#"$,<the tab character>0.00" }

(use ctrl-F9 to enter the special field braces {} and type the rest of the
text in between, and use Alt-F9 to toggle between "field code" view, and
"field result view")

e. Select the document using ctrl-A and press F9 to re-execute the fields.

f. if you really feel the need to see the VBA, switch on the macro recorder
while you do that lot.

See if that gets you close to the sort of thing you want. If you use
Jezebel's suggestion - putting the values into Document Properties and using
{ DOCPROPERTY } fields to display the results, all you should need to do is
leave the DOCPROPERTY fields in the document, ensure the paragraphs they are
in have the correct tabs, and populate the appropriate document properties
with the numbers you want to display. Alternatively, you could try using
Document variables (the code looks very similar) and { DOCVARIABLE } fields
but don't do that i you're putting the values in headers/footers in older
versions of Word.

Peter Jamieson

 

[hareshusa]

Thanks a lot Peter, but I am using the Bookmarks instead of Fields
and with your suggestion I tried to Insert the Decimal Tab in the Table
Cell which I populate with Numeric $ values.

Now I see that the Decimal points are all aligned but the "$" signs are
not aligned.

for eg: $ 1,123.00
$ 123.00

How do you align both now, "$" sign and Decimal point.

Thaks again for you help,
HJ

 

[Peter Jamieson]

A left-aligned tab before the $ should do it, then your decimal-aligned tab.

You could either put the first tab inside the format or before the bookmark.

Peter Jamieson

 

[hareshusa]

Hey, Thanks again.

This is what I did :
1) Deleted the current Bookmark from the Table Cell
2) Cleared all the Tab from the Table Cell
3) Format/Tabs Set the Left Aligned Tab to 0.5"
4) Inserted the Bookmark after the Left Aligned Tab
5) Changed the Tab to Decimal Tab, set it from ruler somewhere towards
right of the cell
6) Ran my VBA Code to Populate the Bookmark with format of "$#,##0.00"

Result shows :
$137,729.44
$22,566.92
$7,200.18

As you can see the Dollars ("$") signs are aligned but the Decimal
Points are not Aligned.
What I am doing wrong here ?

Again I appreciate all your help with this.
HJ

 

[Peter Jamieson]

Sorry, in a table cell a single decimal tab is "actioned" automatically,
i.e. it's as if the cell has an invisible tab at the beginning. So you need
to set another tab. As far as I can tell, you can use either
a. a left tab (say, at 0.1in) at the beginning, then a decimal tab (say, at
2cm) and a string consisting of, e.g. "<tab>$<tab>123.45" or
b. the decimal tab you want (say, at 2cm), another tab you're not going to
use (say, at 10cm), and a string consisting of e.g. "$<tab>123.45"

Peter Jamieson

 

[hareshusa]

Thanks Peter,

I tried all different ways of Decimal Tabs and Tab settings but either
I could align "$" sign or
Decimal Point. Any other solutions, please let me know.

I am now almost thinking of creating another column with only $ sign
and print the numbers in
a separate column.

HJ

 

[Peter Jamieson]

The extra column sounds like a good workaround. If you want, despam my
e-mail address (e-mail address removed) , send me what you have, and
I'll see if I can make any suggestions. I'm offline for a few days after
about 11a.m. (UK time) tomorrow.

Peter Jamieson