HOWTO: Automate Excel Using MFC and Worksheet Functions

• The Microsoft Foundation Classes (MFC), used with:
  • Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0
  • Microsoft Excel 97 for Windows
• Microsoft Excel 2000

SUMMARY

This article shows how to automate Microsoft Excel 97 Microsoft Excel 2000 using the Microsoft Foundation Class (MFC) library, version 4.2 (installed with Microsoft Visual C++ versions 5.0 and 6.0). Specifically, this article illustrates how to use worksheet functions provided by an add-in such as the Analysis ToolPak (ATP) and how to use the worksheet formula functions that are built-in to Microsoft Excel.

MORE INFORMATION

You can copy the code in this article to the message handler function of an event defined in an MFC .cpp file. However, the purpose of the code is to illustrate the process of using the IDispatch interfaces and member functions defined in the Excel8.olb for Excel 97 or the Excel9.olb for Excel 2000 type library. The primary benefit comes from reading and understanding the code in the example, so that you can modify the example or write code from scratch to automate a worksheet function in Microsoft Excel using MFC.

Steps to Create the Project

1. Follow steps 1 through 12 in the following Microsoft Knowledge Base article to create a sample project that uses the IDispatch interfaces and member functions defined in the Excel8.olb or Excel9.olb type library:




Q178749 HOWTO: Create an Automation Project Using MFC and a Type Library

2. At the top of the AutoProjectDlg.cpp, add the following line:




      #include "excel8.h" 

If you are automating Excel 2000, include excel9.h.


3. Add the following code to CAutoProjectDlg::OnRun() in the AutoProjectDLG.cpp file:


