Worksheet.CustomProperties

[Derrick]

Hello all;

I'm messing around with Visual Studio Tools for Office 2003, particularly
Excel.

Was wondering if the Worksheet.CustomProperties collection has to be indexed
by an integer, or can you use the name of the property you are trying to
access? Since I don't think I can guarantee the order in which the
properties are added, I don't want to use integer indexing (though I could
loop through and check the property Name).

Specifically, this code snippet (which I fire from a CommandBar button)
always throws a Type Mismatch exception (hope you don't mind C#). Note that
the CustomProperty.Count is incrementing as expected; it blows up on the 2nd
line within the Try:

private void TestProperties(Excel.Worksheet activeSheet)
{
try
{
int i = activeSheet.CustomProperties.Count; //just checking if a
property was actually added

Excel.CustomProperty prop = activeSheet.CustomProperties["Test"];

if((bool)prop.Value == true)
{
MessageBox.Show("Test property was true");
}
else
{
MessageBox.Show("Test property was false");
}
}
catch(System.Runtime.InteropServices.COMException e) //property doesn't
exist, add it
{
activeSheet.CustomProperties.Add("Test", true);
//MessageBox.Show(e.Message);
}
}

 

[Chip Pearson]

Derrick,

Yes, custom properties can be accessed by name. I don't know C#,
but the following works in VBA:

Debug.Print ThisWorkbook.CustomDocumentProperties("Test").Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

Just to add, worksheets don't have custom properties. Workbooks do. And as
Chip pointed out, they are called customdocumentproperties in the Excel
object model. Can't say for C#.

 

[Derrick]

Actually, in Visual Studio Tools for Office 2003, worksheets do indeed have
CustomProperties. Previous versions did not.

Cheers,

Derrick

Just to add, worksheets don't have custom properties. Workbooks do. And
as
Chip pointed out, they are called customdocumentproperties in the Excel
object model. Can't say for C#.

--
Regards,
Tom Ogilvy

Hello all;

I'm messing around with Visual Studio Tools for Office 2003, particularly
Excel.

Was wondering if the Worksheet.CustomProperties collection has to be indexed
by an integer, or can you use the name of the property you are trying to
access? Since I don't think I can guarantee the order in which the
properties are added, I don't want to use integer indexing (though I
could
loop through and check the property Name).

Specifically, this code snippet (which I fire from a CommandBar button)
always throws a Type Mismatch exception (hope you don't mind C#). Note that
the CustomProperty.Count is incrementing as expected; it blows up on the 2nd
line within the Try:

private void TestProperties(Excel.Worksheet activeSheet)
{
try
{
int i = activeSheet.CustomProperties.Count; //just checking if
a
property was actually added

Excel.CustomProperty prop = activeSheet.CustomProperties["Test"];

if((bool)prop.Value == true)
{
MessageBox.Show("Test property was true");
}
else
{
MessageBox.Show("Test property was false");
}
}
catch(System.Runtime.InteropServices.COMException e) //property doesn't
exist, add it
{
activeSheet.CustomProperties.Add("Test", true);
//MessageBox.Show(e.Message);
}
}

 

[Tom Ogilvy]

Yes, apparently we are talking about two different things with similar
names.

anyway, this reference indicates you can refer to them using an index:

http://blogs.msdn.com/eric_carter/archive/2004/11/18/265787.aspx

--
Regards,
Tom Ogilvy

Actually, in Visual Studio Tools for Office 2003, worksheets do indeed have
CustomProperties. Previous versions did not.

Cheers,

Derrick

Just to add, worksheets don't have custom properties. Workbooks do. And
as
Chip pointed out, they are called customdocumentproperties in the Excel
object model. Can't say for C#.

--
Regards,
Tom Ogilvy

Hello all;

I'm messing around with Visual Studio Tools for Office 2003, particularly
Excel.

Was wondering if the Worksheet.CustomProperties collection has to be indexed
by an integer, or can you use the name of the property you are trying to
access? Since I don't think I can guarantee the order in which the
properties are added, I don't want to use integer indexing (though I
could
loop through and check the property Name).

Specifically, this code snippet (which I fire from a CommandBar button)
always throws a Type Mismatch exception (hope you don't mind C#). Note that
the CustomProperty.Count is incrementing as expected; it blows up on

the
2nd

line within the Try:

private void TestProperties(Excel.Worksheet activeSheet)
{
try
{
int i = activeSheet.CustomProperties.Count; //just checking if
a
property was actually added

Excel.CustomProperty prop = activeSheet.CustomProperties["Test"];

if((bool)prop.Value == true)
{
MessageBox.Show("Test property was true");
}
else
{
MessageBox.Show("Test property was false");
}
}
catch(System.Runtime.InteropServices.COMException e) //property doesn't
exist, add it
{
activeSheet.CustomProperties.Add("Test", true);
//MessageBox.Show(e.Message);
}
}