How to embed and automate an Excel worksheet by using MFC and Visual C++ 2005 or Visual C++ .NET

This step-by-step article describes how to embed an Excel worksheet in a View object in a single-document interface (SDI) Microsoft Foundation Classes (MFC) application by using Visual C++ 2005 or Visual C++ .NET.

Create an MFC Application that Embeds an Excel Worksheet

The following steps describe how to embed a worksheet and automate the worksheet to add data to a cell.

1. Start Microsoft Visual Studio .NET. On the File menu, click New, and then click Project. Under Project types click Visual C++ Projects, and then click MFC Application under Templates. Name the project Embed_Excel.
   
   Note In Visual C++ 2005, click Visual C++ instead of Visual C++ Projects.
2. When the MFC Application Wizard appears, follow these steps:
   1. Click Application Type and then select Single Document.
   2. Click Compound Document Support and then select Container.
   3. Click Finish to accept all other default settings.
3. Add interfaces from the Excel object library. To do this, follow these steps:
   1. On the Project menu, click Add Class.
   2. Select MFC Class From TypeLib from the list of templates and click Open. The Add Class From TypeLib Wizard appears.
      
      Note In Visual C++ 2005, click Add instead of Open.
   3. In the list of available type libraries, locate Microsoft Excel version Object Library. For Excel 2000, the version is 9.0, for Excel 2002, the version is 10.0, and for Microsoft Office Excel 2003, the version is 11.0.
   4. Add the following interfaces:
      • _Application
      • _Workbook
      • _Worksheet
      • Range
      • Workbooks
      • Worksheets
   5. Click Finish.
4. Add the following line to Cntritem.h as a public member function of the CEmbed_ExcelCntrItem class:

   LPDISPATCH GetIDispatch();

5. Add the GetIDispatch method to Cntritem.cpp, as follows:

   /*******************************************************************
   *   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 you verify that it is valid, return the IDispatch
   	//interface to the caller.
   	ASSERT(lpDispatch != NULL);
   	return lpDispatch;
   }
   					

   Note In Visual C++ 2005, you must add the common language runtime support compiler option (/clr:oldSyntax) to successfully compile the previous code sample. To add the common language runtime support compiler option, follow these steps:
   1. Click Project, and then click ProjectName Properties.
      
      Note ProjectName is a placeholder for the name of the project.
   2. Expand Configuration Properties, and then click General.
   3. In the right pane, click to select Common Language Runtime Support, Old Syntax (/clr:oldSyntax) in the Common Language Runtime support project settings.
   4. Click Apply, and then click OK.
   For more information about common language runtime support compiler options, visit the following Microsoft Developer Network (MSDN) Web site:
   http://msdn2.microsoft.com/en-us/library/k8d11d4s.aspx
   These steps apply to the whole article.
6. Add the following line to Embed_excelview.h as a public method of the CEmbed_ExcelView class:

   void EmbedAutomateExcel();
   					

7. Add the following lines to Embed_excelview.cpp:

   #include "CApplication.h"
   #include "CWorkbook.h"
   #include "CWorksheet.h"
   #include "CRange.h"
   #include "CWorksheets.h"
   #include "CWorkbooks.h"
   
   /********************************************************************
   *   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 that the user knows that something exciting is going
   	//on.
   	BeginWaitCursor();
   
   	CEmbed_ExcelCntrItem* pItem = NULL;
   	TRY
   	{
   		//Get the document associated with this view, and be sure that it is
   		//valid.
   		CEmbed_ExcelDoc* pDoc = GetDocument();
   		ASSERT_VALID(pDoc);
   
   		//Create a new item associated with this document, and be sure that 
   		//it is valid.
   		pItem = new CEmbed_ExcelCntrItem(pDoc);
   		ASSERT_VALID(pItem);
   
   		// Get a Class ID for the Excel sheet.
   		// This is used in creation.
   		CLSID clsid;
   		if(FAILED(::CLSIDFromProgID(L"Excel.sheet",&clsid)))
   			//Any exception will do. You just need to break out of the
   			//TRY statement.
   			AfxThrowMemoryException();
   
   		// Create the Excel embedded item.
   		if(!pItem->CreateNewItem(clsid))
   			//Any exception will do. You just need to break out of the
   			//TRY statement.
   			AfxThrowMemoryException();
   
   		//Make sure the new CContainerItem is valid.
   		ASSERT_VALID(pItem);
   
   		// Start 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.
   		CWorkbook wb;
   		CWorksheets wsSet;
   		CWorksheet ws;
   		CRange CRange;
   		CApplication app;
   
   		//Set CWorkbook wb to use lpDisp, the IDispatch* of the
   		//actual workbook.
   		wb.AttachDispatch(lpDisp);
   
   		//Get the application for the worksheet.
   		app = wb.get_Application();
   
   		//Get the first worksheet in the workbook.
   		wsSet = wb.get_Worksheets();
   		ws = wsSet.get_Item(COleVariant((short)1));
   
   		//Get a CRange object that corresponds to cell A1.
   		CRange = ws.get_Range(COleVariant("A1"), COleVariant("A1"));
   
   		//Fill A1 with the string "Hello, World!"
   		CRange.put_Value2(COleVariant("Hello, World!"));
   
   		//NOTE: If you are automating Excel 2002, the CRange.SetValue method has an 
   		//additional optional parameter that specifies 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  
   		//resemble the following:
   
   		//CRange.SetValue( ColeVariant( (long)DISP_E_PARAMNOTFOUND, VT_ERROR ), 
   		//                COleVariant("Hello, World!"));
   	}
   
   	//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 that the user knows that exciting stuff
   		//is no longer happening.
   		EndWaitCursor();
   }
   					

8. Replace the code for CEmbed_ExcelView::OnInsertObject in Embed_excelview.cpp with the following:

   void CEmbed_ExcelView::OnInsertObject()
   {
   	EmbedAutomateExcel();
   }
   						

   NOTE: EmbedAutomateExcel 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. You will override this behavior because it is not needed for this demonstration.

Test the Application

1. Press F5 to build and run the application.
2. On the Edit menu of the application, click Insert New Object.
3. Examine the results. A new Excel worksheet is embedded into the View object, with the text "Hello, World!" in cell A1.

Troubleshooting

• If you add class wrappers for the Excel object library by using the File option in the Add Class From TypeLib Wizard, you may receive an error message when you browse to the object library. To avoid this problem, type the full path and file name for the object library instead of browsing to the file.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
  311408 BUG: 'Read-Only' Warning When Adding MFC Class From Library
• If you receive the following error message when you build your sample application, change "Variant DialogBox" in CRange.h to "Variant _DialogBox":
  warning C4003: not enough actual parameters for macro 'DialogBoxA'
  For more information on the cause of this error, see the following Knowledge Base article:
  311407 BUG: MFC Wizard Doesn't Resolve Naming Conflicts with API Macros

For more information, see the following Microsoft Developer Network (MSDN) Web site: For additional information about Excel Automation, see the following Knowledge Base articles: