Storing meta data in an Excel.Range object

Question

I am integrating excel with an external service which involves receiving data from that service and showing in excel. I want to be able to store the information I get in the Excel.Range object. Is there any property of an Excel.Range object where one can store meta data?

To clarify, like in Outlook an Outlook.TaskItem has ItemProperties which is an Outlook.ItemProperty Object. So is there anything similar like that in Excel?

And if not, then what is the best way to store meta data for a Excel.Range?

EDIT: I need to persist this meta data information. So if a user saves, closes and then re opens the workbook, I need to be able to extract this meta data from the Excel.Range object (or any other property)

score 2 · Accepted Answer · answered Mar 27 '16 at 08:37

Since you need the information to be persistent, I was using a simpler and clearer approach. Create a new WorkSheet, call it something like [YourSheetName]Metadata (in case you have multiple of this kind). Set it to VeryHidden (xlSheetVeryHiddencan't be Unhidden from with Excel you have to unhide it from code):

xl.XlSheetVisibility.xlSheetVeryHidden

Save all your metadata for a Range R1 in metadata sheet in Range R1. Your code will be very simple and clear in that way.

It may look something like:

Sheet1.Range[row,col].Value = SomeValue;
Sheet1Metadata.Range[row,col].Value = MetaDataOfSomeValue;

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

I am not sure if there is a such property even if, I was developing my own custom Range Metadata class. In that way, you have the flexibility to do whatever you need or may need later.

Following is a starting example of how you can do it:

class RangeListMetaData
{
    private readonly List<RangeMetaData> _rangeList;

    public RangeListMetaData()
    {
        _rangeList = new List<RangeMetaData>();
    }

    public void Add (RangeMetaData rangeMetaData)
    {
        _rangeList.Add(rangeMetaData);
    }
}


class RangeMetaData
{
    public RangeMetaData(xl.Range range)
    {
        this.Range = range;
    }

    public RangeMetaData(xl.Range range, object value) : this(range)
    {
        this.RangeValue = value;
    }

    public xl.Range Range { get; private set; }

    public object RangeValue { get; set; }
}

class TestRangeMetaData
{
    void Test()
    {
        var rangeListMetaData = new RangeListMetaData();

        // storing part
        RangeMetaData range = new RangeMetaData([Your excel Cells], [You Value]);
        rangeListMetaData.Add(range);

        // Retrieve Part
        rangeListMetaData.FindByRange(...);
        rangeListMetaData.FindByValue(...);
        rangeListMetaData.FindBySomethingElse(...);
    }
}

Since you are working with interops, you need to take care to release COM objects. For that, you can rely to another post, see my answer there

edited May 23 '17 at 12:15

Community

1
1

answered Mar 27 '16 at 06:15

ehh

3,412
7
43
91

The main problem I am facing is that I want this metadata to persist. Once the user saves, closes and re opens the workbook, I need to be able to extract this meta data from each cell. So, referring to your solution, where do I persist this RangeListMetaData and RangeMetaData? – Furqan Tariq Mar 27 '16 at 06:35
@Furqan Tariq, this is an important information you didn't mention in your question. My solution didn't solve this point. However, you could take it one step forward and save the info in xml, db... But now, I am not sure this is the best solution. – ehh Mar 27 '16 at 06:39
I have made the edit in the question now. The solution I am now leaning towards is saving the information in CustomProperties of an Excel.Sheet. Instead of storing individual metadata in each Range object, I store all the metadata in a custom property and later process as desired – Furqan Tariq Mar 27 '16 at 06:46
Ok, I am not sure what you are trying to achieve but just take in consideration that you won't be able to query, gets the information (metadata) until you open the excel file. – ehh Mar 27 '16 at 06:52
My flow does require the user to open excel file. So I think this solution of custom property could work. – Furqan Tariq Mar 27 '16 at 06:54
1

You can also create an hidden worksheet, call it metadata, and store whatever you need for each range. You can set the excel sheet to be very hidden meaning that the user will not be able to unhide it, but only through code – ehh Mar 27 '16 at 06:56

Storing meta data in an Excel.Range object

2 Answers2