Hi

I'm developing a COM Addin for Excel, which will be communicating data
with a document handling system using Custom Document Properties.
I've already done this in Word but now I find that it't not implemented in
Excel in the same fashion.

Is there a way to "Insert" data from the spreadsheets Custom properties
into Cells and retain the automatic updating?
I know this works in VBA creating av function which gets the propertyvalue and
return it, but is this possible in a COM Addin.
We don't want to use macros.

Thanks in advance

--
Peter Karlström
Midrange AB
Sweden

Hello Peter,

I notice that you have posted the same question in our
microsoft.public.office.developer.com.add_ins newsgroup, which I have
already responded. So please check my answer there and if you need any
further assistance on this particular issue, please reply to me in that
thread so I can follow up with you in time.

For your convenience, I have included my reply as follows:

From your post, my understanding on this issue is: you want to know how to
display a custom property value automatically when the property is changed
in a COM add-in for Excel. If I'm off base, please feel free to let me know.

I also notice that you once pasted the same question in newsgroup
microsoft.public.excel.worksheet.functions, and a community member
explained how to do it in VBA macro:
Function balance() As Variant
balance = ActiveWorkbook.CustomDocumentProperties("Balance") .Value
End Function

In order to do it in COM addin, I think we could register the document open
event and update the cell's value according to the Customer Document
properties in the event handler. When an Excel workbook is opened, it is
not allowed to modify the custom document properties outside the workbook,
so there are two scenarios where we need to update the cells:
1. When a workbook is opened.
2. When the custom document property is changed inside the workbook (e.g,
in macro or COM add-in code)

For the first scenario, we could add the event handler for Workbook open:
applicationObject.WorkbookOpen += new
Microsoft.Office.Interop.Excel.AppEvents_WorkbookO penEventHandler(applicatio
nObject_WorkbookOpen);

void applicationObject_WorkbookOpen(Microsoft.Office.In terop.Excel.Workbook
Wb)
{
DocumentProperties properties =
(DocumentProperties)Wb.CustomDocumentProperties;
if (properties["Balance"] != null)
{
Excel.Worksheet sheet1 = (Excel.Worksheet)Wb.Worksheets[1];
Excel.Range range = (Excel.Range)sheet1.Cells[1, 1];
range.Value2 = properties["Balance"].Value;
}
}
In the code above, I suppose you have a custom document property:
"Balance", and I set the cell A1 in worksheet1 as its value.

For the second scenario, we could programmatically update the cell after
the code that change the custom document property. For instance,
DocumentProperties properties =
(DocumentProperties)Wb.CustomDocumentProperties;
properties["Balance"].Value = "123"; // the "Balance" property is changed
Excel.Worksheet sheet1 = (Excel.Worksheet)Wb.Worksheets[1];
Excel.Range range = (Excel.Range)sheet1.Cells[1, 1];
range.Value2 = properties["Balance"].Value // update the cell's value
according to the new property value

Please let me know if you have any other concerns, or need anything else.

Thanks and have a nice day!

Sincerely,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.