Knowledge Base

How to use MFC to automate Excel and create and format a new workbook

Article ID: 179706

Article Last Modified on 1/22/2007

APPLIES TO

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

This article was previously published under Q179706

SUMMARY

This article shows how to automate Microsoft Excel using the Microsoft Foundation Class (MFC) library, version 4.2 (installed with Microsoft Visual C++ versions 5.0 and 6.0).

The article describes a technique for using OLE automation to create/format a Microsoft Excel workbook; it introduces several methods/properties (from the Microsoft Excel type library) for adding data to the worksheet and formatting the worksheet.

Notes for Automating Microsoft Excel 2000 and 2002

The sample code in this article uses class wrappers generated from the Excel 97 object library (Excel 8.olb). With slight modification, this code can be applied to an Automation client that uses class wrappers for Excel 2000 (Excel9.olb) or Excel 2002 (Excel.olb). For additional information about using the sample code described in this article with the Microsoft Excel 2000 or 2002 type library, please click the article number below to view it in the Microsoft Knowledge Base:

224925 INFO: Type Libraries for Office May Change with New Release

MORE INFORMATION

You can copy the code in this article to the message handler function of an event defined in an MFC .cpp file. However, the purpose of the code is to illustrate the process of using the IDispatch interfaces and member functions defined in the Excel8.olb type library. The primary benefit of this article, however, comes from reading and understanding the code in the example so that you can modify the example or write your own code to automate Microsoft Excel 97 using MFC.

Steps to Create the Project

Follow steps 1 through 12 in the following Microsoft Knowledge Base article to create a sample project that uses the IDispatch interfaces and member functions defined in the Excel8.olb type library:

178749How To Create Automation Project Using MFC and a Type Library

At the top of the AutoProjectDlg.cpp file, add the following line:

      #include "excel8.h"

Add the following code to CAutoProjectDlg::OnRun() in the AutoProjectDLG.cpp file:

Sample Code

      // Commonly used OLE variants.
      COleVariant
                 covTrue((short)TRUE),
                 covFalse((short)FALSE),
                 covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);


      _Application app;
      Workbooks books;
      _Workbook book;

      Worksheets sheets;
      _Worksheet sheet;
      Range range;
      Font font;
      Range cols;

      // Start Excel and get Application object.

      if(!app.CreateDispatch("Excel.Application"))
      {
       AfxMessageBox("Couldn't start Excel and get Application object.");
       return;
      }


      //Get a new workbook.
      books = app.GetWorkbooks();
      book = books.Add (covOptional);

      //Get the first sheet.
      sheets =book.GetSheets();
      sheet = sheets.GetItem(COleVariant((short)1));

      //Fill cells A1, B1, C1, and D1 one cell at a time with "headers".
      range = sheet.GetRange(COleVariant("A1"),COleVariant("A1"));
      range.SetValue(COleVariant("First Name"));
      range = sheet.GetRange(COleVariant("B1"),COleVariant("B1"));
      range.SetValue(COleVariant("Last Name"));
      range = sheet.GetRange(COleVariant("C1"),COleVariant("C1"));
      range.SetValue(COleVariant("Full Name"));
      range = sheet.GetRange(COleVariant("D1"),COleVariant("D1"));
      range.SetValue(COleVariant("Salary"));


      //Format A1:D1 as bold, vertical alignment = center.
      range = sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
      font = range.GetFont();
      font.SetBold(covTrue);
      range.SetVerticalAlignment(
               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.GetRange(COleVariant("A2"), COleVariant("B6"));
         range.SetValue(COleVariant(saRet));

         saRet.Detach();
      }

      //Fill C2:C6 with a relative formula (=A2 & " " & B2).
      range = sheet.GetRange(COleVariant("C2"), COleVariant("C6"));
      range.SetFormula(COleVariant("=A2 & \" \" & B2"));

      //Fill D2:D6 with a formula(=RAND()*100000) and apply a number
      //format.
      range = sheet.GetRange(COleVariant("D2"), COleVariant("D6"));
      range.SetFormula(COleVariant("=RAND()*100000"));
      range.SetNumberFormat(COleVariant("$0.00"));

      //AutoFit columns A:D.
      range = sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
      cols = range.GetEntireColumn();
      cols.AutoFit();

      //Manipulate a variable number of columns for Quarterly Sales Data.
      {
         short NumQtrs;
         CString msg;
         Range resizedrange;
         Interior interior;
         Borders borders;

         //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.GetRange(COleVariant("E1"), COleVariant("E1"));
         resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
         resizedrange.SetFormula(
                COleVariant("=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\""));
         //Change the Orientation and WrapText properties for the headers.
         resizedrange.SetOrientation(COleVariant((short)38));
         resizedrange.SetWrapText(covTrue);
         //Fill the interior color of the headers.
         interior = resizedrange.GetInterior();
         interior.SetColorIndex(COleVariant((short)36));

         //Fill the columns with a formula and apply a number format.
         range = sheet.GetRange(COleVariant("E2"), COleVariant("E6"));
         resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
         resizedrange.SetFormula(COleVariant("=RAND()*100"));
         resizedrange.SetNumberFormat(COleVariant("$0.00"));

         //Apply borders to the Sales data and headers.
         range = sheet.GetRange(COleVariant("E1"), COleVariant("E6"));
         resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
         borders = resizedrange.GetBorders();
         borders.SetWeight(COleVariant((short)2));   //xlThin = 2

         //Add a Totals formula for the Quarterly sales data and apply a
         //border.
         range = sheet.GetRange(COleVariant("E8"), COleVariant("E8"));
         resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
         resizedrange.SetFormula(COleVariant("=SUM(E2:E6)"));
         borders = resizedrange.GetBorders();
         {
            Border bottomborder;
            bottomborder = borders.GetItem((long)9);
            bottomborder.SetLineStyle(
                           COleVariant((short)-4119));   //xlDouble = -4119
            bottomborder.SetWeight(
                           COleVariant((short)4));       //xlThick = 4

         }
      }

      //Make the application visible and give the user control of
      //Microsoft Excel.
      app.SetVisible(TRUE);
      app.SetUserControl(TRUE);

Add the following function to AutoProjectDLG.cpp, locating it somewhere before CAutoProjectDlg::OnRun():

Sample Code

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

      }

In Developer Studio, run the application. When the dialog box appears (the example creates a dialog-based application), click the Run button.

Additional query words: Excel 8 8.0 XL97 XL8

Keywords: kbfunctions kbprogramming kbautomation kbhowto kbinterop KB179706

Microsoft KB Archive/179706

Contents

How to use MFC to automate Excel and create and format a new workbook

SUMMARY

Notes for Automating Microsoft Excel 2000 and 2002

MORE INFORMATION

Steps to Create the Project

Sample Code

Sample Code