none
Content Controls in Word (from Sharepoint) linked to Excel worksheet RRS feed

  • Question

  • I have a Word document set as the default template for a Sharepoint content type.  This allows me to use the colums in the SP Content Type as Quick Parts (aka Content Controls) in the Word document and, therefore, have the data the users enter into the Word document saved as part of the Sharepoint record.  The data in some of the Quick Parts, however, instead of being entered by users needs to come from a linked Excel workbook and be updated whenever the workbook is updated.  I wrote a Word macro to copy the Excel cells into the Word document (as a table) and then update each ContentControl with the numbers in the table cells.  Works fine except that because the copied table is the object that is linked to the Excel file, it gets updated (when I click "Update Links") and but the values in the Content Controls don't get updated.  (Originally I tried creating links directly between cells in the Excel workbook and the specific ContentControls didn't like that and I got errors.)

    I suppose I could add another macro that recopies the table cell values into the ContentControls but I'm hoping there's a cleaner way to do it.  I'm prepared to dive into VSTO if that's necessary but although I'm minimally knowledgeable about Visual Studio (2008 and 2010), I've never used VSTO itself.

    Thanks in advance for any assistance you can give.  Carol.

    Friday, March 15, 2013 5:30 PM

All replies

  • Hi Carol,

    Thank you for posting in the MSDN Forum.

    I'll consult your issue with my colleague. You'll be informed if there's any update.

    Thank you for your patience and understanding.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, March 18, 2013 8:04 AM
    Moderator
  • Hey Carol,

    Can you explain the process you are using for updating the content controls with numbers in the table cells?

    Brandon

    Monday, March 18, 2013 8:47 PM
    Moderator
  • Sure.  Here's my code (Basically it copies a range from the Excel workbook and pastes it into a Word table.  Then it searches through the Word document for a content control with a particular name (comes from one of the columns in the workbook).  When it finds that content control, it copies the number value in the table (from the second column in the workbook) into the value of the Content Control):

    <snip>

    [this section finds the range to be copied]       

      For Each xlSheet In xlApp.ActiveWorkbook.Worksheets
                If xlSheet.Name = "Abstract fields" Then
                    xlSheet.Range("A2:B15").Copy
                End If
            Next xlSheet
           
            oName.Activate
            oName.Visible

    [this section pastes the Excel range into the Word document; the table contains one column with text and one with numbers]                
            Selection.GoTo What:=wdGoToBookmark, Name:="ExcelTableLink"        
            
            Selection.PasteExcelTable True, False, True
            'Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
                wdInLine, DisplayAsIcon:=False

        End If
       
    [this section copies the values from to the table in Word into the content controls that have the same names as the first column from the Excel table]    
        For tableRow = 1 To 13
            controlName = ActiveDocument.Tables(2).Cell(Row:=tableRow, Column:=1).Range
            controlName = Left(controlName, Len(controlName) - 2)
            controlValue = ActiveDocument.Tables(2).Cell(Row:=tableRow, Column:=2).Range
            controlValue = Left(controlValue, Len(controlValue) - 2)
           
        For Each Item In ActiveDocument.ContentControls
            If Item.Title = controlName Then
                Item.LockContents = False
                Item.Range.Text = controlValue
                Item.LockContents = True
            End If
        Next Item
        Next tableRow

    I hope this helps.  Carol.   

    Tuesday, March 19, 2013 2:45 AM
  • Hi Carol,

    Does the value of "controlValue" get set correctly in "For tableRow" and then the content control doesn't set it correctly, or is it not getting the correct value from the table?

    Brandon

    Wednesday, March 20, 2013 7:53 PM
    Moderator
  • Hi, Brandon.  When I run the  macro, everything is done correctly.  The problem happens after I've closed the document, the workbook has been updated (so range in the Abstract Fields tab has been updated), and then I open the Word document again.  At that point, I get a question saying that there are links that need to be updated and do I want to update the links.  I answer yes, and the links in the copied table get updated (the table created by these lines):

    Selection.GoTo What:=wdGoToBookmark, Name:="ExcelTableLink"    
            Selection.PasteExcelTable True, False, True
            'Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
                wdInLine, DisplayAsIcon:=False

    My problem is that the values from this table that were copied into the content control values are not updated, because updating the copied table doesn't automatically run the part of the macro that copies the values into the content controls.  Is there a way to create a macro that is triggered by the event that updates the links?  I've created macros that run either when Word is started or is shut down but I'm unaware of other types of events that can be used to trigge macros.  Do those events exist?

    I hope I've made this clear.  And I hope that events can trigger macros.  Thanks.  Carol.

    Thursday, March 21, 2013 12:31 AM
  • Hi Carol,

    Unfortunately, there are no events that I know of that get fired specifically when the links are updated. 

    There are other events that you might be able to use to try and run your code, but I don't know of a specific update links event.

    http://msdn.microsoft.com/en-us/library/office/jj889487.aspx

    http://msdn.microsoft.com/en-us/library/office/jj878809.aspx

    Brandon 

    Thursday, March 28, 2013 7:14 PM
    Moderator
  • None of the events in Word 2010 seem all that helpful, but

     a. In Word 2013 you might be able to put the entire linked table in a rich text content control, mark it as undeletable, link it to a Custom XML Part, and use the ContentControlBeforeStoreUpdate event. Some preliminary tests suggest that that can be done, but I haven't followed up all aspects of it.

     b. in Word 2007/2010 you can't link a rich text content control to the Custom XML store, so that is not possible. I tried various approaches to see if you could use plain text content controls but...

      - although you can paste a link (say, to the plain text of an Excel cell) into a plain text control, when you close/re-open the document an XML format error occurs.

      - if you paste-link the table, then (say) create a plain text content control that "covers" one cell, the content control is deleted when the table updates. If you protect the control against deletion, the table doesn't update. That is not unexpected, but I thought I'd check anyway. SOmetimes I thought I spotted variations on that behaviour which makes the whole approach look like a dead end.

     c. However, if you select the table and create a rich text content control, the ContentControlBeforeDelete event does fire when the table updates. So as long as your event handler can reinsert the content control, this event may do what you need. It isn't that easy to identify the control because, despite the event name, the control has actually gone before the event code gets to process it. Also, the event code does not fire when the document opens, so you have to have extra code for that. I decided to identify the control by inserting a bookmark that encompasses the table (i.e. will not be deleted when the table updates. This isn't ideal - you could perhaps use an enclosing content control instead. The code I had at the end of the experiment was as follows - perhaps others can suggest simplifications:

    Private Sub Document_ContentControlBeforeDelete(ByVal OldContentControl As ContentControl, ByVal InUndoRedo As Boolean)
    Dim rng As Word.Range
    Set rng = ActiveDocument.Bookmarks("cc").Range
    If rng.Start <= OldContentControl.Range.Start And rng.End >= OldContentControl.Range.End Then
      ActiveDocument.ContentControls.Add wdContentControlRichText, rng.Tables(1).Range
      ' replace the following line with the necessary processing
      Debug.Print rng.Tables(1).Range.Cells(1).Range.Text
    End If
    Set rng = Nothing
    End Sub

    Sub applycc()
    Dim rng As Word.Range
    Set rng = ActiveDocument.Bookmarks("cc").Range.Tables(1).Range
    If rng.ContentControls.Count = 1 Then
      rng.ContentControls(1).Delete
    End If
    rng.ContentControls.Add wdContentControlRichText, rng
    ' replace the following line with the necessary processing
    Debug.Print rng.Cells(1).Range.Text
    Set rng = Nothing
    End Sub

    Private Sub Document_Open()
    Call applycc
    ' now assume the table has changed and process it
    End Sub
    Private Sub Document_ContentControlBeforeDelete(ByVal OldContentControl As ContentControl, ByVal InUndoRedo As Boolean)
    Dim rng As Word.Range
    Set rng = ActiveDocument.Bookmarks("cc").Range
    If rng.Start <= OldContentControl.Range.Start And rng.End >= OldContentControl.Range.End Then
      ActiveDocument.ContentControls.Add wdContentControlRichText, rng.Tables(1).Range
      ' replace the following line with the necessary processing
      Debug.Print rng.Tables(1).Range.Cells(1).Range.Text
    End If
    Set rng = Nothing
    End Sub
    
    Sub applycc()
    Dim rng As Word.Range
    Set rng = ActiveDocument.Bookmarks("cc").Range.Tables(1).Range
    If rng.ContentControls.Count = 1 Then
      rng.ContentControls(1).Delete
    End If
    rng.ContentControls.Add wdContentControlRichText, rng
    ' replace the following line with the necessary processing
    Debug.Print rng.Cells(1).Range.Text
    Set rng = Nothing
    End Sub
    
    Private Sub Document_Open()
    Call applycc
    ' now assume the table has changed and process it
    End Sub
    



    Peter Jamieson

    Sunday, March 31, 2013 2:19 PM