0

I have created a sharepoint list which may contain hundreds of line items. I have exported the list file to .iqy in order to connect it to excel.

In excel, I enabled the enable background refresh and refresh data when opening in the connection properties. In this way, any updates on the sharepoint list would appear to my excel file.

What I am try to acheive:

Is it possible that whenever I edit the excel file, it would also update the sharepoint list?

Problem:

Whenever I update the excel file, it doesn't update the items in sharepoint list. It just updates the excel file based on the changes in the sharepoint list itself.

  • OOTB, it's a one way transaction. Perhaps you should look into MS Access instead as it's possible to write back to the actual list. – Christoffer Jan 31 '18 at 7:01
  • For complex periodic and rollup data from excel documents scenarios, a customization probably is the best and only option. It can be the technology of choise to read data from Excel (let's say ClosedXML .Net library or XLSX.js Node.js or Excel COM automation) and technology to write and update data in SharePoint (CSOM, SSOM or REST APIs) and settings layer extention. Or 3rd parties like this one – JohnKelheim Jan 31 '18 at 7:35
  • @Christoffer Thank you for shedding some light on this topic. I think I can go with the MS Access instead. Will give this a try. To JohnKelheim, Thanks as well, but I can only go for what is available in our resources. – Sevpoint Jan 31 '18 at 7:55
0

As Christoffer explained, it's a one-way refresh only. If you want two-way sync, your choices are: switch to Access, do specific development, or use a 3rd party product, such as SharePoint List Synchronizer. This product (developed by my company) installs on Excel only (no server setup) and makes the existing sync process bi-directional.

However, keep in mind that Excel doesn't support all SharePoint scenarios, which probably explains why Microsoft stopped the two-way sync between Excel and SharePoint lists about 10 years ago. For example, Excel doesn't support multiple selection in drop-down lists, or doesn't handle rich text in a way that would be compatible with SharePoint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.