Solved

Event in Automation

Posted on 2001-06-13
3
851 Views
Last Modified: 2013-11-20
I'm writing an MFC application that automates Microsoft Excel. In my application, when I click on "RUN" button, MS Excel will be activated and a workbook is opened. So, in my application, how do I capture the event that occurs when I mannually close the workbook and shut down MS Excel ?
0
Comment
Question by:teonguyen
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
GloriousRain earned 200 total points
ID: 6189379
<MSDN>
HOWTO: Catch Microsoft Excel Application Events Using VC++

Q186427

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0
Microsoft Office 2000 Developer
Microsoft Excel 2000
Microsoft Excel 97 for Windows

--------------------------------------------------------------------------------


SUMMARY
This article demonstrates how to catch Microsoft Excel 97 and Excel 2000 application events using Microsoft Visual C++ 5.0 and 6.0. Because the default implementation in MFC of IDispatch::Invoke() does not support named arguments, you must provide your own implementation of IDispatch.



MORE INFORMATION
Normally, you would perform the steps outlined in the following article in the Microsoft Knowledge Base:

Q183599 HOWTO: Catch Microsoft Word97 Application Events Using VC++
However, even when using these steps, events for Excel might not work as expected. Excel actually fires the events, but the default implementation in MFC for IDispatch::Invoke() returns errors because Excel supplies named arguments that MFC does not support.

To catch these events, you must provide your own implementation of IDispatch. The following steps demonstrate how to do this.
Steps to Create Project
Create a new dialog box-based application using the MFC AppWizard. Name your project ExcelEvents, and accept the default settings.


Add the following public member variables to your ExcelEventsDlg class in ExcelEventsDlg.h:



      COleDispatchDriver m_app;
      IConnectionPoint *m_pConnectionPoint;
      DWORD m_adviseCookie;
Add two command buttons to your dialog box and name them "Start and Setup" and "Quit and Clean Up," respectively.


Add the following code to a handler for the Start and Setup button:



      // Check to see if you've already started the server.
      if(m_app.m_lpDispatch != NULL) {
         AfxMessageBox("Server already started.");
         return;
      }

      char buf[256]; // General purpose buffer.

      // Start the Automation server.
      COleException e;
      if(!m_app.CreateDispatch("Excel.Application", &e)) {
         sprintf(buf, "Error on CreateDispatch(): %ld (%08lx)",
           e.m_sc, e.m_sc);
         AfxMessageBox(buf, MB_SETFOREGROUND);
         return;
      }

      // Make the server visible through automation.
      // i.e.: Application.Visible = TRUE
      DISPID dispID;
      unsigned short *ucPtr;
      BYTE *parmStr;
      ucPtr = L"visible";
      m_app.m_lpDispatch->GetIDsOfNames(
           IID_NULL, &ucPtr, 1, LOCALE_USER_DEFAULT, &dispID
      );
      parmStr = (BYTE *)( VTS_VARIANT );
      m_app.InvokeHelper(
         dispID, DISPATCH_METHOD | DISPATCH_PROPERTYPUT, VT_EMPTY,
         NULL, parmStr, &COleVariant((short)TRUE)
      );

      // Declare the events you want to catch.

      // {00024413-0000-0000-C000-000000000046}
      static const GUID IID_IExcel8AppEvents =
      {0x00024413,0x000,0x0000,{0xc0,0x00,0x0,0x00,0x00,0x00,0x00,0x46 } };

      // Steps for setting up events:
      // 1. Get server's IConnectionPointContainer interface.
      // 2. Call IConnectionPointContainer::FindConnectionPoint()
      //    to find the event you want to catch.
      // 3. Call IConnectionPoint::Advise() with the IUnknown
      //    interface of your implementation of the events.

      HRESULT hr;

      // Get server's IConnectionPointContainer interface.
      IConnectionPointContainer *pConnPtContainer;
      hr = m_app.m_lpDispatch->QueryInterface(
         IID_IConnectionPointContainer,
         (void **)&pConnPtContainer
      );
      ASSERT(!FAILED(hr));

      // Find the connection point for events you're interested in.
      hr = pConnPtContainer->FindConnectionPoint(
         IID_IExcel8AppEvents,
         &m_pConnectionPoint
      );
      ASSERT(!FAILED(hr));

      // Setup advisory connection.
      hr = m_pConnectionPoint->Advise(&g_XLEventDispatch, &m_adviseCookie);
      ASSERT(!FAILED(hr));

      // Release IConnectionPointContainer interface.
      pConnPtContainer->Release();
Add the following code to a handler for the Quit and Clean Up button:



      // Check whether you've started the server.
      if(m_app.m_lpDispatch == NULL) {
         AfxMessageBox("You haven't started the server yet.");
         return;
      }
      m_pConnectionPoint->Unadvise(m_adviseCookie);
      m_pConnectionPoint->Release(); //don't forgot to release pointer

      // Tell the server to quit.
      // Application.Quit()
      DISPID dispID;                   // Temporary DISPID
      unsigned short *ucPtr;           // Temporary name holder
      ucPtr = L"quit";
      m_app.m_lpDispatch->GetIDsOfNames(
           IID_NULL, &ucPtr, 1, LOCALE_USER_DEFAULT, &dispID
      );
      m_app.InvokeHelper(dispID, DISPATCH_METHOD, VT_EMPTY, NULL, NULL);

      // Release the application object.
      m_app.ReleaseDispatch();
