Jun 25 2018 11:11 PM
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
Jun 28 2018 07:55 AM
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
Jan 06 2019 11:34 PM
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?
Jan 07 2019 02:21 AM
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!
Feb 18 2019 07:02 AM
If data in cell filled by formula it not update. Create macro in order to fill cell.
Mar 26 2019 10:21 AM
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.
Mar 28 2019 03:51 AM
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.
Mar 28 2019 04:14 AM
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
Feb 28 2020 08:55 AM
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?
Jun 29 2020 01:24 PM
Sep 08 2020 07:47 AM
Feb 11 2022 08:20 AM