Forum Discussion
Dynamically update metadata from a cell
Hi,
In excel I've created a link between a cell and custom properties.
The value is dynamically updated in custom properties:
How ever it is only created once in the files metadata and it does not update when saving the document or make any changes in the cell. 28,57% was the first value created from the linked cell, the value in the cell has now changed to 14,28 % in custom properties but is not updated in the metadata:
I want it to act the same way as the "Size" does. I want to use the metadata in a column in sharepoint which shows the status in a project:
Is someone able to help me out here :)?
Regards,
Emil
11 Replies
- How do I create a link from a cell to a custom property? I cannot seem where to do this.
- I’ve the exact same question as well. Would love to understand what a fix could be . 😀
- I had a similar problem so I used VBA to run and save on closing the file.
"Sub Auto_Close()"
Remove the quotes and replace the names in below
ActiveWorkbook.ContentTypeProperties("Your_Metadata_Name").Value = Range("Your_Cell_Value_In_Excel_File").Value
Above will update the Metadata.
ActiveWorkbook.Save
End Sub
You may need to tweak the save part depending on your setup. Your columns should now update according to your metadata.
I found the exact same issues with something I am looking to do! I am looking to populate excel file metadata in SharePoint 365 from specific cells within the excel file. I am able to create the linked Advanced Properties within the file, import it into SharePoint, and auto populate the values on the initial load. The problem is, when users update the information in the linked cell it shows in the Properties setup window, but not in the Advanced Properties dropdown (on Info screen). It seems like this is only ever updated once and I can't seem to get it to refresh.
Utilizing VBA I was able to view these properties and could see that they were stored correctly, but it was not updating the Advanced Properties dropdown.
VBA used to check:
rw = 1 Worksheets(1).Activate ForEach p In ActiveWorkbook.CustomDocumentProperties Cells(rw, 1).Value = p.Name Cells(rw, 2).Value = p.Value rw = rw + 1NextPlease let me know if you have solved this issue.
Hi,
Thanks for your answer. Sorry no I haven't solved this issue yet. A colleuge of mine (wich have great skills in excel) tried this as well without any success. I'm guess that the issue is related to Sharepoint Online. However this could might be solved using Microsoft Flow but i'm not sure.
Please let me know if you find a solution to this. Or if I'm able to solve it I will post it in this thread.
Hello, were you able to come up with a solution to this? I agree, this does appear to be on the SharePoint 365 side. I have added indexes to these metadata columns in SharePoint. Would Column Validation help?
Hi Emil Rindell,
I have also tried this, but had the same problem as you with the properties not updating.
Did you find a solution for this problem?
Hi harbak,
Sorry, but no I tried all kinds of workarounds but in the end I did not got this to work as I wanted.
I been thinking of using Flow to solve it, but have not tried anything yet due to lack of time.
Please let me know if you're able to find a solution to this!
If data in cell filled by formula it not update. Create macro in order to fill cell.
Hey Emil-
This seems odd. I wonder if the data on SharePoint is refreshing properly. Will the value update if the Custom Property is hardcoded? If so you may be able to use a worksheet change event to update the value or perhaps a button with a macro linked to it. You may test this file (see attached) and see if that will work for your scenario.
If the linked custom property doesn't update then it would be kind of useless. Seems kind of glitchy if that's the case.
Change Event Macro:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("rngProcent").Address Then 'Update the value of the hardcoded custom property to match that of 'the linked custom property.... ActiveWorkbook.CustomDocumentProperties("Procent").Value = Range("rngProcent1") End If End SubButton Macro:
Sub Rectangle1_Click() ActiveWorkbook.CustomDocumentProperties("Procent_1").Value = Range("rngProcent1") End Sub
