Query-Import SharePoint List to Excel using Data Source Connections

Have you ever had to query and import the SharePoint list to Excel? Or ever required to use SharePoint list as an 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 and create a new view matching your requirement by adding necessary columns.

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.

 Make sure you set the below properties to skip column width and preserve sort/filter/layout/formats.

You can also set the data refresh when opening the file.

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 the SharePoint list (One way – from the SharePoint list!)

Fix data format in Lookup and People picker columns
When you import data from the SharePoint list to Excel, it imports the data in the same format SharePoint stores internally. 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.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.