VSTO: Attach meta-data to a cell in Excel?

Question

Hi!

I'm using VSTO to create an Excel Add-on. This add-on retrieves and display alot of data from a sql-server. This works great, but later on I plan to access some of the data inside excel and modify it in some ways. My problem is that I need a way of classify cells that I want to modify. Is there any way to add meta-data to a cell to know if it is a cell that should be modified? E.g. add a attribute to the cell, e.g. "editable_cell", and do something like Excel.FindCellsWithAttribute("editable_cell") to find the sought after cells?

Thanks! /Gustav

Answer 1

I don't recall a way to do exactly what you are asking. What I've seen done in the past was setting range names based on what you might want to look up. Another option is to hide a cell next to it or some other predetermined offset (e.g. always 3 cells to the right, or same position but on a hidden page). The hidden cell/page would have the data you would be looking for.

One thing that seems to have accidentally emerged as a best practice at the accounting firm I used to work for was that you should push all your data into an "ugly" page that is hidden and use formulas/lookups to refer to your data. That way you can update the "ugly" page and know where data is while having a "pretty page" that the users can monkey up to their hearts' content.

Answer 2

You might want to look at XML mapping: http://msdn.microsoft.com/en-us/library/aa203737(office.11).aspx

Answer 3

There are several way to do this. I do not know your specific requirements, so I will briefly outline some solutions.

• Create a Named Range, but adding/removing data can affect the defined Named Range if you don't do it right. Sometimes, it is better to define a single cell named range to act as a bookmark then "select range" will get you all the data.

• Create a Style. Apply this style to each data cell you wish to "find". Define a method that returns a Range base on which cells have the specified style.

• Create a PivotCache Object. This object has the ability to update itself, and reduces the file size, especially if the cache is used repeatedly in a workbook. It is also one way to work around the limitation in the number of rows in a worksheet.

• Create a List. This has many advantages. You can add/remove data at will. Add/remove columns. Think of a list as a table.

• Use XML Mapping (http://msdn.microsoft.com/en-us/library/aa203737(office.11).aspx) as "code4life" mentions.

• If the workbook is XMLSS, then define a new namespace and adore the cells with an attribute from the namespace. You can then "query" using XPath. Very powerful because you can embed whatever is needed into a workbook.

Each has its advantages/disadvantages. I have used each solution multiple times.