How to Link Your SharePoint List to Excel:
SharePoint provides the capability to Link your SharePoint list data to an Excel workbook. This link is a one way street where data updates made on SharePoint will be reflected in the Excel workbook, but any updates made within Excel will not get pushed through to the SharePoint list.
To Create The Data Connection:
1. Navigate to your SharePoint list
2. List Tools
3. List
4. Export to Excel
5. Select Open
6. In Excel select enable
When you use this method you are not just sending the table of data to Excel, you are actually creating the data connection within the workbook.
You can tell if workbook has a connection by doing the following:
1. Data Tab on the ribbon
2. Connections
3. Connections
4. There should be something along the lines of Owssvr
You can tell exactly where the connection is going to by inspecting the properties. Within the properties it will say what the URL is, the list ID, & view ID.
This data will stay stagnant until you refresh the data connection. You can refresh the data connections within your workbook by:
1. Data Tab
2. Connections
3. Refresh All
Many companies would like to report weekly or monthly status based on the list and use Pivot Tables and Pivot Charts to do so. By creating the Pivot Tables & Pivot Charts based upon the linked table will allow all of your pivots to be updated when the table is refreshed.
Most users do not know how to refresh the data connection between SharePoint and Excel so I typically like to use VBA on the workbook load to refresh the data or creating a button and using VBA there to Refresh All. In order to create the button or On Open Code, you will need to enable the developer tools.
How to Enable Developer Tools For Excel (Developer Tab):
1. Within Excel Select File
2. Options
3. Customize Ribbon
4. Checkmark The Developer Box Under Main Tabs
5. Select Ok
The Code You Will Need For The Button Refresh:
Button Code (Your button (Name) will replace CommandButton1)
Private Sub CommandButton1_Click()
ActiveWorkbook.RefreshAll
End Sub
ActiveWorkbook.RefreshAll
End Sub
&
Workbook Code
Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
End Sub
ThisWorkbook.RefreshAll
End Sub
To Refresh Automatically On Workbook Open:
1. Developer Tab
2. Code
3. Visual Basic
4. This Workbook
5. Drop The Private Sub Workbook_Open() code provided above in and save
To Create A Button To Refresh On Button Click
1. Developer Tab
2. Insert
3. ActiveX controls
4. Command Button
5. Double Click Into The Button
6. Insert the ActiveWorkbook.RefreshAll into the button within the sub procedure
In order for your users to use these “Macros,” you must save the workbook as a Macro Enabled Workbook through Save As.
Enjoy!
Dan
Great work, does exactly wat I'm looking for.
ReplyDeleteThank you Colin, I am glad that it was able to help you!
ReplyDeleteDan
This comment has been removed by the author.
ReplyDeleteDan, is it possible to do this in the opposite direction? Link a workbook to a SharePoint list and when you add/revise content to Excel it will update SharePoint automatically? Thank you.
ReplyDeleteHi Dannysal,
ReplyDeleteMy initial question would be to why you would want the connection to go both ways? Typically the connection to the List or Library data would be used to create reports & metrics within Excel on the linked content. If this has been proposed by your business partners in hopes addressing bulk uploads, they would be able to modify multiple records at a time using the datasheet view within SharePoint. If the requirement is to upload / manipulate large amounts of data rather than just manipulating multiple line items at the same time, you could MS Access which has the two way street functionality that you are looking for. You could also use Access to write append, delete, etc queries in order to modify large amounts of data in a scripted fashion.
Regards,
Dan
We have a calendar we pull from an Access macro currently. We use an ODBC connection with Access, then run and updates the calendar with 30 days out action items. Manual process. Must open, execute macro, close Access each time we want it updated. Having this Excel doc 9or other doc) updated using a data refresh every night would keep the calendar current? So if I have a Excel doc linked to a Calendar, then want that Excel doc updated and refreshed every night. Or somehow to create those data connection and insert into a calendar.
ReplyDeleteHi Kriss,
DeleteThanks for the comment! I am a little confused on to what exactly is being done with Access, but couldn't you create a Calendar in SharePoint as well as assign the Tasks in SharePoint? By creating the calendar in SharePoint, it would give your team a central location to view the calendar as well as the potential to use calendar overlays to color code events etc. As for the users who desire the itinerary in Excel, they could export the Calendar as a list which would give them an itemized view. It would also be possible to create a calendar view based upon the upcoming tasks or even creating personal views such as “Your Tasks” by filtering on the assigned to = [me]. I hope this helps and please feel free to clarify if I was completely off base!
Cheers!
Dan
Really Awesome :D
ReplyDeleteHi Dan,
ReplyDeleteI need to publish the workbook, that's gathering information from one SharePoint site, to a different site. The original data that is feeding the workbook isn't in an excel spreadsheet format. A coworker saved the data into excel and now needs to publish the workbook onto our SharePoint site. SharePoint doesn't like this and gives me the error message:
"The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:
SharePoint lists
Contact the workbook author."
If this makes sense, can this be done- viewing the already connected information in another SharePoint site?
Hi Kristie,
DeleteFirst of all, thank you for checking out the blog and posting a comment!
I am not sure if I fully understand your question, but do you have a workbook which is currently connected to a SharePoint list or was the data exported to Excel without setting up a data connection? You can tell if there is a connection to a SharePoint list by going to Excel → Data Tab → Connections→owssvr
You should be able to refresh the workbook’s data connection regardless of where it is stored, but keep in mind you must have permissions to that list to run the refresh. If you don’t have permissions to the list or library where the data is being pulled from, you will not be able to pull in the new data.
Also from your comment it sounds like you are trying to use an Excel Web Access Web Part to display a portion of your workbook as part of a dashboard or possibly using it on a client which does not have Excel on it.
There are a few caveats of the Excel Web Access Web Part which will not allow it to display on the page:
Excel workbooks that contain or use one or more of the following features will not load in Excel Services.
http://msdn.microsoft.com/en-us/library/ms496823(office.12).aspx
• Code. This includes workbooks with VBA macros, form controls, toolbox controls, Microsoft Excel 5.0 dialog sheets, and XLM macro sheets.
• Information Rights Management (IRM) protection.
• ActiveX controls.
• Embedded smart tags.
• PivotTables report with multiple consolidation ranges.
• External references (workbooks that contain links to other workbooks).
• Workbooks saved in formula view.
• XML expansion packs.
• XML maps.
• Data validation.
• Query tables, SharePoint lists, Web queries, and text queries.
• Workbooks that reference add-ins.
• Workbooks that use the RTD() function.
• Workbooks that use workbook and worksheet protection.
• Embedded pictures or clip art.
• Cell and sheet background pictures.
• AutoShapes and WordArt.
• Ink annotations.
• Organization charts and diagrams.
• DDE links.
As you can see there are many features which could stop the workbook from displaying in the web page. I am not sure if this is what your question was regarding, but feel free to post another comment so I have a better idea of what scenario is.
Cheers!
Dan
Hi Dan,
ReplyDeleteI love the blog; just the kind of techie information I'm looking for; my boss loves the pivot table view; but now my team lead would like one pivot table that captures the information from more than one SharePoint list - so I was thinking (I know "be careful") two linked spreadsheets then a workbook with a pivot table that links to the two linked workbooks . . . Or maybe join the two lists in SharePoint first then export that to Excel . . . Thoughts? And I understand if you would rather that I self - "opt out" of your blog :-)
Have a Blessed day - Chris
Hi Christopher,
DeleteThank you for the kind comment and I am glad that you are enjoying the blog!
The best way to capture information from multiple SP lists within one Pivot Table is to use Power Pivot which is a free add on. There are a few prerequisites which your environment needs to have in order to use it, but it will allow you to set up the relationships which you have in SharePoint in Excel.
I am assuming that you have two or more SP lists which are connected via a Lookup column in SharePoint and now you are trying to display that information within one pivot. Power Pivot will allow you to create a relationship based on the Lookup in order to display all of the info in a single pivot table.
I actually wrote a blog article on it a while back on how to connect to your first list or library using Power Pivot as well as it links to the exact requirements to use it.
http://danssharepointblog.blogspot.com/2013/07/how-to-install-powerpivot-connect-to.html
Let me know if you have any questions and good luck!
Dan
Thank you so much! I think you help me a lot here
ReplyDeleteI was stuck many many hours trying to my workbook refresh automatically at open, i was having this issue:
if i had the excel file locally everything worked perfect
if i had the excel on a server it just didn't refresh at open it
With your solution (for developers) i was able to resolve this =)