Dynamically update metadata from a cell

Copper Contributor

Hi,

 

In excel I've created a link between a cell and custom properties.
The value is dynamically updated in  custom properties:

 

01.png

 

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: 

 

02.png

 

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:

 

04.png

 

Is someone able to help me out here :)?

 

Regards,

Emil

11 Replies

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 Sub

Button Macro:

Sub Rectangle1_Click()
   ActiveWorkbook.CustomDocumentProperties("Procent_1").Value = Range("rngProcent1")
End Sub

 

 

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.

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 + 1Next

 

 

Please let me know if you have solved this issue.

@Emil Rindell 

@Mudskipper 

 

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.   

@Emil Rindell 

Hi Emil,

it  is part of my  macro , may be your college will use it to help you. ( Sorry, Cyrillic letters  will not proper print in those site

 

Sub Ìàêðîñ1()

Sheets("Çàïðîñ").Activate
Dim a As String
a = Cells(3, 3)
ActiveWorkbook.ContentTypeProperties("Çàÿâèòåëü").Value = a
Dim b As String
b = Cells(4, 3)
ActiveWorkbook.ContentTypeProperties("Íàèìåíîâàíèå").Value = b
Dim c As String
c = Cells(5, 3)
ActiveWorkbook.ContentTypeProperties("Ó÷àñòîê").Value = c
Dim d As String
d = Cells(5, 9)
ActiveWorkbook.ContentTypeProperties("Çîíà").Value = d
Dim e As String
e = Cells(7, 2)
ActiveWorkbook.ContentTypeProperties("Îïèñàíèå").Value = e
Dim g As String
g = Cells(9, 2)
ActiveWorkbook.ContentTypeProperties("Ïðè÷èíà").Value = g
Range("AL65:AP78").Select
ActiveWindow.SmallScroll Down:=-9
Selection.Copy
ActiveWindow.SmallScroll Down:=-27
Range("AL38").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=18
Range("AL60:AN60").Select
Application.CutCopyMode = False
Selection.Copy
Range("Òàáëèöà2[èíèöèàòîð]").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

@Emil Rindell 

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?

I’ve the exact same question as well. Would love to understand what a fix could be . :grinning_face:
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.
How do I create a link from a cell to a custom property? I cannot seem where to do this.