locked
Link in Word to Excel chart stored in SharePoint RRS feed

  • Question

  • I want to embed chart from Excel in Word document. Both Excel and Word documents are saved in SharePoint library. What I did, was to paste chart from Excel with "link data" option enabled. I also went to Links window and set "Update method for seected link" to "Automatic update". 

    This works great, asking me to update chart everytime I open Word document, which is exactly what I want. Only as long as I have Excel file open. (I noticed, that when the file was on local PC, instead of SharePoint, it worked without opening Excel). When Excel is not open, chart is not refreshed. 

    If I go to Client Tools -> Design ribbon and click on Edit Data, source is opened in window and chart is updated. 

    My question is:

    How can I force chart to update with data, that might have changed in Excel located on SharePoint? Macro, that would open the file is fine. 

    Making users go thru ribbon and "edit data" option is unfortunately too complicated for inexperienced users. I need one-button-click solution or something that works automaticaly. 

    Wednesday, December 10, 2014 9:20 AM

Answers

  • I accomplished this with following macro:

    Sub Update()
    
        Dim donut As InlineShape
        Dim source As String
        Dim xlsobj_2 As Object
        Dim xlsfile_chart As Object
        
        Set donut = ActiveDocument.InlineShapes.Item(1)
        source = ActiveDocument.InlineShapes.Item(1).LinkFormat.SourceFullName
        
        'open excel
        Set xlsobj_2 = CreateObject("Excel.Application")
        xlsobj_2.Application.Visible = False
        Set xlsfile_chart = xlsobj_2.Application.Workbooks.Open(source)
        
        donut.LinkFormat.Update
        
        'close excel
        Set xlsfile_chart = Nothing
        xlsobj_2.DisplayAlerts = False
        xlsobj_2.Quit
        Set xlsobj_2 = Nothing
        
    End Sub
    

    What this macro does is:

    - finds pasted chart in shapes (the 'donut')

    - gets the source of chart and opens it with Excel client

    - calls update method on chart

    - closes Excel client

    • Marked as answer by Tschareck Monday, January 5, 2015 10:38 AM
    Monday, January 5, 2015 10:38 AM

All replies

  • Hi,

    Your required could be done via some SharePoint develop solution. I'll move this thread to SP forum.

    https://social.technet.microsoft.com/Forums/office/en-US/home?forum=sharepointdevelopment

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Thursday, December 11, 2014 2:37 AM
  • Hi Tschareck,

    According to your description , my understanding is that you want to update the excel chart data when editing the chart in the word.

    As a workaround, I suggest you can use SharePoint ItemUpdated Event Receiver, when you updated the chart data in the word, then reupload the excel file in the event receiver.

    Here are some detailed code demo for your reference:

    How to: Create an Event Receiver

    programmatically upload file to sharepoint list

    Best Regards 

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Zhengyu Guo
    TechNet Community Support

    Friday, December 12, 2014 9:27 AM
  • Actually, I wanted something opposite. 

    The Excel will be updated automaticaly (there is another tool, that spits out excel data, and this data gets uploaded to SharePoint library).

    Then, some small portion of that data, a chart, is used in Word document.

    This Word doc is a template, user will open it from SharePoint. The document already has the chart, layout is also in the doc. User writes text in a text field, and then presses Print or Save as to PDF. 

    From my tests, if data in excel document has changed, Word (the thick client app) should update the doc, that has linked data. Unfortunately, it doesn't unless the file is already open in Excel client.

    Can I maybe write VB macro, that would open excel data?

    I'm not sure, that this thread really should have been moved to SharePoint development forum. 


    • Edited by Tschareck Monday, December 15, 2014 1:33 PM
    Monday, December 15, 2014 1:33 PM
  • So let me repeat what I've understood.

    • You have an excel file that has a chart getting data from some LOB systems. You have uploaded the file in SharePoint
    • You have copied the chart in Microsoft Word and upload the file in SharePoint
    • Now when you go to Word document in SharePoint you don't get the data refreshed automatically.
    • However if you open the excel file in SharePoint (not sure if data in excel get refreshed automatically at this point or you manually refresh) and then open the word document in another browser tab, you get the updated chart in word.

    If this is the case then possibly the word document doesn't force the data refresh, it just gets the data from excel (where excel has the old chart). And I think this should be the default behaviour for SharePoint - you need to install and configure SharePoint BI components to refresh data as far as I understand.

    You need to configure Excel Data Services in SharePoint where you can configure your excel files to be refreshed periodically. You can quickly check the link below to see if this helps:

    http://blog.faisalmasood.com/2014/06/primer-on-sharepoint-excel-datarefresh.html


    Thanks,
    Sohel Rana
    http://ranaictiu-technicalblog.blogspot.com

    • Marked as answer by Patrick_Liang Monday, December 22, 2014 2:37 PM
    • Unmarked as answer by Tschareck Monday, January 5, 2015 10:38 AM
    Tuesday, December 16, 2014 5:38 AM
  • I accomplished this with following macro:

    Sub Update()
    
        Dim donut As InlineShape
        Dim source As String
        Dim xlsobj_2 As Object
        Dim xlsfile_chart As Object
        
        Set donut = ActiveDocument.InlineShapes.Item(1)
        source = ActiveDocument.InlineShapes.Item(1).LinkFormat.SourceFullName
        
        'open excel
        Set xlsobj_2 = CreateObject("Excel.Application")
        xlsobj_2.Application.Visible = False
        Set xlsfile_chart = xlsobj_2.Application.Workbooks.Open(source)
        
        donut.LinkFormat.Update
        
        'close excel
        Set xlsfile_chart = Nothing
        xlsobj_2.DisplayAlerts = False
        xlsobj_2.Quit
        Set xlsobj_2 = Nothing
        
    End Sub
    

    What this macro does is:

    - finds pasted chart in shapes (the 'donut')

    - gets the source of chart and opens it with Excel client

    - calls update method on chart

    - closes Excel client

    • Marked as answer by Tschareck Monday, January 5, 2015 10:38 AM
    Monday, January 5, 2015 10:38 AM