 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / April 2006How to Format Numeric Values in Bookmarks in Word Doc.
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 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. > Hi All, > [quoted text clipped - 35 lines] > Many Thanks, > HJ 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. ;-)  SignatureGreetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" 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). > Hi Jezebel, > [quoted text clipped - 25 lines] > from someone who wanted help on how to complain > about mildew in her living room. ;-) << 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 > Hi All, > [quoted text clipped - 35 lines] > Many Thanks, > HJ Hi Peter, what if the bookmarks are floating with changings of the surrounding text? SignatureGreetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" 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 > 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 > Hi Peter, > [quoted text clipped - 13 lines] > Thanks so much > HJ 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 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 > 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 [quoted text clipped - 10 lines] > Thaks again for you help, > HJ 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 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 > Hey, Thanks again. > [quoted text clipped - 18 lines] > Again I appreciate all your help with this. > HJ 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 The extra column sounds like a good workaround. If you want, despam my e-mail address pjj@KillmapSpjjnet.demon.co.uk , 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 > Thanks Peter, > [quoted text clipped - 7 lines] > > HJ Then I suppose the poster may decide to tell us. Peter Jamieson > Hi Peter, > > what if the bookmarks are floating with changings > of the surrounding text? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.