Synch Options from Microsoft Excel to SharePoint

Depending on the business case one of the following options should be a valid one.  It depends on the infrastructure too. There are a few limitations, variations and difference in concept depending on the requirements, for example if the use case involves only Excel 2003 usage, the process of porting data from Excel to SharePoint concept is called Publishing, where as if its Excel 2007, the process is keyed as Exporting. This difference is because

Excel 2003, out of the box (OOTB) it has the ability to synch 2 way with SharePoint.

Excel 2007, OOTB has the ability to do only 1 way synch. Although Microsoft released a add in to make 2 way synch possible.

In either case once the export/publishing is done the data in SharePoint should be considered as the master data.

1. MS Office Excel 2003 – 2 way synch is possible [Publish to SharePoint]

2. MS Office Excel 2007 – 1 way synch. (with add in 2 way synch is possible) [Export to SharePoint] (Addin 2 way synch) One way synch (SP to Excel)

3. General

4. Publishing Excel Workbooks to SharePoint so that users without MS Office can access complex spreadsheets in Browser


Excel Services Charts, Spreadsheets & Worksheets in SharePoint – Issues

You do not have permissions to open this file on excel services.

Make sure that the file is in an Excel Services trusted location and that you have access to the file.

If you are troubled with the above errors in SharePoint while publishing and viewing the excel services forms in browser then I believe you are in the right place. I am not sure if my guidelines will solve your problem but at least it solved my issues.

  1. In central admin site, in operations page, under services in the server farm enable the Excel Services.
  2. Open up the Shared Services Admin site. Under “Excel Services settings” section, open the ‘Edit Excel Services settings ‘. Set the File Access Method to Process Account. In the same page under the ‘External Data’ section assign the appropriate user.
  3. Back to SSP Admin site. Open the “Excel Services Trusted File Locations”. Give the URL Address of the location where you publish your excel workbooks. Potentially this will be the document library of type excel services. If it’s a document library make sure not to put the complete URL i.e DON’T PUT something like http://server:5050/doclibraryname/Forms/AllItems.aspx, instead put it like http://server:5050/doclibraryname. In my case this is the main reason which showed the above error. If I use full URL I get those errors and no site mentions this after spending hours searching on google.
  4. In the same page under the ‘External Data’ select the Trusted data connection libraries and embedded or just the Trusted data connection libraries.