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,189 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
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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 65

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Pimping Sharepoint 2007 without Server-Side Code Part 1 One of my biggest frustrations with Sharepoint 2007 in the corporate world is that while good-intentioned managers lock down the more interesting capabilities of Sharepoint programming in…
We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 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

22 Experts available now in Live!

Get 1:1 Help Now