Data persistence without using sheet or external file

[splashscreen]

Sorry, wrong section! Could of the mods move it to excel please?

Hi, I'm looking for a way to save user input during execution of a macro, in such a way that the data is available for the macro in the next session (ie closing and restarting of excel), but isn't stored in an external file or (visibly) on one of the workbook sheets. (also no use of hidden rows etc).

One way I tought of is to place an embedded object on one of the sheet, make it invisible and somehow store data in it, but I havent found a suitable object yet

Anyone solved this type of problem before?
(Im probably just overlooking some really obvious solution or mechanism for this...)

[lucas]

Sorry, wrong section! Could of the mods move it to excel please?

Hi, I'm looking for a way to save user input during execution of a macro, in such a way that the data is available for the macro in the next session (ie closing and restarting of excel), but isn't stored in an external file or (visibly) on one of the workbook sheets. (also no use of hidden rows etc).

One way I tought of is to place an embedded object on one of the sheet, make it invisible and somehow store data in it, but I havent found a suitable object yet

Anyone solved this type of problem before?
(Im probably just overlooking some really obvious solution or mechanism for this...)

So you couldn't put it on one of your sheets and format the fonts to white either?

[splashscreen]

I could, but I'm looking for a 'clean(er)' solution. Btw, by visibly I mean, maybe its possible to hide a sheet .. but I'd rather not do it like that.

[Norie]

You could store values in the registry.

Take a look at SaveSetting and GetSetting.

[splashscreen]

Ok, that could work, though the information can't be shared (its a shared workbook).

[Norie]

I think we need some more information.

What are you actually trying to do?

Why can't you use an external file or a hidden sheet?

[splashscreen]

The reason for these requirements is that I want to have an excelfile with as little 'operational' clutter/constraints as possible (ie no extra files needed to use it, no invisible settings on the sheet that can inadvertently be erased etc).

The idea is to use a setup that is as robust as possible, so people can freely share it without having to know/worry about breaking underlying code.

I was hoping excel had some built-in persistent datastore (or some equivalent hack to be possible) that can only be accessed programmatically and not through the usual userinterface (to keep a high degree of seperation between user and code).

But I think I will go for the Sheet.Visibilty = xlSheetVeryHidden option as this comes close to what Im looking for. Unless someone else has better idea I will set it as solved

ps i hope im making some sense

[xCav8r]

Would an admin please move this Excel garbage out of our pristine Access forum?

[splashscreen]

Would an admin please move this Excel garbage out of our pristine Access forum?

io!

[xCav8r]

Oh, btw, this wouldn't be hidden, but you could put data in a custom document property for the workbook.

[xCav8r]

io!

I was only kidding.

[splashscreen]

ah thx!

How would this be not hidden for the user?

[xCav8r]

Because you can view it by going to properties on the file menu (from within Excel or Windows Explorer). But it's effectively hidden since no one ever looks at that.

[splashscreen]

Many thanks <img>

[xCav8r]

Plz, can an admin move this thread to Excel?