add in persistent properties

  • Thread starter Thread starter faffo1980
  • Start date Start date
F

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
 
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
 
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
 
Thanks Keith!
I will try using custom document properties for my solution.

Regards,

faffo1980
 
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)
 
Back
Top