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?

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.
hattersvAsked:
Who is Participating?
 
BembiConnect With a Mentor CEOCommented:
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
 
BembiCEOCommented:
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
 
hattersvAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BembiCEOCommented:
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
 
hattersvAuthor Commented:
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
 
BembiCEOCommented:
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
 
hattersvAuthor Commented:
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
 
BembiCEOCommented:
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
 
hattersvAuthor Commented:
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
 
BembiCEOCommented:
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
 
hattersvAuthor Commented:
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
 
hattersvAuthor Commented:
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
 
hattersvAuthor Commented:
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
 
BembiCEOCommented:
I guess there is some useful information in there, from this side worth to paq.

But it is always your decision ;-)
0
 
hattersvAuthor Commented:
There was a lot of good feedback and I found that I can not do what I wanted to do.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.