How To Automate Excel to Create And Format a New Workbook Using C++ .NET and MFC

Article translations Article translations
Article ID: 308292 - View products that this article applies to.
This article was previously published under Q308292
For a Microsoft Visual Basic .NET version of this article, see 301982.
For a Microsoft Visual C# .NET version of this article, see 302084.
Expand all | Collapse all

On This Page

Note Microsoft Visual C++ .NET (2002) supports both the managed code model that is provided by the Microsoft .NET Framework and the unmanaged native Microsoft Windows code model. The information in this article applies only to unmanaged Visual C++ code.

SUMMARY

This article demonstrates how to create an Automation client for Excel by using Microsoft Foundation Classes (MFC) and Visual C++ .NET.

MORE INFORMATION

Automation is a process that allows applications that are written in languages such as MFC to programmatically control other applications. Automation to Excel allows you to perform actions such as creating a new workbook, adding data to the workbook, or creating charts. With Excel and other Microsoft Office applications, virtually all of the actions that you can perform manually through the user interface can also be performed programmatically by using Automation.

Excel exposes this programmatic functionality through an object model. The object model is a collection of classes and methods that serve as counterparts to the logical components of Excel. For example, there is an Application object, a Workbook object, and a Worksheet object, each of which contain the functionality of those pieces of Excel. To access the object model from MFC and Visual C++ .NET, you can create an MFC wrapper class from Excel's type library.

This article demonstrates how to use the wrapper classes that are generated by MFC to create and format a new workbook by using Visual C++ .NET.

