Syncing SharePoint metadata with Excel

Question

I have a spreadsheet in a SharePoint Online library. The library has several columns of metadata, say A, B, C and D. The first two are text and next two are numbers.

I would like to do two things:

1. I have two named cells in my spreadsheet, Field1 and Field2, which are inputs in my spreadsheet model. I would like these fields to be automatically populated when I create a copy of the spreadsheet in the library. Ideally, if A or B change in SharePoint, I need Field1 and Field2 to be automatically updated.
2. Two more named cells in the spreadsheet, Field3 and Field4 are outputs of my model and I would like the SharePoint metadata C and D to be populated/ updated based on the values in these fields in my spreadsheet.

I am open to having VBA codes in my spreadsheet to achieve one or both of the tasks. Many thanks

Answer 1

This may help you develop some code to update the metadata https://gallery.technet.microsoft.com/office/Upload-a-file-to-f778988c or https://social.msdn.microsoft.com/Forums/en-US/c3c0f7fc-d7d2-4eb8-bd12-821f4af49e60/uploading-a-file-to-sharepoint-by-programming-in-vba?forum=exceldev