Forum Discussion
Updating SharePoint List from Excel
Hi Laura,
When you create a connection from SharePoint to Excel, it is a one way link, from SharePoint to Excel. By default the synchronisation is done on demand, when you do "Refresh" the table on Excel side (you could change the settings on Excel side to deactivate or force the synchro when opening the Excel file, automate it on a regular basis, integrate it or not in the "Refresh all", etc.).
Each "Refresh" reloads all the data from SharePoint to Excel. Any new item is added, anys items modified override data previously loaded and any deleted item disappears from Excel. Any modification made directly in Excel is overrided.
Adding the hyperlinks "Edit" on Excel side as I explained allows you to open each item on Edit mode in SharePoint. There is one "Edit" button for each row in the Excel table, each row corresponding to a unique item in SharePoint. When you click "Edit" for a row, the Edit form opens in a browser and you can make changes on the item. Once saved (in SharePoint), go back to Excel and Refresh the table to load the modification. Of course, if there are lots of items in the SharePoint list and the refresh process is quite long, there is no need to refresh the Excel table immediately. You can make changes on several items and refresh the Excel file from times to times, when needed.
Hope that helps!
Thanks very much for this. Adding the edit link is really helpful, so I learned a new trick 🙂 I was trying to do several items at a time in excel because it is easier to manipulate data than in the SP list, and then have those changes synch with the SP. But if I have to click on each edit link one by one it does not help me much with the "mass updates". But thanks again for sharing your tip, it does work beautifully when we need to update a few records. Less scrolling than when making edits in SP directly.
- This article too seems to do the opposite. It allows for SharePoint list data to come into an Excel spreadsheet. We want our Excel spreadsheet to update our SharePoint list 😞
- Hi Laura,
Updat
inge SharePoint from Excel is possible but more complicated. You have to use VBA and ado.net.
Here is an article I used a few years ago to do that : http://depressedpress.com/2014/04/05/accessing-sharepoint-lists-with-visual-basic-for-applications/
