Microsoft KB Archive/184663

= How to embed and automate a Microsoft Excel worksheet with MFC =

Article ID: 184663

Article Last Modified on 3/28/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Visual C++ 5.0 Professional Edition
 * Microsoft Visual C++ 6.0 Professional Edition
 * Microsoft Foundation Class Library 4.2
 * Microsoft Office XP Developer Edition
 * Microsoft Office 2000 Developer Edition

-



This article was previously published under Q184663



SUMMARY
This article describes how to embed a Microsoft Excel Worksheet into a View object in an SDI MFC application.

This article includes step-by-step instructions for embedding the worksheet and adding some text to cell A1, as well as comments explaining each step.

Although the sample code in this article can be taken and put into your application, the real benefit comes from reading and understanding the sample code.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To create the MFC application, follow these steps:  Use the AppWizard to create a new MFC AppWizard (EXE) project named "Embed_Excel." Select Single Document as the type of application to create, and select Container as the type of compound document support to include. Accept all other default settings.

The following classes are generated:

Application: CEmbed_ExcelApp in Embed_Excel.h and Embed_Excel.cpp

Frame: CMainFrame in MainFrm.h and MainFrm.cpp

Document: CEmbed_ExcelDoc in Embed_ExcelDoc.h and Embed_ExcelDoc.cpp

View: CEmbed_ExcelView in Embed_ExcelView.h and Embed_ExcelView.cpp

Container Item: CEmbed_ExcelCntrItem in CntrItem.h and CntrItem.cpp On the View menu, click ClassWizard. Click the Automation tab, click Add Class, and choose From a Type Library. Locate the Microsoft Excel type library, and add all the classes in the type library to your project. For Excel 97, the type library is located in Excel8.olb. For Excel 2000 the type library is located in Excel9.olb, and for Excel 2002 and later, the type library it located in Excel.exe.  Add the following line to CntrItem.h: LPDISPATCH GetIDispatch;   Then add the GetIDispatch method to CntrItem.cpp: 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; }                     Add the following line to Embed_ExcelView.h:      void EmbedAutomateExcel;   Then add the EmbedAutomateExcel method to Embed_ExcelView.cpp: 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; }

</li>  Add the following line to Embed_ExcelView.h:      #include "excel8.h" Note If you are automating Excel 2000, the header file is "excel9.h." If you are automating Excel 2002 or a later version of Excel, the header file is "excel.h." </li> Look at the OnInsertObject method of the View class. It is interesting to note that this method, and the method we've just written, are strikingly similar. In fact, the code we've written is merely a special case of OnInsertObject, which allows the user to select from a list of available OLE objects to insert into the application. Because we only want to automate the Excel Worksheet, we override this behavior. For our application, remove all the code from the interior of InsertObject and replace it with a call to EmbedAutomateExcel.</li> Compile and run the application.</li> On the Edit menu, click Insert New Object.</li></ol>

Results: A Microsoft Excel worksheet is embedded into the View. Additionally, cell A1 is populated with "Hello, World!" through automation.

Additional query words: XL2007

Keywords: kbprogramming kbautomation kbhowto kbinterop KB184663

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.