Solved

How do I create a dynamic link between an Excel spreadsheet and an SP List when I want the spreadsheet to be the document that is edited?

Posted on 2014-03-25
17
1,271 Views
Last Modified: 2014-09-15
Users prefer to edit in Excel to avoid the slowness of waiting for forms and the limits of the datasheet view.  For example certain fields are not editable.
0
Comment
Question by:hattersv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 
LVL 35

Expert Comment

by:Bembi
ID: 39954555
You can edit every excel document on sharepoint just by opening in excel if not disabled in central admin. Then there is a button in the sp view to open the file with excel.

Saving the file then stores it back to sharepoint
0
 

Author Comment

by:hattersv
ID: 39956995
Hi - I do know this, but I want the information to present as a SharePoint List - with the ability to have different views, not as an excel worksheet.
0
 
LVL 35

Expert Comment

by:Bembi
ID: 39961159
First at all, if Datasheet view is enable, they can edit in the DataSheet view what look like a tab.

Maybe this helps:
http://office.microsoft.com/en-us/sharepoint-foundation-help/synchronize-a-sharepoint-list-with-a-spreadsheet-program-HA101854204.aspx
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:hattersv
ID: 39986760
Datasheet view has a lot of limitations.  What I want is to be able to edit in Excel and have it present in a sharepoint list.  Is this possible?
0
 
LVL 35

Expert Comment

by:Bembi
ID: 39989817
Yes sure, because sharepoint is not excel....

> What I want is to be able to edit in Excel and have it present in a sharepoint list.  Is this possible?

As I described, synchrnonisation. Nevertheless input and output have to be handled in a differnet way.
If enabled, you can even allow inplace edit of an excel files, means you can use a limited functionality of excel directly in sharepoint. Pranctically, you provide a excel file as it and can open it in sharepoint for basic editing. For full functionality you have to open it in Excel from sharepoint. If inplace editiong is enough, then this maybe a way.

On every structured datasource, you can put views or even lists on top , if there is an additionally view needed. This can be realized i.e via Excel services.

But all describes enterprise functionality.
0
 

Author Comment

by:hattersv
ID: 40021646
OK, so what I am understanding is that I can't have information displayed in a SharePoint list format that is actually edited in Excel.  If is true then that answers my question.  Can you confirm?

My issue is that users are used to Excel and are annoyed with having to edit each item via the few clicks that editing a sharepoint list requires.  Datasheet view doesn't solve that problem as it has limitations.

  Thanks!
0
 
LVL 35

Expert Comment

by:Bembi
ID: 40021951
Not exactly....
You can put a sharepoint list on top of a excel sheet, as excel can be used as datasource like any other database.
If you want to edit the data and do not want to use the sharepoint list, than you have to do it in excel.
This can be done in the excel view (inplace editing) -- if allowed -- or you have to open it in Excel directly.
But from which side you turn it into the other, there is no way to use excel in a kind of a form for a sharepoint list.
0
 

Author Comment

by:hattersv
ID: 40022621
Aha!  That is great, so how do I put a SharePoint list on top of an Excel spreadsheet?  Do I need Designer or can I do it out of the box?  Our IT group has withdrawn our Designer rights for now (arrgghhh) so I am limited in what I can do.  Thanks!
0
 
LVL 35

Expert Comment

by:Bembi
ID: 40027882
So, let me give you some hints...
Excel services with sharepoint list...
http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/07/11/excel-services-using-a-sharepoint-list-as-a-data-source.aspx

THis is an example to connect excel services to any kind of data sources, here a sharepoint list.

Something similar....
http://sharepointdiva.wordpress.com/2009/02/11/from-sharepoint-list-thru-excel-to-web-part/

And this one:
http://blogs.msdn.com/b/davidlean/archive/2010/11/15/how-to-see-sharepoint-2010-data-connections-from-excel-2010.aspx

And another solution, what worked for SP 2007, not sure if it works also in 2010:
http://blog.fumus.de/sharepoint/2011/03/sharepoint-listen-in-excel-aktualisieren-und-bearbeiten-3/
and
http://www.microsoft.com/en-us/download/details.aspx?id=9345

These are some solution without Sharepoint Designer. To create a datasource based on OLEDB, you need Sharepoint designer....

Hope one of the examples can help you.
0
 

Author Comment

by:hattersv
ID: 40028087
sorry but these all seem to have the Sharepoint list as the data source.  I want the Excel file to be the data source, and when I update Excel it updates the SharePoint list.  they are Uni-directional the wrong way.

My users like to edit in Excel vs. SharePoint, but we want to display the information in Sharepoint, without having to import all the time.
0
 
LVL 35

Expert Comment

by:Bembi
ID: 40028127
0
 

Author Comment

by:hattersv
ID: 40028195
It is the same thing. You do your edits in SharePoint and it shows up in Excel.  I want to make edits in Excel and have it update SharePoint list.
0
 
LVL 35

Accepted Solution

by:
Bembi earned 500 total points
ID: 40028290
In this direction, you need to use a OLEDB / .NET provider as a data source for a list in Sharepoint. This can work only with sharepoint designer where you can define any kind of data providers as long as the associated .NET data provider is installed on the system.

Sharepoint allows to use any .Net data source as source for sharepoint lists, bit this assumes, that there exists a field to filed connection between the data source and the sharepoint list. The most common way (as implemented by default) is SQL server as a data source. But any other datasource is capable to be used with sharepoint. Bu the wa is through sharepoint designer to setup the OLEDB connction as well as to define the field associations between the data provider and the sharepoint list fields.

Have a look here as least to explain the relation:
http://windowsitpro.com/windows/integrating-external-data-sources-sharepoint
0
 

Author Comment

by:hattersv
ID: 40028469
OK, that helps, at least I know that I can't do what I want just yet.  Thanks!  Should I accept this as  solution even though it isn't a solution or withdraw the question?
0
 
LVL 35

Expert Comment

by:Bembi
ID: 40030227
I guess there is some useful information in there, from this side worth to paq.

But it is always your decision ;-)
0
 

Author Closing Comment

by:hattersv
ID: 40030278
There was a lot of good feedback and I found that I can not do what I wanted to do.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40322971
Hi guys

I know this question has already been answered, but if it helps I just finished an article on how to pull off an Excel front-end, SQL Server Stored procedure back-end, with passing user-defined parameters:

Microsoft Excel & SQL Server:  Self service BI to give users the data they want
If you like what you see, please click the 'Yes' button next to 'Was this article helpful?', and provide some feedback.

Thanks in advance.
Jim
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Last week I faced a strange issue recently, i have deployed SharePoint 2003 servers for one project and one of the requirements was to open SharePoint site from same server. when i was trying to open site from the same server i was getting authentic…
Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question