PDA

View Full Version : [SOLVED] Data persistence without using sheet or external file



splashscreen
07-24-2005, 11:24 AM
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
07-24-2005, 11:48 AM
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
07-24-2005, 12:04 PM
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
07-24-2005, 01:05 PM
You could store values in the registry.

Take a look at SaveSetting and GetSetting.

splashscreen
07-25-2005, 12:46 PM
Ok, that could work, though the information can't be shared (its a shared workbook).

Norie
07-25-2005, 01:03 PM
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
07-26-2005, 12:27 PM
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 :D

xCav8r
07-26-2005, 12:31 PM
Would an admin please move this Excel garbage out of our pristine Access forum? :whip ;)

splashscreen
07-26-2005, 12:45 PM
Would an admin please move this Excel garbage out of our pristine Access forum? :whip ;)

io!

xCav8r
07-26-2005, 12:45 PM
Oh, btw, this wouldn't be hidden, but you could put data in a custom document property for the workbook.

xCav8r
07-26-2005, 12:48 PM
io! I was only kidding. :yes

splashscreen
07-26-2005, 12:55 PM
:D

ah thx!

How would this be not hidden for the user?

xCav8r
07-26-2005, 12:56 PM
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
07-26-2005, 12:59 PM
Many thanks <img>

xCav8r
08-02-2005, 10:05 PM
Plz, can an admin move this thread to Excel?