Connecting SharePoint list to Excel WorkbookPosted: February 24, 2011
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,