Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

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.

share|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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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