none
updating "Link to Content" excel custom property RRS feed

  • Question

  • Hello,

    We have following code that updates excel file custom property values. This code works for all the properties except for the properties which are marked "Link To Content"

    Is there a way to update the properties marked "Link To Content".  The below image shows how link to content is done.

    is it possible to change such linked property to a value which does not come from cell?

    object objDocProps;
                Type typeObjDocProps = objDocProps.GetType();
                string propName; //Name of the property that needs to be updated to a new value "DOC_DESC"
                string propVal; // New value of the property d1111
    
                object objPropsCount = typeObjDocProps.InvokeMember("Count",
                    BindingFlags.Default | BindingFlags.GetProperty,
                    null,
                    objDocProps,
                    new object[] { });
    
                int propCount = (int)objPropsCount;
    
                if (propCount > 0)
                {
                    object objProp = null;
                    try
                    {
                        objProp = typeObjDocProps.InvokeMember("Item",
                                        BindingFlags.Default | BindingFlags.GetProperty,
                                        null, objDocProps,
                                        new object[] { propName });
                    }
                    catch (Exception ex1)
                    {
                        
                    }
                    
                    try
                    {
                        object propval_obj = propVal;
                        Type typeObjProp = objProp.GetType();
    
                        object objpropname = typeObjProp.InvokeMember("Name",
                                                   BindingFlags.Default | BindingFlags.GetProperty,
                                                   null,
                                                   objProp,
                                                   new object[] { });
    
                        LogHelper.Instance.Write("Setting property value...");
    
                        // This line throws exception for properties that are marked "Link to Content"
                        object objpropval = typeObjProp.InvokeMember("Value",
                                                   BindingFlags.Default | BindingFlags.SetProperty,
                                                   null,
                                                   objProp,
                                                   new object[] { propval_obj });
                        // Exception is 
                        /*
                        ERROR: Exception occurred.
                    
                        Exception has been thrown by the target of an invocation.
                        mscorlib
                        at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
                           at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
                           at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args)
                        */
    
                    }
                    catch (Exception ex2)
                    {
                        LogHelper.Instance.Write("ERROR: Exception occurred.");
                        LogHelper.Instance.LogException(ex2);
                    }
            }

    Thursday, November 9, 2017 7:51 AM

All replies

  • Hello,

    To update the value for "Link to Content" custom property, we could get the name object and then get the referenced range. Update the range value, then the property value is also updated.

    Here is the sample to use early binding in VBA to update the value.

    Sub Macro1()
    Dim p As DocumentProperty
    For Each p In ActiveWorkbook.CustomDocumentProperties
    If p.LinkToContent Then
    ActiveWorkbook.Names(p.LinkSource).RefersToRange.Value = "test"
    End If
    Next
    End Sub

    According to your code, you are using late binding. Here is the example to set the value of the custom property "link" which is linked to content.

                object exlApp = Marshal.GetActiveObject("Excel.Application");
                object wbs = exlApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, exlApp, null);
                object wb = wbs.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, wbs, new object[] { 1 });
                object propers = wb.GetType().InvokeMember("CustomDocumentProperties", BindingFlags.GetProperty, null, wb, null);
                object objPropsCount = propers.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, propers, null);
                int propCount = (int)objPropsCount;
                if (propCount > 0)
                {
                    object proper = propers.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, propers, new object[] {"link"});
                    object getValue = proper.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, proper, null);
                    object getLink = proper.GetType().InvokeMember("LinkToContent", BindingFlags.GetProperty, null, proper, null);
                    if ((Boolean)getLink == true)
                    {
                        object linkSoure= proper.GetType().InvokeMember("LinkSource", BindingFlags.GetProperty, null, proper, null);
                        object names = wb.GetType().InvokeMember("Names", BindingFlags.GetProperty, null, wb, null);
                        object name = names.GetType().InvokeMember("Item", BindingFlags.InvokeMethod, null, names, new object[] { linkSoure });
                        object rng =name.GetType().InvokeMember("RefersToRange", BindingFlags.GetProperty, null, name, null);
                        rng.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, rng, new object[] { "newValue" });
                    }
                }

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 10, 2017 5:33 AM
    Moderator