HOWTO: Use MFC to Automate Excel and Fill a Range with an Array

ID: Q186120

The information in this article applies to:


This article demonstrates how to automate Microsoft Excel 97 and fill a multi-cell range with an array of values.


To fill a multi-cell range without populating the cells one-by-one, you must create a two-dimensional variant SAFEARRAY which you pass to Excel by calling the SetValue function for the Range object. The following steps illustrate this process.

Steps to Create Project

1. Follow steps 1 through 12 in the following article in the Microsoft

   Knowledge Base to create a sample project that uses the IDispatch
   interfaces and member functions defined in the Excel8.olb type library:

      ARTICLE-ID: Q178749
      TITLE     : HOWTO: Create an Automation Project Using MFC and a Type

2. To the dialog box created in steps 4 and 5 of the parent article
   Q178749, add the following controls with properties as specified. Also
   add the corresponding member variables:

                                  Member                Member
      Control   Name              Variable Type         Variable Name
      Edit      IDC_STARTINGCELL  m_sStartingCell       CString
      Edit      IDC_NUMROWS       m_iNumRows            short
      Edit      IDC_NUMCOLS       m_iNumCols            short
      CheckBox  IDC_STRING        m_bFillWithStrings    BOOL

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

      #include "excel8.h"

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

   Sample Code

      // OLE Variant for Optional.
      COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

      _Application objApp;
      _Workbook objBook;
      Workbooks objBooks;
      Worksheets objSheets;
      _Worksheet objSheet;
      Range range;


      // Instantiate Excel and start a new workbook.
      objBooks = objApp.GetWorkbooks();
      objBook = objBooks.Add(VOptional);
      objSheets = objBook.GetWorksheets();
      objSheet = objSheets.GetItem(COleVariant((short)1));

      //Get the range where the starting cell has the address
      //m_sStartingCell and it's dimensions are m_iNumRows x m_iNumCols.
      range = objSheet.GetRange(COleVariant(m_sStartingCell),
      range = range.GetResize(COleVariant(m_iNumRows),

      //*** Fill the range with an array of values.

      //Create the SAFEARRAY.
      COleSafeArray saRet;
      DWORD numElements[2];
      numElements[0]= m_iNumRows;   //Number of rows in the range.
      numElements[1]= m_iNumCols;   //Number of columns in the range.

         saRet.Create(VT_BSTR, 2, numElements);
         saRet.Create(VT_R8, 2, numElements);

      //Fill the SAFEARRAY.
      long index[2];
      long iRow;
      long iCol;

            index[0] = iRow;
            index[1] = iCol;
            if(m_bFillWithStrings)      //Fill with Strings.
               VARIANT v;
               CString s;
               v.vt = VT_BSTR;
               s.Format("r%dc%d", iRow, iCol);
               v.bstrVal = s.AllocSysString();
               saRet.PutElement(index, v.bstrVal);
            else                     //Fill with Numbers.
               double d;
               d = (iRow*1000) + iCol;
               saRet.PutElement(index, &d);

      //Set the range value to the SAFEARRAY.

      //Return control of Excel to the user.

6. Compile and Run the project.

7. Specify the following values for the controls on the dialog box:

      Control               Contents
      IDC_NUMROWS           10
      IDC_NUMCOLS           5
      IDC_STRING            True

   Click OK.

   Results: A new workbook is generated and cells A1:E10 of the first
   worksheet are populated with string values.

8. Specify the following values for the controls on the dialog box:

      Control               Contents
      IDC_NUMROWS           2
      IDC_NUMCOLS           9
      IDC_STRING            False

   Click OK.

   Results: A new workbook is generated and cells C3:K4 of the first
   worksheet are populated with numeric values.


For more information about automating Microsoft Excel using MFC, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q186122
   TITLE     : HOWTO: Use MFC to Automate Excel & Obtain an Array from a

   ARTICLE-ID: Q184663
   TITLE     : HOWTO: Embed and Automate a Microsoft Excel Worksheet with

   ARTICLE-ID: Q179706
   TITLE     : HOWTO: Use MFC to Automate Excel & Create/Format a New

   ARTICLE-ID: Q178781
   TITLE     : HOWTO: Automate Excel 97 Using MFC and Worksheet Functions

   ARTICLE-ID: Q178783
   TITLE     : HOWTO: Use MFC to Create a Microsoft Excel 97 Chart

Additional query words:
Keywords          : kbcode kbinterop kbole kbAutomation kbMFC kbVC500 kbVC600 
Version           : WINNT:5.0
Platform          : winnt
Issue type        : kbhowto

Last Reviewed: January 7, 1999