Sample Code:

      try
      {
      _Application app;     // app is an _Application object.
      _Workbook book;       // More object declarations.
      _Worksheet sheet;
      Workbooks books;
      Worksheets sheets;
      Range range;          // Used for Microsoft Excel 97 components.
      LPDISPATCH lpDisp;    // Often reused variable.

      // Common OLE variants. Easy variants to use for calling arguments.
      COleVariant
        covTrue((short)TRUE),
        covFalse((short)FALSE),
        covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

      // Start Microsoft Excel, get _Application object,
      // and attach to app object.
      if(!app.CreateDispatch("Excel.Application"))
       {
        AfxMessageBox("Couldn't CreateDispatch() for Excel");
        return;
       }

      // Set visible.
      app.SetVisible(TRUE);

      // Register the Analysis ToolPak.
      CString sAppPath;
      sAppPath.Format ("%s\\Analysis\\Analys32.xll", app.GetLibraryPath());

      if(!app.RegisterXLL(sAppPath))
        AfxMessageBox("Didn't register the Analys32.xll");

      // Get the Workbooks collection.
      lpDisp = app.GetWorkbooks();     // Get an IDispatch pointer.
      ASSERT(lpDisp);
      books.AttachDispatch(lpDisp);    // Attach the IDispatch pointer
                                       // to the books object.

      // Open a new workbook and attach that IDispatch pointer to the
      // Workbook object.
      lpDisp = books.Add( covOptional );
      ASSERT(lpDisp);
      book.AttachDispatch( lpDisp );

      // To open an existing workbook, you need to provide all
      // 13 arguments for the Open member function.
      // The code below opens a workbook and adds it to the Workbook's
      // Collection object.
      // You need to modify the path and file name for your own
      // workbook.
      // 
      // lpDisp = books.Open("C:\\Test",     // Test.xls is a workbook.
      // covOptional, covOptional, covOptional, covOptional, covOptional,
      // covOptional, covOptional, covOptional, covOptional, covOptional,
      // covOptional, covOptional );   // Return Workbook's IDispatch
      // pointer.

      // Get the Sheets collection and attach the IDispatch pointer to your
      // sheets object.
      lpDisp = book.GetSheets();
      ASSERT(lpDisp);
      sheets.AttachDispatch(lpDisp);

      // Get sheet #1 and attach the IDispatch pointer to your sheet
      // object.
      lpDisp = sheets.GetItem( COleVariant((short)(1)) );
                                        //GetItem(const VARIANT &index)
      ASSERT(lpDisp);
      sheet.AttachDispatch(lpDisp);

      // Fill range A1 with "1/25/98", the settlement date.
      lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
      ASSERT(lpDisp);
      range.AttachDispatch(lpDisp);
      range.SetValue(COleVariant("1/25/98"));

      // Fill range A2 with "11/15/99", the maturity date.
      lpDisp = sheet.GetRange(COleVariant("A2"), COleVariant("A2"));
      ASSERT(lpDisp);
      range.AttachDispatch(lpDisp);
      range.SetValue(COleVariant("11/15/99"));

      // Fill range A3 with "2", the frequency for semi-annual interest
      // payments.
      lpDisp = sheet.GetRange(COleVariant("A3"), COleVariant("A3"));
      ASSERT(lpDisp);
      range.AttachDispatch(lpDisp);
      range.SetValue(COleVariant("2"));

      // Fill range A4 with 1, the basis (actual/actual).
      lpDisp = sheet.GetRange(COleVariant("A4"), COleVariant("A4"));
      ASSERT(lpDisp);
      range.AttachDispatch(lpDisp);
      range.SetValue(COleVariant("1"));

      // Fill range C1 with the formula "=COUPNCD(A1, A2, A3, A4)" and
      // format the cell with a Date type of the Number format.
      lpDisp = sheet.GetRange(COleVariant("C1"), COleVariant("C1"));
      ASSERT(lpDisp);
      range.AttachDispatch(lpDisp);
      range.SetNumberFormat(COleVariant("mm/dd/yy"));
      range.SetFormula(COleVariant("=COUPNCD(A1, A2, A3, A4)"));

      /* This is an alternative that works without placing variables on
      // the worksheet.
      // The values are arguments contained in the SetFormula() call.
      // range.SetFormula(COleVariant(
                       "=COUPNCD(\"09/15/96\",\"11/15/99\",2,1)"));
      */ 

      // *** The example in this block uses a built-in Microsoft Excel
      // function.

      // You do not have to register any add-in to use the built-in
      // Microsoft Excel worksheet functions.
      lpDisp = sheet.GetRange(COleVariant("C3"), COleVariant("C3"));
      ASSERT(lpDisp);
      range.AttachDispatch(lpDisp);
      range.SetFormula(COleVariant("=SUM(A3, A4)"));
      // or use:
      // range.SetFormula(COleVariant("=SUM(2,1)"));

      // *** End of example for built-in function usage.

      // Release dispatch pointers.
      range.ReleaseDispatch();
      sheet.ReleaseDispatch();
      // This is not really necessary because
      // the default second parameter of AttachDispatch releases
      // when the current scope is lost.

      } // End of processing.

        catch(COleException *e)
      {
        char buf[1024];     // For the Try...Catch error message.
        sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);
        ::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
      }

      catch(COleDispatchException *e)
      {
        char buf[1024];     // For the Try...Catch error message.
        sprintf(buf,
               "COleDispatchException. SCODE: %08lx, Description: \"%s\".",
               (long)e->m_wCode,(LPSTR)e->m_strDescription.GetBuffer(512));
        ::MessageBox(NULL, buf, "COleDispatchException",
                           MB_SETFOREGROUND | MB_OK);
      }

      catch(...)
      {
        ::MessageBox(NULL, "General Exception caught.", "Catch-All",
                           MB_SETFOREGROUND | MB_OK);
      } 

Additional query words: MFC VC++ Automation typelib IDispatch ATP toolpack

Keywords : kbinterop kbole kbAutomation kbExcel kbMFC kbVC500 kbVC600
Version : WINDOWS:2000; winnt:5.0,6.0
Platform : WINDOWS winnt
Issue type : kbhowto