0

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?

1
  • Further to this I have created a template of both word and excel and need to move these files to the appropriate client folder to modify the data in both excel and word. However the link is still referencing the templates folder and I cannot find any way to change these links to the new client folder. When trying to use edit links and change source the dialogues box only allows local files and there seems to be no way of editing the URL/sharepoint link. How can I change the sharepoint link to reference the correct files cop[ied to the client folder?
    – Aperson
    Jul 13, 2021 at 2:36

2 Answers 2

1

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.

1
  • 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, 2011 at 16:47
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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