Storing extra information in Excel Cells

[zathras]

I'm working on COM Add-in for Excel.

This application allows you to type in a nominal account number and then the application goes off and finds details of the account and inserts it into the Spreadsheet.

This nominal account is set up in a tree structure. So an account may have child accounts or sibling accounts. I need to set it up so that when you right click on an account (or account description for that matter). It gives the option.

Part of the information inserted into the sheet is the budgetting. So certain columns may store the budget for January, Feb etc. When the user double clicks on this I might want it to Zoom either to the underlying transactions or to a finer grained budget say Month -> Week -> Day -> Individual transactions.

The problem I have is that to make this work I need some form of meta-data in a cell. Say What it is (Account Number, Budget etc), Record identifier, etc.

Anybody got an idea how one might store Meta-data in a cell with the sheet itself (rather than in some form of database)

tools are VS.Net 2005/Excel 2003/7 (yes I'm on the Beta Programme )

[NoddY]

...tools are VS.Net 2005/Excel 2003/7 (yes I'm on the Beta Programme )

"I'm sorry Sir, we don't support beta software on this forum. Is there anything else we can interest you in? Have you considered our 0% on balance transfers? Perhaps our 15% discount with Thomas Cook? OK Sir thank you, good bye."

[zathras]

"I'm sorry Sir, we don't support beta software on this forum. Is there anything else we can interest you in? Have you considered our 0% on balance transfers? Perhaps our 15% discount with Thomas Cook? OK Sir thank you, good bye."

Does that mean you don't know then?


Oh well hows about VS2005 and Excel 2003!

[eternalnomad]

would the cell comments be of any use ?

[zathras]

would the cell comments be of any use ?

Might be but is their anything less visible to the user. (or for that matter that can stop the user from trampling their size 10 boots over.

[BoredBloke]

You can turn off the comment indicator in options. So the comment get applied to the cell but the user does not see it. They would however be able to overwrite the comment though.

[zathras]

You can turn off the comment indicator in options. So the comment get applied to the cell but the user does not see it. They would however be able to overwrite the comment though.

That's the problem if the application falls over because the user does something that is what I'm trying to avoid.

The line I've been thinking is using XMLMaps but backwards. Instead of the Map controlling what is in Excel. Excel controls what is in the map. Then when account information is inserted into Excel the map is updated, together with the information missing.

As MAPS are XML files it might work but

a) I'm not sure if you can use MAPS this way

b) How to do it if you can.

[xoggoth]

The comments thing might be ok if you can fit what you want in 255 chars. Otherwise, why not use VBA to fetch the additional information you want from a hidden sheet? Nothing special in way of data input needed that way - just update your details sheet and hide it from the user. If the approach sounds of use I could soon come up with up some code for you. (But you mustn't say anything nasty about Fosters again)

[MarillionFan]

"You can turn off the comment indicator in options. So the comment get applied to the cell but the user does not see it. They would however be able to overwrite the comment though."

Comments seem a good bet to me without having to rely on loading data into memory arrays, holding elsewhere etc.


Even if they turn comments off and cannot see the metadata, your drill down function can still pick it up(assuming you;ve coded it). In addition, locking down the cells if will stop them editing the data.

If you are using code, lock the comments down programatically

[scotspine]

sounds like a case for dr. net