I have a word document which contains some charts based on data in an Excel spreadsheet. I update the word document frequently (it is as status report) by editing some of the supporting data in the Excel spreadsheet.

Now, I would like to store both the Word document and the Excel spreadsheet in a SharePoint document library. However, when I do this, the "link" between the charts in the Word document and the data in the spreadsheet is broken. When I try to edit the links in Word to point to the Excel spreadsheet in SharePoint, I get "You cannot enter a URL here" in the Open Document dialog box.

I'm using Office 2007. Is this something that might work in Office 2010?


Yes it is possible in Office 2010. You have to set the Excel file up as a data connection for your chart data in Word and then specify which worksheet to use.

The caveat to this approach is that you are now embedding the URL to the SharePoint server in your Word document, which effectively hides it. This means that if your SharePoint server changes URL down the road (i.e. Upgrade, hardware replacement, corporate reorg) then your link is now broken.

| improve this answer | |
  • Thanks Dave. Although setting up the Excel spreadsheet as a data connection to the chart data worked, I couldn't get data updates to function smoothly. I had to edit the data of the chart, click refresh data, agree to some security warnings, etc before the data would update. – Jim Hurne Nov 16 '11 at 16:47

Although I wasn't able to edit pre-existing links between the Excel and Word documents, I was able to add new links to documents stored in SharePoint by:

  1. Open the Excel File from SharePoint.
  2. In the Excel File, create the chart and then copy it to the clipboard.
  3. In the Word document, perform a Paste Special...
  4. On the left-hand side of the Paste Special dialog box, select the Past link: radio button.
  5. Select Microsoft Office Excel Chart Object from the As: list box and click OK.

After performing the above, I looked again at the links to other files (Office Menu (Jewel) -> Prepare -> Edit Links to Files) and the links now all point to the SharePoint URL of the Excel File instead of the local copy of the Excel spreadsheet.

As with Dave Wise's answer, this method still embeds the URL in the word document. Thus, if the SharePoint URL changes the link will break. However, I don't anticipate this happening any time soon so it is an acceptable constraint.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.