none
Numeric formatting for Quick Parts from Sharepoint 2010 RRS feed

  • Question

  • Word 2010 and Sharepoint 2010 are not playing nicely with each other (sort of)...

    Here's the situation:  I have a Word template document that contains some Quick Parts corresponding to numeric Sharepoint fields in a document library.  The Sharepoint fields are formatted to show thousands with a comma, e.g. 1,000,000.  While in Word with the Document Information Panel showing I can type a value into a numeric field and it appears correctly in the DIP.  But the corresponding Quick Part within the Word document just appears as 1000000, i.e. without the correct comma separation.  When I look at the properties of the Quick Part (highlight the Quick Part, click the Developer tab and click Properties), Word appears to be treating the Content Control as Text (it's applying the "Default Paragraph Style" and all the alternative styles are for text). 

    I'm wondering if I can do something to the underlying XML to fix this.  Here is the XML for one of the Quick Parts for which I'm having this problem (I think):

    I tested putting the formatting that I need into a "PageNum" field that I inserted in the Header of another document and found this section of XML that contained the formatting code,\# $#,##0.00 :

    Is there a way to either change the type of the Quick Part so Word recognizes it as a numeric or add some sort of XML to do the formatting I need?  Or am I totally going down the wrong path? 

    Thanks in advance again for any help you can give me.  Carol.

    Monday, April 1, 2013 10:40 PM

