Hi faffo198,
The best way to do this is to use the registry functions offered by Excel. I know, writing stuff to the registry sounds scary, but it's safe if you use Excel's built-in functions. The three functions that you will be using are SaveSetting, GetSetting and DeleteSetting.
SaveSetting allows you to store values in the registry, GetSetting allows you to retrieve those values and you use DeleteSetting to clean up once you are done. These three functions require an application name, a section name and a key name (optional for DeleteSetting). My suggestion is to set the first two using global constants:
Global Const sAppName = "MyAddin"
Global Const sSectionName = "Config"
Then define three functions:
'Retrieve the value of the "key" registry key under sAppName\sSectionName
Public Function get_value(key As String) As String
get_value = GetSetting(sAppName, sSectionName, key)
End Function
'Set the value of the "key" registry key under sAppName\sSectionName
Public Sub set_value(key As String, key_value As String)
SaveSetting sAppName, sSectionName, key, key_value
End Sub
'Delete the sAppName\sSectionName registry path (will delete all keys in that path too)
Sub clear_registry()
On Error Resume Next
DeleteSetting sAppName, sSectionName
On Error GoTo 0
End Sub
And you can now save and restore configuration settings in a persistent manner. Example:
Sub LastUsed()
dim last_used as string
'Retrieve last used date
On Error Resume Next
last_user = get_value("LastUsed")
On Error GoTo 0
MsgBox "This add-in was last used on " & last_used
'Set new last used date
set_value "LastUsed", CStr(Now)
End Sub
Sub CleanUp()
'Erase all registry keys stored in sAppName\sSectionName
'Don't use if you wish for the data to persist after you close Excel
clear_registry
End Sub
Cheers,
Benoit
Hi all,
I need to store persistent configuration settings of a custom Excel 2003
add-in.
Which is best way to do this in your opinion?
I can suppose these possible solutions:
1) write configuration settings in a text file and load it every time is
needed
2) write settings in a "hidden" cell or worksheet (but I do not like this);
is there another way to store this information inside the .xls file?
Thanks in advance,
faffo1980
On Monday, August 10, 2009 6:07 AM Joe wrote:
1) In a hidden worksheet that is protect. Make the cells readable but not
writable
2) Store text in a module as comments. Again you can make th emodule
invisable. the text can be read from VBA code.
"faffo1980" wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: Excel 2010 - The Missing Manual [OReilly]
http://www.eggheadcafe.com/tutorial...w-excel-2010--the-missing-manual-oreilly.aspx
