RE: DDE Link Change Event

From: Geoff Darst (geoffda_at_microsoft.com)
Date: 08/09/04 

Date: Mon, 09 Aug 2004 17:11:43 GMT

Hi Dave,

You write:

>How do I determine if a DDE link has updated on a work***?
>Excel.DocEvents_ChangeEventHandler does not fire when DDE links
>update.

It looks like this may still be a bug in Excel. This behavior is documented in the following Knowledge Base article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;172832 . Apparently, this issue started cropping up in Excel 97 and I'm not seeing anything that
confirms it has been fixed as of yet. Assuming that this bug is still out there, you can use the workaround provided by the article:

WORKAROUND
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied
warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being
demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular
procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited
programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more
information about Microsoft Certified Partners, please visit the following Microsoft Web site:
http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

You can create a Visual Basic event macro that runs whenever the data on a work*** is changed. To use the following example, run the LinkList macro.
When your DDE link is updated, the macro LinkChange will automatically run. Sub LinkList()
   Dim Links As Variant
   ' Obtain an array for the links to Excel workbooks
   ' in the active workbook.
   Links = ActiveWorkbook.LinkSources(xlOLELinks)
   ' If the Links array is not empty, then open each
   ' linked workbook. If the array is empty, then
   ' display an error message.
   If Not IsEmpty(Links) Then
       For I = 1 To Ubound(Links)
           ActiveWorkbook.SetLinkOnData Links(i), "LinkChange"
       Next I
   Else
       MsgBox "This workbook does not contain any links " & _
       "to other workbooks"
   End If
End Sub

Sub LinkChange()
   MsgBox "linked"
End Sub

Note that the above is Vba code--I'll leave it up to you to convert it to your .Net solution.

You also write:

>Also where is the VSTO documentation?

You won't find much specific documentation on VSTO itself. What is available is can be found on MSDN: http://www.msdn.microsoft.com/library/default.asp
  In the table of contents, search under "Office Soutions Development" for "Microsoft Visual Studio Tools For Office". This may be the same documentation
that we installed on your machine.

The reason for this is that the Office object models are already well documented within the specific products. If you have installed Visual Basic For
Applications with your Office install, you will have the documentation that you need to understand the object model. I find it helpful to access this
documentation from the Object Browser (the Vba Object Browser, not the Visual Studio one). If you launch Excel or Word, press Alt-F11 to bring up the Vba
IDE, you can then press F2 to bring up the Object Browser. As you click on the various objects and their members, you can press the help button (looks like
a question mark) in the Object Browser's Toolbar to access the documentation.

Although this documentation will explain how the object model works, you will have to adapt it to VSTO programming yourself. This includes porting any of
the sample code to Visual Basic .Net or C# (depending on what you are using).

>For example how to I implement Excel.DocEvents_CalculateEventHandler?
>Searching on Google found zero references to this delegate? From the
>object browser I find the Calculate event is a member of
>DocEvents_Event interface implemented by WorksheetClass but when I
>reference the Work*** this event is not available?

Yes, the eventing model in Excel is a bit strange because the work*** events don't live on the work*** itself and Excel is sourcing COM events which
are getting converted to .NET events by the managed wrapper. The end result (as you have discovered) is that the Calculate event is sourced by the
VSTO Work*** class which you can't get to. The good news is that the event delegate is actually exposed on the Excel.Application class (which you can
get to via the thisApplication member of the OfficeCodeBehind class).

If you are using C#, you can sync the event by adding the following code to the Workbook_Open handler:

                        thisApplication.SheetCalculate+=new Microsoft.Office.Interop.Excel.AppEvents_SheetCalculateEventHandler
(thisApplication_SheetCalculate);

Your event handler might look something like this:

                private void thisApplication_SheetCalculate(object Sh)
                {
                        Excel.Work*** work*** = Sh as Excel.Work***;
                        MessageBox.Show(String.Concat(work***.Name, ": OnCalculate"));
                }

There is also a technical article that discusses how events work in Excel on MSDN. See: http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/odc_vsto2003_ta/html/odc_vsthookexl.asp .

Hope this helps.

Sincerely,

Geoff Darst
Microsoft VSTO Tools 

-- 
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at 
http://www.microsoft.com/info/cpyright.htm 
Note:  For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.  
--------------------