All replies

  • Hi Carol

    I don't think there's anyway to apply numeric formatting to a content control, the same way you can do to a field.

    Speaking strictly from the Word APIs point of view, which is the main topic of this forum, you could use an EVENT in a VBA project in the document to track the ContentControlBeforeStoreUpdate event insert the commas into the text.

    XML can define number formatting for numeric fields (which appears to be what you have at the DIP end), but that's applied to the stored text when an XML parser displays the XML. Word doesn't use this when displaying CustomXMLPart content in a content control bound to an element.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, April 2, 2013 10:12 AM
    Moderator
  • Thanks, Cindy.  That's what I was worried about.  I know the users wil want the formatting and I also know I have to keep a connection between the Content Controls in the DIP and the document so I'll have to figure out some sort of workaround, maybe some sort of combination of copying and bookmarks. 

    If anyone has any creative ideas, I'd appreciate hearing them.  Thanks.  Carol.

    Tuesday, April 2, 2013 1:27 PM
  • (I posted this question on the Word for Developers format and received the reply that what I'm looking for wasn't doable using the Word API which made sense.  I thought that the users of this group might be able to look at my problem from a different perspective and, hopefully, provide a way for me to figure out my problem.  I hope no one counts this as cross posting because that isn't how I meant it.)

    Word 2010 and Sharepoint 2010 are not playing nicely with each other (sort of)...

    Here's the situation: I have a Word template document that contains some Quick Parts corresponding to numeric Sharepoint fields in a document library. The Sharepoint fields are formatted to show thousands with a comma, e.g. 1,000,000. While in Word with the Document Information Panel showing I can type a value into a numeric field and it appears correctly in the DIP. But the corresponding Quick Part within the Word document just appears as 1000000, i.e. without the correct comma separation. When I look at the properties of the Quick Part (highlight the Quick Part, click the Developer tab and click Properties), Word appears to be treating the Content Control as Text (it's applying the "Default Paragraph Style" and all the alternative styles are for text).

    I'm wondering if I can do something to the underlying XML to fix this. Here is the XML for one of the Quick Parts for which I'm having this problem (I think):

    I tested putting the formatting that I need into a "PageNum" field that I inserted in the Header of another document and found this section of XML that contained the formatting code,\# $#,##0.00 :

    Is there a way to either change the type of the Quick Part so Word recognizes it as a numeric or add some sort of XML to do the formatting I need? Or am I totally going down the wrong path?

    Thanks in advance again for any help you can give me. Carol.

    Tuesday, April 2, 2013 2:53 PM
  • Hi Carol

    Will a macro solution in the Word document work for you? Using the event I mentioned would be the simplest, I think...


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, April 2, 2013 3:25 PM
    Moderator
  • It might if you can give me some help on how to work with events within a Word macro.  I am already using a macro that copies a table from Excel, pastes it into the Word document, copies the values from the table into the Content Controls and then deletes the links to the Excel workbook.  Here's the part that copies the values from the pasted table into different Content Controls (the table is formatted so the ControlName matches the name/tag of a Content Control):

    Does the ContentControlBeforeStoreUpdate event happen right after the Item.Range.Text line or after the End If line?  Or do I need to create a new VSTO type of things to replace this macro, or at least part of it? (I've never gone the VSTO route before...).  I'd appreciate seeing any specifics you can give me. 

    Thanks again, Cindy.

    Tuesday, April 2, 2013 5:06 PM
  • As far as the Excel table thing is concerned, I posted a suggestion in the conversation you started about that problem. What I didn't say in there is that the code I suggested needs to be in the "ThisDocument" module in the document itself, although I expect there are other ways to do it even without VSTO.

    Peter Jamieson

    Tuesday, April 2, 2013 7:03 PM
  • Thanks, Peter.  Actually I chickened out and setup my macro so that links are created, then the links are broken and then deleted from the Word document each time the macro runs.  I added a macro button to the QAT that goes along with the document itself.

    I just checked the macro code and found that it is in Modules (which I guess is the default) instead of in ThisDocument.

    Thanks again.  Carol.

    Tuesday, April 2, 2013 9:08 PM
  • Smart move IMO, since the OLE events don't seem to be trappable.

    My comment was more to do with the fact that I hadn't mentioned where the event handling code needs to be. If you do not already know...

    If your document is a .docm, the event code for Custom XML store events and Content Control events needs to be in ThisDocument. In that case, if you open up the ThisDocument module, select Document in the dropdown at the top left of the code area, and select the appropriate event name in top right dropdown, VBE will insert the appropriate Sub defiinition and you can take it from there.  

    If you are relying on a .dotm or an Addin, I'd have to look further.


    Peter Jamieson

    Tuesday, April 2, 2013 9:51 PM
  • Thanks, Peter, I never knew what the ThisDocument module was for because all of the regular macros I create automatically get placed under Modules into a NewMacros object.  I see what you mean now about where to put custom code that's triggered by an event.  What other type of code goes into ThisDocument rather than NewMacros?

    Thanks for helping my long term education.  :-)  Carol.

    Wednesday, April 3, 2013 1:22 PM
  • Yes, by default when you record a new macro it goes in the Normal template's "NewMacros" module. In that case, Word creates the module if it doesn't already exist.

    The document's ThisDocument module is (primarily) for the event handlers for "Document-level" events (you'll see them listed on the dropdown when you select "Document". Application-level events such as the MailMerge events are handled in a different way.

    However, the ThisDocument module would also be the place for event handlers for any activeX objects, such as the old ActiveX Forms Controls, in the document surface. You don't generally see that much any more (deprecated/supposed to use content controls instead/poor or no support on 64-bit, no support on Mac, etc.), but if you put one of those things on the document surface, you'll see an entry for it in the left-hand dropdown in the ThisDocument module pane, and when you select it you'll see the associated list of events.

    (Just for the sake of completeness, if you develop using .NET/VSTO and put Windows Form objects on the document surface, they are in fact placed inside ActiveX wrappers, and therefore such objects would also appear in the list of object in ThisDocument if you happened to use VBE to look at such a document. But of course you wouldn't use the VBE to program events for such objects.)

    Not sure that is a complete list of things that need to be in ThisDocument. ThisDocument can also hold ordinary Subs and Functions but it's probably not the best place for them.


    Peter Jamieson

    Wednesday, April 3, 2013 2:20 PM
  • Hi Carol

    Are you all set now, or was the discussion with Peter a side-track that hasn't moved you forward on the topic under discussion?


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, April 3, 2013 5:13 PM
    Moderator
  • All: This is the link to the discussion to which Carol refers:

    http://social.msdn.microsoft.com/Forums/en-US/worddev/thread/3cffbf4e-4c95-456f-9bd5-676fe91cb52f

    Carol: If this formatting has to be done dynamically, when the user changes a value in the DIP, then the Open XML SDK will not be of any help. It only works on closed documents, not documents open in Word. At what point should this number formatting occur, and what should trigger it?


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, April 3, 2013 5:45 PM
    Moderator
  • Thanks again, Cindy.  I've answered your question on my Word Developer forum post discussion (the link you provide) but I'll answer it here too -- I have a macro that copies values from a linked Excel table into Content Controls so the formatting needs to be done after the copying has completed.  If it's possible for the copying to trigger it, that would be good but I don't know if that's something that can be captured.

    Thanks.  Carol.

     
    Wednesday, April 3, 2013 8:37 PM
  • Thanks for asking, Cindy.  The side discussion with Peter was just that and I'd still appreciate learning how I can use the ContentControlBeforeStoreUpdate event to format the numeric values within the Content Controls after the values have been copied from the linked fields (please see my post on 4/2 @ 5:06 p.m. above for detailis about what I have included in my macro). 

    Carol.

    Wednesday, April 3, 2013 8:38 PM
  • <<
    Does the ContentControlBeforeStoreUpdate event happen right after the Item.Range.Text line
    >>

    Yes. (You can put Debug.Print "1" etc. lines into your code to help you see the sequence of events. In this case I would expect the following sequence:

    Set the value of the cc using Item.Range.Text
    Execute 
    Document_ContentControlBeforeStoreUpdate
    Execute Document_ContentControlBeforeContentUpdate if it is there.

    The thing about these events is that you are passed the ContentControl, and the "Content" (i.e. text value). So in the simplest possible case (all the ContentControls that are connected to the data store have the same numeric type and need to be processed the same way) the "BeforeStoreUpdate" method's content could be as simple as

    Content = CStr(CDbl(Content))

    and the "BeforeContentUpdate" could be something like 

    Content = FormatNumber(Content)

    But the first of those certainly isn't robust, and you might prefer to parse the number properly and deal with formatting errors etc.

    If some controls are supposed to contain numeric data and others non-numeric, you'll need to differentiate between the two. If you know the names of the numeric fields, you can obviously hard code them in some way. Otherwise, you can get a certain amount of infromation about the expected data types by iterating through the Document's ContentTypeProperties collection (which contains objects of type Office.MetaProperty). For any more detail than that, you have to start looking at the XML that SharePoint creates. There are some clues about how to do that in my posts in the following conversation - I think I got a little further than that at the time, but not far.

    http://answers.microsoft.com/en-us/office/forum/office_2010-word/set-default-filename-from-dip-values/1741650d-0c7d-4ed7-95ba-b2fe212f27aa



    Peter Jamieson

    Thursday, April 4, 2013 2:18 PM
  • Hi Carol

    OK, the information you posted about the issue in the Open XML SDK forum, combined with the macro code, tells me you probably don't even need any events for this. As long as you don't have to do anything if the user starts typing in the content controls or the DIP, you can make the formatting part of the macro that's bringing in the values.

    In that case, look at Peter's suggestion: Content = FormatNumber(Content)

    You want to use ControlValue from your macro, here. You can look up FormatNumber in the VBA Help. Just put this in after ControlValue= Left(....


    Cindy Meister, VSTO/Word MVP, my blog


    Thursday, April 4, 2013 5:31 PM
    Moderator
  • Hi Carol

    If the discussion in the Word forum provides you with the needed assistance, we should remove this discussion from the Open XML SDK forum as the Open XML SDK can't provide you with the answer you need (it doesn't work in VBA macros).


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, April 4, 2013 5:32 PM
    Moderator
  • Thanks to you both, Cindy and Peter.  I'm not feeling well today but hopefully I'll get a chance to try Peter's suggestions tomorrow or, at least, over the weekend.

    Carol.

    Thursday, April 4, 2013 5:33 PM
  • I'm okay with removing this discussion, Cindy.  How exactly do I go about doing that?  Thanks.  Carol.
    Thursday, April 4, 2013 5:34 PM
  • I finally was able to test your "FormatNumber(content)" suggestion, Peter, and it worked GREAT (I got commas in my numbers contained in Content Controls) ...except for one thing -- The fields I am working with in Sharepoint are numeric and they choke on anything other than numbers.  So after I apply FormatNumber() I can't save my Word document and its Sharepoint fields back into the Sharepoint Document Library. 

    Do you think there's a way to have the fields that need commas not be the real content controls but, instead, somehow feed just the number themselves back into the content controls (which would need to be hidden) and, therefore, back into Sharepoint? 

    I certainly hope that's possible.  Thanks for any ideas from anyone.  Carol.

    Tuesday, April 30, 2013 6:35 PM
  • A pity. My updates are going back into SharePoint OK here. My best guess right now is that the way my simple test numeric columns are defined on SharePoint allows that but yours don't. So to compare notes, my test is using Word 2010, SP2010, and the column is defined on SP with the default settings (I think).
     - Number (1, 1.0, 100)
     - Require that this column contains information: No
     - Enforce unique values: No
     - No min or max
     - Number of decimal places: Automatic
     - Default value: Number, but left blank
     - No additional column validation is defined.

    How about yours?


    Peter Jamieson

    Wednesday, May 1, 2013 7:29 AM
  • My columns are defined exactly the same as yours, Peter.  They are Site Columns, however, and are being used as part of a Custom Content Type.  I wouldn't think that would make a difference but...as my team says these days "Sharepoint is weird". 

    If you think that being a site column would make a difference, I can create a regular column, use it outside of a custom content type, and test things that way.  Let me know if you think that might be useful.  Thanks.

    Carol.

    Wednesday, May 1, 2013 1:25 PM
  • It works here with a site column I just created, but I'm wondering about the Custom Content Type - in this case, is it
     a. "just" a Word template that has been set up as the template for a Document Library (or some such) or 
     b. is there also a custom InfoPath DIP, or
     c. something else?

    I'm wondering if it's (b) (in fact creating such a DIP was one of the suggestions I was going to make as a possible way forward. The other one would have to be done server-side by someone into SharePoint server programming, and would use an Event Receiver to modify the value when Sharepoint saved the item into the Library. I just tried that here and established that it was at least feasible, but it's another area I'm not familiar with, so don't know how easy it would be to set up if you need this for lots of columns).





    Peter Jamieson

    Wednesday, May 1, 2013 3:06 PM
  • I hate to say this, Peter, but it's more complicated than either a, b, or c.  Here's the situation:  I created three custom content types -- Two that inherit from the Document content type (one that has a Word .docm template and one that has an Excel spreadsheet template) and one that inherits from the Document Set content type.  For document sets, as you probably know, any content types that are part of the document set can share all the columns of the document set.  Both my Word custom content and my Excel custom content type are part of the custom Document Set. 

    And my custom Document Set is what actually contains all the site columns that I'm working with in the Word template.

    (Ultimately I will be using a custom Infopath DIP but I'm sticking with the default one until I get this working.)

    I will create another document library that just has the custom Word content type template, and will add some of the problematic Site columns to that document library and see if I have the same problem.

    Thanks.  Carol.

    Wednesday, May 1, 2013 4:53 PM
  • Well, I setup a new document library using my custom Word content type and had the same problem.  I added a few of the site columns to the content type and added content controls for them to my document.  Then I ran a macro that had the FormatNumber() function and ended up with the numbers formatted correctly, but with red lines around the fields both in the document and in the DIP. 

    I am pretty sure that these red lines mean that I won't be able to save the document. :-(

    Do you get something different, Peter? 

    Carol.

    Wednesday, May 1, 2013 5:21 PM
  • I only see the red lines if I introduce a constraint (e.g. the number must be less than 100000) and violate that constraint. In fact I had to introduce a constraint just to make sure that I was getting red lines at all. I haven't yet tried to replicate the Document Set scenario that you described. I've tried to think if there's a Word, SharePoint or Document Library setting that would affect this particular behaviour but no ideas right now.

    I had a look at the XML schema that sharepoint generated for my test column (without any constraints) and the basic data type is defined as

    <xsd:simpleType xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:restriction base="dms:Number" /></xsd:simpleType>

    Interesting, because I'm not sure dms:Number should allow commas in it , but it seems to work here. Nothing else in the schema tells me anything (you can find the schema in one of the itemn.xml files inside the the customXml folder inside the .docx/.docm after you rename it to a .zip).


    Peter Jamieson

    Wednesday, May 1, 2013 7:48 PM