Hi,

How can I make Excel save my metadata as part of a
spreadsheet file?

I'm working on a plug-in to allow some data-retrieval from
a web application into Excel. I need to persist metadata
in the workbook (data selection criteria, layout, etc) in
order to refresh the data later.

One obvious way to do this is to create a function and set
arguments containing the criteria. However, this is not
very good as the settings don't pertain to a single cell,
an excel formula is limited to 30 arguments, and the
formula would show up in the formula bar...

I would like the only noticable result of persisting my
settings to be the creation of a named range. The user
should then be able to edit settings by setting the active
cell anywhere in that range and choose a command on a
custom menu. I would therefore like the persisted
information to be somehow associated with a range.

Ideas and suggestions are very welcome. Happy coding!

Dag Johansen
email: DagOystein.Johansen AT sage.com

ps!

In case anyone wonders: " AT " in my email address above
is to be replaced by "@". Spammers' scripts crawl NGs and
other web pages, parsing out strings that match the e-mail
pattern, forcing me to either be spammed or do boring
workarounds like that.

Maybe a hidden worksheet with each cell shadowing a cell on another worksheet???

But if you allow the user to insert/delete rows (or just sort the data), then
this won't work?

If the number of cells you want to keep track of is small, maybe define some
range names and use that as an index on that other worksheet.

In excel2002, there's a .id property of the range object. But it gets forgotten
if you save the workbook as .xls (but remembered if you save it as .html).

Maybe you could use the comment field. It'll travel with the cell.

Dag Johansen wrote:

Hi,

How can I make Excel save my metadata as part of a
spreadsheet file?

I'm working on a plug-in to allow some data-retrieval from
a web application into Excel. I need to persist metadata
in the workbook (data selection criteria, layout, etc) in
order to refresh the data later.

One obvious way to do this is to create a function and set
arguments containing the criteria. However, this is not
very good as the settings don't pertain to a single cell,
an excel formula is limited to 30 arguments, and the
formula would show up in the formula bar...

I would like the only noticable result of persisting my
settings to be the creation of a named range. The user
should then be able to edit settings by setting the active
cell anywhere in that range and choose a command on a
custom menu. I would therefore like the persisted
information to be somehow associated with a range.

Ideas and suggestions are very welcome. Happy coding!

Dag Johansen
email: DagOystein.Johansen AT sage.com

ps!

In case anyone wonders: " AT " in my email address above
is to be replaced by "@". Spammers' scripts crawl NGs and
other web pages, parsing out strings that match the e-mail
pattern, forcing me to either be spammed or do boring
workarounds like that.

--

Dave Peterson