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

ID: Q186120

The information in this article applies to:

Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0

SUMMARY

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

MORE INFORMATION

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
                  Library

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;

      if(!UpdateData(TRUE))
      {
         return;
      }

      // Instantiate Excel and start a new workbook.
      objApp.CreateDispatch("Excel.Application");
      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),
                                COleVariant(m_sStartingCell));
      range = range.GetResize(COleVariant(m_iNumRows),
                              COleVariant(m_iNumCols));

      //*** 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.

      if(m_bFillWithStrings)
      {
         saRet.Create(VT_BSTR, 2, numElements);
      }
      else
      {
         saRet.Create(VT_R8, 2, numElements);
      }

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

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

      //Set the range value to the SAFEARRAY.
      range.SetValue(COleVariant(saRet));
      saRet.Detach();

      //Return control of Excel to the user.
      objApp.SetVisible(TRUE);
      objApp.SetUserControl(TRUE);

6. Compile and Run the project.

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

      Control               Contents
      ------------------------------
      IDC_STARTINGCELL      A1
      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_STARTINGCELL      C3
      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.

REFERENCES

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
               Range

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

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

   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