How to Link Your SharePoint List to Excel!

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

&

Workbook Code

Private Sub Workbook_Open()
    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