Query-Import SharePoint List to Excel using Data Source Connections

Have you ever had to query and import SharePoint list to Excel? or ever required to use SharePoint list as excel data source? Well, just follow these steps to excel data connection to use SharePoint list as excel data source:

Step 1: Create a SharePoint list view with desired columns and order:

Go to your SharePoint list, create a new view matching your requirement by adding necessary columns to it.

Step 2: Use Export to Excel to generate Data Source Connection

Navigate to your SharePoint list view created in Step-1, use the “Export to Excel” button under the “List” tab. This imports the SharePoint list to Excel and establishes the data source connection in Excel as below.
use sharepoint list as excel datasource
 Make sure you set the below properties to skip column width and preserve sort/filter/layout/formats.
excel data connection to sharepoint list
You can also set the data refresh when opening the file.
sharepoint list as excel data source

How to Refresh the Data?

All you have to do is, either right-click on any data imported and click “Refresh” in the context menu or click on the “Refresh All” button under the “Data” tab of Excel.

Once you click refresh, Your Excel sheet will be synced from SharePoint list (One way – from SharePoint list!)

query sharepoint list using excel

Fix data format in Lookup and People picker columns
When you import data from the SharePoint list to Excel, It just imports the data in the same format SharePoint stores internally. Say for E.g. You’ll see John Mathew;#157;#Omar Saad in the people picker column.

Solution:

Let’s create a custom function in VBA code to replace the format “Number#;” in People Picker, Lookup columns with regular expressions:

  • Go to Excel >> File >> Options >> Customize Ribbon >> Enable “Developer” check box under Customize the Ribbon. This adds a new tab “Developer” in the Ribbon. 
  • Click on the “View Code” button under the “Controls” group of the Developer Tab. 
  • Click on Tools >> Add Reference >> Pick “” to add Regular Expressions library: Microsoft VBScript Regular Expressions 5.5Add Reference to Microsoft VBScript Regular Expressions 5.5
  • Right-Click the VBAProject >> Insert >> Module. Insert the below code into the Module.
Function xREPLACE(pattern As String, searchText As String, replacementText As String, Optional ignoreCase As Boolean = True) As String
    On Error Resume Next
    Dim RegEx As New RegExp
    RegEx.Global = True
    RegEx.MultiLine = True
    RegEx.pattern = pattern
    RegEx.ignoreCase = ignoreCase
    xREPLACE = RegEx.Replace(searchText, replacementText)
End Function
  • Now, create a new column in Excel, Say for E.g. You are going to properly format the “Deal Lead” column, Enter the formula as:
    =xREPLACE(“\#\d*;|#\d*|\d*;#”,TRIM([Deal lead]),” “)
  • You can hide the original column “Deal Lead” and Name your new columns similar to “Deal Lead”, say “Deal leader” or add a . (dot) prefix.
  • Save the Excel file as “XLSM” format so that the code saved along with your Excel sheet.

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

One thought on “Query-Import SharePoint List to Excel using Data Source Connections

  • May 10, 2020 at 8:16 PM

    Very helpful, great tips, thank you

    Reply

Leave a Reply