How to embed and automate a Microsoft Excel worksheet with MFC

LPDISPATCH GetIDispatch();
					

   Sample Code
   -----------


      /*******************************************************************
      *   This method returns the IDispatch* for the application linked to
      *   this container.
      ********************************************************************/ 
      LPDISPATCH CEmbed_ExcelCntrItem::GetIDispatch()
      {
         //The this and m_lpObject pointers must be valid for this function
         //to work correctly. The m_lpObject is the IUnknown pointer to
         // this object.
         ASSERT_VALID(this);

         ASSERT(m_lpObject != NULL);

         LPUNKNOWN lpUnk = m_lpObject;

         //The embedded application must be running in order for the rest
         //of the function to work.
         Run();

         //QI for the IOleLink interface of m_lpObject.
         LPOLELINK lpOleLink = NULL;
         if (m_lpObject->QueryInterface(IID_IOleLink,
            (LPVOID FAR*)&lpOleLink) == NOERROR)
         {
            ASSERT(lpOleLink != NULL);
            lpUnk = NULL;

            //Retrieve the IUnknown interface to the linked application.
            if (lpOleLink->GetBoundSource(&lpUnk) != NOERROR)
            {
               TRACE0("Warning: Link is not connected!\n");
               lpOleLink->Release();
               return NULL;
            }
            ASSERT(lpUnk != NULL);
         }

         //QI for the IDispatch interface of the linked application.
         LPDISPATCH lpDispatch = NULL;
         if (lpUnk->QueryInterface(IID_IDispatch, (LPVOID FAR*)&lpDispatch)
            !=NOERROR)
         {
            TRACE0("Warning: does not support IDispatch!\n");
            return NULL;
         }

         //After assuring ourselves it is valid, return the IDispatch
         //interface to the caller.
         ASSERT(lpDispatch != NULL);
         return lpDispatch;
      }
					

      void EmbedAutomateExcel();
					

Sample Code
-----------
      /********************************************************************
      *   This method encapsulates the process of embedding an Excel
      *   Worksheet in a View object and automating that worksheet to add
      *   some text to cell A1.
      ********************************************************************/ 
      void CEmbed_ExcelView::EmbedAutomateExcel()
      {
         //Change the cursor so the user knows something exciting is going
         //on.
         BeginWaitCursor();

         CEmbed_ExcelCntrItem* pItem = NULL;
         TRY
         {
            //Get the document associated with this view, and be sure it's
            //valid.
            CEmbed_ExcelDoc* pDoc = GetDocument();
            ASSERT_VALID(pDoc);

            //Create a new item associated with this document, and be sure
            //it's valid.
            pItem = new CEmbed_ExcelCntrItem(pDoc);
            ASSERT_VALID(pItem);

            // Get Class ID for Excel sheet.
            // This is used in creation.
            CLSID clsid;
            if(FAILED(::CLSIDFromProgID(L"Excel.sheet",&clsid)))
               //Any exception will do. We just need to break out of the
               //TRY statement.
               AfxThrowMemoryException();

            // Create the Excel embedded item.
            if(!pItem->CreateNewItem(clsid))
               //Any exception will do. We just need to break out of the
               //TRY statement.
               AfxThrowMemoryException();

            //Make sure the new CContainerItem is valid.
            ASSERT_VALID(pItem);

            // Launch the server to edit the item.
            pItem->DoVerb(OLEIVERB_SHOW, this);


            // As an arbitrary user interface design, this sets the
            // selection to the last item inserted.
            m_pSelection = pItem;   // set selection to last inserted item
            pDoc->UpdateAllViews(NULL);

            //Query for the dispatch pointer for the embedded object. In
            //this case, this is the Excel worksheet.
            LPDISPATCH lpDisp;
            lpDisp = pItem->GetIDispatch();

            //Add text in cell A1 of the embedded Excel sheet
            _Workbook wb;
            Worksheets wsSet;
            _Worksheet ws;
            Range range;
            _Application app;

            //set _Workbook wb to use lpDisp, the IDispatch* of the
            //actual workbook.
            wb.AttachDispatch(lpDisp);


            //Then get the worksheet's application.
            app = wb.GetApplication();

            //Then get the first worksheet in the workbook
            wsSet = wb.GetWorksheets();
            ws = wsSet.GetItem(COleVariant((short)1));

            //From there, get a Range object corresponding to cell A1.
            range = ws.GetRange(COleVariant("A1"), COleVariant("A1"));

            //Fill A1 with the string "Hello, World!"
            range.SetValue(COleVariant("Hello, World!"));

           //NOTE: If you are automating Excel 2002, the Range.SetValue method has an 
           //additional optional parameter specifying the data type.  Because the 
           //parameter is optional, existing code will still work correctly, but new 
           //code should use the new convention.  The call for Excel2002 should look 
           //like the following:

           //range.SetValue( C<?xm-insertion_mark_start author="v-thomr" time="20070326T121607-0600"?>O<?xm-insertion_mark_end?><?xm-deletion_mark author="v-thomr" time="20070326T121606-0600" data="o"?>leVariant( (long)DISP_E_PARAMNOTFOUND, VT_ERROR ), 
           //                COleVariant("Hello, World!"));
         }

           //Here, we need to do clean up if something went wrong.
           CATCH(CException, e)
           {
              if (pItem != NULL)
              {
                 ASSERT_VALID(pItem);
                 pItem->Delete();

              }
              AfxMessageBox(IDP_FAILED_TO_CREATE);
           }
           END_CATCH

           //Set the cursor back to normal so the user knows exciting stuff
           //is no longer happening.
           EndWaitCursor();
        }

					

      #include "excel8.h"