Updating SharePoint List from Excel

[Gilbert Okello]

@John White, @jcgonzalezmartin  is there a way to update a sharepoint list from Excel? (other than through 3rd party tools like SoftFluent's Sharepoint list synchronizer? SharePoint List Synchronizer)

[#2]

@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? 

[#3]

The closing square braket is missing: [@ID]
If you're not used to working with data tables and structured references, you should watch a video about it, for example https://youtu.be/C6f3_gCNz-U.

I don't know the answer of the other question.

Best

[#4]

@Sylvie_in_France 

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!

[#5]

@LauraB1858 

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!

[#6]

@Sylvie_in_France 

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.

[#7]

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/

[#8]

@bronniecplace I do not see the answer.  I want to do the same thing.  I keep an Excel list that I update weekly.  I want this list to be visible to users on SharePoint as view only.  They do not need to edit this list.  Only I need to edit the list.  But it is very important to our organization that they can view the list.  The way it works now, every time I update the list, I must create a new PDF and then make it available on SharePoint.  It is a lot of extra work for me.  It should be a simple matter to have my Excel list update a SharePoint list with the most recent changes that I made in Excel.  But this is proving to be extremely frustrating 😞 

[#9]

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.

[#10]

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 😞

[#11]

@Gilbert Okello Did you ever get a solution to this problem? I want to do the same thing but the replies I see do the opposite of what we want to do. We want our Excel table to update our SharePoint list when we make changes to our Excel table. However, the replies appear to do the opposite.

[#12]

@SusanSRG , @Gilbert Okello , @Sylvie_in_France , @LauraB1858 , @Ryuojiin 

I have the complete 100% solution with no code to do sync from  Excel to SharePoint Online or on-prem list. (100% free)

It also works from  sqlserver, ODBC and CSV.

It creates missing items and update the changes made in the Excel book to existing items in the list.

It runs on any machine including a VM in azure as a an unattended windows service that has a build in scheduler. Also when something goes wrong you can be notified on email, windowsEventLog or invoke an automated Powe Automate cloud flow.

If you are interested I can show you how to use it and help you get started.

Thanks

//Steen

[#13]

@MrNoCode  That is great!  Yes, I am interested.  I cannot get anything to work.

I just tried MS Automate (Flow) again and it says that my test was successful but it doesn't even create my list.

Please help me.

[#14]

@SusanSRG , the reason you cannot get anything to work is that the features you are looking for are simply not there.

and PowerAutomate is insufficient in every way.

Lets connect 🙂

[#15]

I have sent you a private message 2 days ago.

[#16]

Hi @MrNoCode ! Can you please share this with me as well. I'm much interested.

[#17]

yes I sent you a message

[#18]

I would love your assistance with this as well.

[#19]

Hello @MrNoCode - Could you share with me as well?

[#20]

Hi @MrNoCode this sounds interesting. I'm stuck with the same issue and am interested in your solution. would it be alright for you to share the solution with me?

[#21]

Hi, could you also provide me with this info? It'd be greatly appreciated!