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,206 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
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.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cumulative Updates 1 49
Sharepoint (online/2013) - project management list of subsites? 4 46
Convert a Master Page to HTML 16 116
SharePoint display modal blank 2 56
Work Over Net is a new and very powerful collaboration product. With its new easy interface it is becoming very competitive to other similar products like webex and office interactive. WON 2010 have the standard business tools needed for multi-offic…
Microsoft SharePoint Foundation 2010 and Microsoft SharePoint Server 2010 do not offer the option to configure the location of the SharePoint diagnostic trace log files during installation.  This can, however, be configured through Central Administr…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

813 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

15 Experts available now in Live!

Get 1:1 Help Now