Create an Automation Client for Microsoft Excel

  1. Follow the steps in the "Create an Automation Client" section of the following Microsoft Knowledge Base article to create a basic Automation client:
    307473 How To Use a Type Library for Office Automation from Visual C++ .NET
    In step 4d of the article, select the following interfaces:

    • _Application
    • Workbooks
    • _Workbook
    • Worksheets
    • _Worksheet
    • Range
    • Font
    • Interior
    • Borders
    • Border

    In step 6 of the article (where you add #include statements for each of the classes for which you generated wrappers), note that the file for the Font class is CFont0.h and the file for the Interior class is Cnterior.h.
  2. In the IDD_AUTOPROJECT_DIALOG dialog box, right-click Run and select Add event handler. In the Event Handler Wizard, select the BN_CLICKED message type and then click Add and Edit. Add the following code in the handler for automating Excel:
    void CAutoProjectDlg::OnBnClickedRun()
    {
       // Commonly used OLE variants.
       COleVariant
          covTrue((short)TRUE),
          covFalse((short)FALSE),
          covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    
       CApplication app;
       CWorkbooks books;
       CWorkbook book;
       CWorksheets sheets;
       CWorksheet sheet;
       CRange range;
       CFont0 font;
       CRange cols;
       CRange resizedrange;
       Cnterior interior;
       CBorders borders;
       CBorder bottomborder;
    
       // Start Excel and get an Application object.
       if(!app.CreateDispatch("Excel.Application"))
       {
          AfxMessageBox("Couldn't start Excel and get Application object.");
          return;
       }
    
       //Get a new workbook.
       books = app.get_Workbooks();
       book = books.Add (covOptional);
    
       //Get the first sheet.
       sheets =book.get_Sheets();
       sheet = sheets.get_Item(COleVariant((short)1));
    
       //Fill cells A1, B1, C1, and D1 one cell at a time with "headers".
       range = sheet.get_Range(COleVariant("A1"),COleVariant("A1"));
       range.put_Value2(COleVariant("First Name"));
       range = sheet.get_Range(COleVariant("B1"),COleVariant("B1"));
       range.put_Value2(COleVariant("Last Name"));
       range = sheet.get_Range(COleVariant("C1"),COleVariant("C1"));
       range.put_Value2(COleVariant("Full Name"));
       range = sheet.get_Range(COleVariant("D1"),COleVariant("D1"));
       range.put_Value2(COleVariant("Salary"));
    
       //Format A1:D1 as bold, vertical alignment = center.
       range = sheet.get_Range(COleVariant("A1"), COleVariant("D1"));
       font = range.get_Font();
       font.put_Bold(covTrue);
       range.put_VerticalAlignment(
          COleVariant((short)-4108));   //xlVAlignCenter = -4108
    
       //Fill A2:B6 with an array of values (First & Last Names).
       {
          COleSafeArray saRet;
          DWORD numElements[]={5,2};   //5x2 element array
          saRet.Create(VT_BSTR, 2, numElements);
    
    
          //Fill the 5x2 SafeArray with the following data:
          //   John      Smith
          //   Tom       Brown
          //   Sue       Thomas
          //   Jane      Jones
          //   Adam      Johnson
    
          FillSafeArray(L"John", 0, 0, &saRet);
          FillSafeArray(L"Smith", 0, 1, &saRet);
          FillSafeArray(L"Tom", 1, 0, &saRet);
          FillSafeArray(L"Brown", 1, 1, &saRet);
          FillSafeArray(L"Sue", 2, 0, &saRet);
          FillSafeArray(L"Thomas", 2, 1, &saRet);
    
          FillSafeArray(L"Jane", 3, 0, &saRet);
          FillSafeArray(L"Jones", 3, 1, &saRet);
          FillSafeArray(L"Adam", 4, 0, &saRet);
          FillSafeArray(L"Johnson", 4, 1, &saRet);
    
          range = sheet.get_Range(COleVariant("A2"), COleVariant("B6"));
          range.put_Value2(COleVariant(saRet));
    
          saRet.Detach();
       }
    
       //Fill C2:C6 with a relative formula (=A2 & " " & B2).
       range = sheet.get_Range(COleVariant("C2"), COleVariant("C6"));
       range.put_Formula(COleVariant("=A2 & \" \" & B2"));
    
       //Fill D2:D6 with a formula(=RAND()*100000) and apply a number
       //format.
       range = sheet.get_Range(COleVariant("D2"), COleVariant("D6"));
       range.put_Formula(COleVariant("=RAND()*100000"));
       range.put_NumberFormat(COleVariant("$0.00"));
    
       //AutoFit columns A:D.
       range = sheet.get_Range(COleVariant("A1"), COleVariant("D1"));
       cols = range.get_EntireColumn();
       cols.AutoFit();
    
       //Manipulate a variable number of columns for Quarterly Sales Data.
       {
          short NumQtrs;
          CString msg;
    
          //Determine how many quarters to display data for.
          for(NumQtrs=1;NumQtrs<=3;NumQtrs++)
          {
             msg.Format("Enter sales data for %d quarter(s)?", NumQtrs);
             if(AfxMessageBox(msg,MB_YESNO)==IDYES)
             {
                break;
             }
          }
          msg.Format("Displaying data for %d quarters.", NumQtrs);
          AfxMessageBox(msg);
    
          //Starting at E1, fill headers for the number of columns selected.
          range = sheet.get_Range(COleVariant("E1"), COleVariant("E1"));
          resizedrange = range.get_Resize(covOptional, COleVariant(NumQtrs));
          resizedrange.put_Formula(
             COleVariant("=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\""));
          //Change the Orientation and WrapText properties for the headers.
          resizedrange.put_Orientation(COleVariant((short)38));
          resizedrange.put_WrapText(covTrue);
          //Fill the interior color of the headers.
          interior = resizedrange.get_Interior();
          interior.put_ColorIndex(COleVariant((short)36));
    
          //Fill the columns with a formula and apply a number format.
          range = sheet.get_Range(COleVariant("E2"), COleVariant("E6"));
          resizedrange = range.get_Resize(covOptional, COleVariant(NumQtrs));
          resizedrange.put_Formula(COleVariant("=RAND()*100"));
          resizedrange.put_NumberFormat(COleVariant("$0.00"));
    
          //Apply borders to the Sales data and headers.
          range = sheet.get_Range(COleVariant("E1"), COleVariant("E6"));
          resizedrange= range.get_Resize(covOptional, COleVariant(NumQtrs));
          borders = resizedrange.get_Borders();
          borders.put_Weight(COleVariant((short)2));   //xlThin = 2
    
          //Add a Totals formula for the Quarterly sales data and apply a
          //border.
          range = sheet.get_Range(COleVariant("E8"), COleVariant("E8"));
          resizedrange = range.get_Resize(covOptional, COleVariant(NumQtrs));
          resizedrange.put_Formula(COleVariant("=SUM(E2:E6)"));
          borders = resizedrange.get_Borders();
          {
             bottomborder = borders.get_Item((long)9);
             bottomborder.put_LineStyle(
                COleVariant((short)-4119));   //xlDouble = -4119
             bottomborder.put_Weight(
                COleVariant((short)4));       //xlThick = 4
          }
       }
    
       //Make the application visible and give the user control of
       //Excel.
       app.put_Visible(TRUE);
       app.put_UserControl(TRUE);
    }
    					
  3. Add the following code immediately above the OnBnClickedRun method. This code simplifies creating an array that is used to send data to Excel:
    void FillSafeArray(OLECHAR FAR* sz, int iRow, int iCol,
                       COleSafeArray* sa)
    {
    
       VARIANT v;
       long index[2];
    
       index[0] = iRow;
       index[1] = iCol;
    
       VariantInit(&v);
       v.vt = VT_BSTR;
       v.bstrVal = SysAllocString(sz);
       sa->PutElement(index, v.bstrVal);
       SysFreeString(v.bstrVal);
       VariantClear(&v);
    
    }
    					
  4. Press F5 to build and run the Automation client.
  5. When the dialog box appears, click Run.

    The Automation client starts Excel and creates a sample workbook. Excel remains running even when the Automation client ends because the user has been given control of the application.

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

REFERENCES

For additional information on automating Excel by using MFC and Visual C++ .NET, click the article number below to view the article in the Microsoft Knowledge Base:
308407 How To Automate Excel From C++ .NET and MFC To Fill or Obtain Data In a Range Using Arrays

Properties

Article ID: 308292 - Last Review: June 29, 2004 - Revision: 3.3
APPLIES TO
  • Microsoft Visual C++ .NET 2003 Standard Edition
  • Microsoft Visual C++ .NET 2002 Standard Edition
  • Microsoft Foundation Class Library 4.2
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbautomation kbhowto KB308292

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com