add in persistent properties

[faffo1980]

Hi,
I'm developing an Excel AddIn with VSTO 2008.
I would like to add to the workbook some addin specific properties and make
them persistent.
Which is the best way in your opinion?
The simplest solution is to make a hidden worksheet containg these
properties but I don't like it very much.
Is there another way?

Thanks for your help,

faffo1980

 

[Jim Thomlinson]

You can write your settings to the registry...

http://www.j-walk.com/ss/excel/tips/tip60.htm

 

[faffo1980]

Hi Jim,
thanks for your reply but I think I have not described my problem correctly:
the properties are addin specific because they should be interpreted only by
the addin but every workbook could have different values for these properties
(they should be data workbook interpreted only by the add-in).

Thanks again

faffo1980

 

[ker_01]

Are you interested in adding your properties to the add-in, or the actual
user workbooks? If it is the latter, consider using custom document
properties- in VBA they are easy to use (I don't have VSTO2008, but I assume
it is easy there too). These are generally not noticable to most users, they
will be workbook-specific (meaning the user can have two different workbooks
and have different settings on each), and your settings will travel with the
workbook even if opened on different PCs. If you need to have one set of
settings that affect every workbook for the same user, then you need to store
your values in your add-in or the registry.

HTH,
Keith

 

[faffo1980]

Thanks Keith!
I will try using custom document properties for my solution.

Regards,

faffo1980

 

[Chip Pearson]

If the properties are specific the add-in itself (not specific to any
particular workbook that might be processed by that add-in), you can
either save the values in the system registry or create a
<Serializable()> class, serialize that to XML and then deserialize
when the add-in is loaded.

If the properties are specific to the workbook processed by your
add-in, then you could save those values to an xlVeryHidden worksheet
or in hidden Defined Names. E.g.,

' Hidden Defined Names
Dim WB As ExcelWorkbook
WB = XLApp.ActiveWorkbook
With WB.Names
.Add "PropName1", 123, False
.Add "PropName2", 345, False
End With

where XLApp is the reference to the XL Application passed in during
OnConnection.

To use a hidden sheet, use code like

' Hidden Sheet
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

WB = XLApp.ActiveWorkbook
Try
WS = WB.Worksheets("MyHiddenSheet")
Catch
WS = WB.Worksheets.Add()
End Try
WS.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
WS.Range("A1").Value = "PropValue1"
WS.Range("A2").Value = "PropValue2"
WB.Save

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)