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,168 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
  • 8
  • 8
17 Comments
 
LVL 35

Expert Comment

by:Bembi
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:hattersv
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 35

Expert Comment

by:Bembi
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:hattersv
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
There was a lot of good feedback and I found that I can not do what I wanted to do.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

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…
For SharePoint sites, particularly public-facing ones, there are times when adding JavaScript, Meta Tags, CSS Styles or other content to the page <head> section is more practical than modifying master pages.  For instance, you could add the jQuery l…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now