Forum Discussion
Updating SharePoint List from Excel
Hi
The solution I've been using for years is the following :
- from the SharePoint List, define a specific view containing the fields you need and also the "ID" field (I generally call this view "ExportToExcel"). Click "Export to Excel" and accept all pop-ups.
- This creates an Excel table. Give it a name, of course (I prefix such tables with "sp" so let's name it spMyList)
- Back to the SharePoint list, build the urls for "New item" and "Edit item"
- <base url>/NewForm.aspx
- <base url >/EditForm.aspx?ID=
- Copy these urls as text in 2 cells, in a separate sheet in the Excel file (prefix them with a quote)
- Give names to these cells : urlNew and urlEdit
- In the table spMyList, insert a column on the left, so that this will be the first column of the table. Insert it IN the table, not before the table.
- Add a formula in the column: =HYPERLINK(urlEdit&[@ID],"Edit")
- This will create an hyperlink on each row, allowing to open in edit mode each item. Once an item has been modified and saved, go back to Excel and refresh spMyList
- Add a row above the spMyList table. In cell A1, enter this formula: =HYPERLINK(urlNew;"New"). This hyperlink will allow you to cretae a new item in the list. Just refresh Excel when it's done to see it in the list. (You can also create a button associated with the url to do that).
- Freeze rows 1 and 2.
There are many other tips to facilitate this way of working. I must make a video one day to share that!
Sylvie_in_France I've been searching for something like however, I have encountered a syntax error when adding the formula in the excel sheet. Any chance you have a video of this?
Also just to clarify, update the SharePoint List will be done via the Excel Button Sheet?
Thank you in advance for any help!
Hi Ryuojiin,
Could you copy paste the formula and the error message so that I can help you?
To update the SharePoint list from Excel, you have to click on the "Edit" hyperlink on any row/item to modify. This hyperlink open the "edit item" page on SharePoint. Modify the item and Save it. Go back to Excel and refresh the table. Your data is updated in Excel.
You can of course modify serval items one by one, using their respective "Edit" hyperlink, and update the Excel file from times to times.
No video yet, sorry!
Keep me updated 🙂
Hi Sylvie, if I were to follow these steps, can I ensure that my excel would not be overriding any NEW items that are loaded in the SharePoint list and did not make it to my excel because of timing? I understand that my edits in excel will affect the items I am working on, but will new items that were not in SP at the time I exported the excel be overridden? The SharePoint is a live site with lots of users, so unless I shut down the site while I make the edits in excel there would be new traffic coming in and I want to make sure I don't delete them when I synch my excel back... Thanks!
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!
Sylvie_in_France Here's my formula -HYPERLINK(urlEdit&[@ID,"Edit") I put on the cell and I do have the 2 of the urls and named them as you told like urlNew and urlEdit.
I get the error:
The syntax if this name isn't correct.
Verify that the name:
- Starts with a letter or underscore (_)
- Doesn't include a space or character that isn't allowed
- Doesn't conflict with an existing name in the workbook.
Also another question, one of my fields in the sharepoint gets info from a distribution list, will doing this affect updating the list?
- This appears to export the SharePoint list items to the Excel table. That is not what we want. We want to export our Excel table to a SharePoint list and then update SharePoint list from the Excel table whenever the Excel table is updated.
