Connecting SharePoint list to Excel Workbook

Recently, I was having a requirement to connect Excel workbook with SharePoint list. Here is very simple solution to synchronize SharePoint list with excel. Please note it’s one way synchronization, i.e: if changes done in excel sheet it will not be reflected back to SharePoint list, but changes done in list will be synchronized to excel.

Step 1: Open SharePoint list and use Export to Excel icon to export the view into Excel. Let’s save it as CustomerView.iqy

Step 2: Create new excel sheet, Open Data tab and click Connections.

Step 3: Use Add – >Browse For More.. button to find CustomerView.iqy file which was saved in step 1.

Step 4: Modify the connection string , copy it in the Notepad and modify with actual details.

There are four parts in this xml chunk ,

  • View ID – GUID of the view
  • List ID – GUID of the list
  • Web Path – Path which points to _vti_bin
  • Root Folder Path – Path of the list

Step 5: Once modified, Click OK and you get the following warning . Click Yes.

Step 6: Go to Data tab and click Existing Connections option ,

Now , select the connection and open it.

Step 7: Keep the table option selected , use the range selector to select cell range.

Once done, click OK and here you are,

Step 8: To test the  synchronization , Added two new document in the SharePoint list

Use Refresh All button in the Data tab to get the updated details,

Here it goes,