HOWTO: Automate Excel 97 Using MFC and Worksheet Functions

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);
      }