Cut and paste the following code into ExcelEventsDlg.cpp before the Start and Setup handler:



      //=============================================================
      //=============================================================
      // A simple IDispatch implementation to catch Excel's events.
      class CMyEventDispatch : public IDispatch
      {
      public:
         ULONG refCount;

         CMyEventDispatch::CMyEventDispatch() {
            refCount = 1;
         }
         CMyEventDispatch::~CMyEventDispatch() {
         }

         // IUnknown methods.
         virtual HRESULT __stdcall QueryInterface(
               REFIID riid, void **ppvObject) {
            if(
               IsEqualGUID(riid, IID_IDispatch) ||
               IsEqualGUID(riid, IID_IUnknown)
            ) {
               this->AddRef();
               *ppvObject = this;
               return S_OK;
            }
            *ppvObject = NULL;
            return E_NOINTERFACE;
         }

         virtual ULONG _stdcall AddRef(void) {
            return ++refCount;
         }

         virtual ULONG _stdcall Release(void) {
            if(--refCount <= 0) {
               //Delete this;
               return 0;
            }
            return refCount;
         }

         // IDispatch methods.
         virtual HRESULT _stdcall GetTypeInfoCount(UINT *pctinfo) {
            if(pctinfo) *pctinfo = 0;
            return E_NOTIMPL;
         }

         virtual HRESULT _stdcall GetTypeInfo(
               UINT iTInfo, LCID lcid, ITypeInfo **ppTInfo) {
            return E_NOTIMPL;
         }

         virtual HRESULT _stdcall GetIDsOfNames(
               REFIID riid, LPOLESTR *rgszNames, UINT cNames, LCID lcid,
               DISPID *rgDispId) {
            return E_NOTIMPL;
         }

         virtual HRESULT _stdcall Invoke(
               DISPID dispIdMember, REFIID riid, LCID lcid, WORD wFlags,
               DISPPARAMS *pDispParams, VARIANT *pVarResult,
               EXCEPINFO *pExcepInfo, UINT *puArgErr) {
            char *ptr = "Unknown-Event";

            switch(dispIdMember) {
               case 0x61d: ptr = "NewWorkbook"; break;
               case 0x616: ptr = "SheetSelectionChange"; break;
               case 0x617: ptr = "SheetBeforeDoubleClick"; break;
               case 0x618: ptr = "SheetBeforeRightClick"; break;
               case 0x619: ptr = "SheetActivate"; break;
               case 0x61a: ptr = "SheetDeactivate"; break;
               case 0x61b: ptr = "SheetCalculate"; break;
               case 0x61c: ptr = "SheetChange"; break;
               case 0x61f: ptr = "WorkbookOpen"; break;
               case 0x620: ptr = "WorkbookActivate"; break;
               case 0x621: ptr = "WorkbookDeactivate"; break;
               case 0x622: ptr = "WorkbookBeforeClose"; break;
               case 0x623: ptr = "WorkbookBeforeSave"; break;
               case 0x624: ptr = "WorkbookBeforePrint"; break;
               case 0x625: ptr = "WorkbookNewSheet"; break;
               case 0x626: ptr = "WorkbookAddinInstall"; break;
               case 0x627: ptr = "WorkbookAddinUninstall"; break;
               case 0x612: ptr = "WindowResize"; break;
               case 0x614: ptr = "WindowActivate"; break;
               case 0x615: ptr = "WindowDeactivate"; break;
            }
            MessageBox(NULL, ptr, "Event was fired!!!", MB_SETFOREGROUND);
            return S_OK;
         }
      };

      CMyEventDispatch g_XLEventDispatch;
      //=============================================================
      //=============================================================
Finally, make sure that the COM libraries get a chance to initialize. Add the following code right before the Start and Setup button handler. This creates a global class that gets created at application startup, and destroyed at exit. The constructor and destructor of this class provide a handy way to perform initialization and cleanup:



      // OLE-initialization class.
      class OleInitClass {
      public:
         OleInitClass() {
            OleInitialize(NULL);
         }
         ~OleInitClass() {
            OleUninitialize();
         }
      };
      // This global class calls OleInitialize() at
      // application startup, and calls OleUninitialize()
      // at application exit.
      OleInitClass g_OleInitClass;
Compile and run.

After running the application, click Start and Setup to start Microsoft Excel and have the event notifications setup. On the File menu, click New to create a new workbook. As soon as the workbook is created, note that the NewWorkbook, WorkbookActivate, and WindowActivate events are fired respectively. When you click on a cell, note that the SheetSelectionChange event also gets fired. Experiment to learn how other events are triggered. When you are done, click Quit and Cleanup to terminate the event notifications and shut down Excel.
eedback to MSDN.Look here for MSDN Online resources.
<MSDN>
0
 
LVL 22

Expert Comment

by:ambience
ID: 6189401
Excel application object fires an event WorkbookBeforeClose (quite obvious when by its name) , you need to catch that event Workbook close event .

There WON'T be any Excel shutdown event at all , even if the user closes the Excel mainwindow , you still have a reference to it (remember) so Excel shuts down at your will , when you release the object.

How to catch the event , well for that you need to create object of AppEvents class ... :)

You feel comfortable or want more help??
0
 
LVL 22

Expert Comment

by:ambience
ID: 6189416
He just gave you everything .. while i was typing .. yikes ... there is however an easier way than using the switch logic in invoke and doing all that work in CMyEventDispatch . You can add a class that implements IDispatch and then add handler-methods to it , just make sure that whatever name you give to a method , its DISPID should match the DISPID of the event that it is handling ..

for example
if WorkbookBeforeClose has a dispid 0x699 , you can add OnWorkbookBeforeClose with the same signature same dispid, and then add whatever you want to do in that method.

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now