With advances in both Excel and SharePoint , it is now possible to create solutions that leverage SharePoint’s hosting and security features along with Excel’s powerful calculation engine, pivot tables, and charting capabilities. When SharePoint is used as a central repository for lists, multiple users throughout the organization can have permissions to access those lists or portions of those lists, ensuring that all users have current information.  Data stored in SharePoint lists can then be pulled into Excel applications using VBA and ADO connections or by linking them with Excel tables.

Below are some examples of how our clients have used SharePoint and Excel together:

• A rapidly growing Franchisor keeps lists franchisees, stores, regions, and associated data in SharePoint and analyzes them in Excel.
  • Legal team has exclusive permissions for maintaining the lists, which are hosted in SharePoint
  • Using custom Excel applications developed by ExcelHelp(excelhelp.com), Development team and Regional Managers pull the SharePoint lists into Excel Pivot Tables and Charts that provide meaningful views of the data

• A construction company reduces manual processes, improves compliance and enhances their safety efforts using SharePoint and Excel.
  • Field supervisors enter information and photos related to job site injuries into SharePoint, where it is immediately available to be viewed by mangers and staff with appropriate permissions.
  • All injury data for each job is pulled into Excel, where data is aggregated, analyzed and presented using Excel’s superior graphing capabilities.
  • Automated monthly management reports generated from Excel provide managers with the information they need in a timely manner.

• An automotive sales team enters new leads and opportunities into SharePoint as they come in, providing both the sales and marketing teams access to that information.

At any time, the marketing team can pull data into Excel and analyze it using slicers and other visualizations, which enables them to target demographics with precision when producing marketing materials

 

Excel ADO and SharePoint Lists

The following discussion provides guidance for connecting an existing SharePoint list to Excel.

Before you get started, you need to set some global parameters.  First, obtain the web or LAN address for your SharePoint site.  Then obtain the GUID and connection string for connecting with your SharePoint lists.  These are explained in more detail in the links below.

GUID:  Finding The GUID of a Sharepoint List

Connection String:  Sharepoint Connection Strings

Once you have the SharePoint site address, GUID and connection string, the following code can be used to connect to the SharePoint list.  The function provided returns the record count, but it can be altered to provide other statistics or to dump the list into Excel for further analysis there.

Option Explicit
‘Note, for this to operate, you need a reference to Microsoft Active X Data Objects
Public Const HOST = “https://yoursite.sharepoint.com/”
Public Const LIST_GUID = “{26534EF9-AB3A-46E0-AE56-EFF168BE562F}”
‘This constant you must find, utilize the following site to guid you
‘https://nickgrattan.wordpress.com/2008/04/29/finding-the-id-guid-for-a-sharepoint-list/
‘example
‘{26534EF9-AB3A-46E0-AE56-EFF168BE562F}
Function TestPullFromSharepoint() As Long
Dim cnn As ADODB.Connection
Dim rec_set As ADODB.Recordset
Dim ConnectionString As String
Dim Query As String
‘Build connection string.  Use this site for assistance
‘https://www.connectionstrings.com/sharepoint/
ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;” & _
“DATABASE=” & HOST & “;” & _
“LIST=” & LIST_GUID & “;”
Set cnn = New ADODB.Connection
Set rec_set = New ADODB.Recordset
With cnn
.ConnectionString = sConn
.Open
End With
Query = “SELECT * FROM [Table];”
rec_set.Open Query, cnn, adOpenStatic, adLockOptimistic
‘Get the # of records returned
TestPullFromSharepoint = rec_set.RecordCount
rec_set.Close
Set rec_set = Nothing
End Function

 

Linking a Sharepoint List to an Excel Table

Rather than utilizing ADO to query SharePoint and pull in data, you can simply create a table in Excel and connect it with SharePoint. This is done by exporting a SharePoint list to Excel and saving the Web Query. Upon opening the saved Web Query, the current data is pulled from SharePoint automatically. Once the list is in Excel, it will not update when SharePoint is updated without adjusting the connection settings that allow a background refresh.

1. From SharePoint, Export the list to Excel.  You will be prompted to Open or Save. Select Open. The web query is then created. You will then be prompted to name the Microsoft Excel Web Query File and Save it.

 

2. Once the Web Query is saved and downloaded, open the file. Excel will open and you will see a copy of the list data.

 

3. This data is static. Navigate to Data, Connections. The Web Query name you entered in step 1 will appear in the connections list. Select it and click the button for Properties.

 

4. Check off the boxes for Enable Background Refresh and Refresh Data when opening the file.

 

5. Click OK and close out all the windows.
Your exported SharePoint list in Excel will now update as the SharePoint data is updated.

If you’d like to discuss with our premier team, kindly reach out via our contact form here and we’ll schedule a time to go over details.