HOWTO: Use MFC to Automate Excel & Obtain an Array from a Range

ID: Q186122

The information in this article applies to:

Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0
Microsoft Excel 97 for Windows

SUMMARY

This article demonstrates how to automate Microsoft Excel 97 and return the values from a multi-cell range to an array.

MORE INFORMATION

To return the values from a multi-cell range without querying the cells one- by-one, you must use the GetValue member function of the Range object to return the values to a VARIANT. You then construct a SAFEARRAY based on the VARIANT you returned from GetValue. Once you have the SAFEARRAY, you can access the elements using GetElement. The following steps illustrate this process and demonstrate how you can access both string and numeric elements.

Steps to Create Project

1. In Microsoft Excel, create a new workbook. Populate cells A1:C8 with a

   mixture of string and numeric data. Save the workbook as "c:\test.xls"
   and exit Microsoft Excel.

2. 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

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

      #include "excel8.h"

4. 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 objRange;
      VARIANT ret;

      // Instantiate Excel and open an existing workbook.
      objApp.CreateDispatch("Excel.Application");
      objBooks = objApp.GetWorkbooks();
      objBook = objBooks.Open("C:\\Test.xls",
                VOptional, VOptional, VOptional, VOptional,
                VOptional, VOptional, VOptional, VOptional,
                VOptional, VOptional, VOptional, VOptional);
      objSheets = objBook.GetWorksheets();
      objSheet = objSheets.GetItem(COleVariant((short)1));

      //Get the range object for which you wish to retrieve the
      //data and then retrieve the data (as a variant array, ret).
      objRange = objSheet.GetRange(COleVariant("A1"), COleVariant("C8"));
      ret = objRange.GetValue();

      //Create the SAFEARRAY from the VARIANT ret.
      COleSafeArray sa(ret);

      //Determine the array's dimensions.
      long lNumRows;
      long lNumCols;
      sa.GetUBound(1, &lNumRows);
      sa.GetUBound(2, &lNumCols);

      //Display the elements in the SAFEARRAY.
      long index[2];
      VARIANT val;
      int r, c;
      TRACE("Contents of SafeArray\n");
      TRACE("=====================\n\t");
      for(c=1;c<=lNumCols;c++)
      {
         TRACE("\t\tCol %d", c);
      }
      TRACE("\n");
      for(r=1;r<=lNumRows;r++)
      {
         TRACE("Row %d", r);
         for(c=1;c<=lNumCols;c++)
         {
            index[0]=r;
            index[1]=c;
            sa.GetElement(index, &val);
            switch(val.vt)
            {
            case VT_R8:
               {
                  TRACE("\t\t%1.2f", val.dblVal);
                  break;
               }
            case VT_BSTR:
               {
                  TRACE("\t\t%s",(CString)val.bstrVal);
                  break;
               }
            case VT_EMPTY:
               {
                  TRACE("\t\t<empty>");
                  break;
               }
            }
         }
         TRACE("\n");
      }

      //Close the workbook without saving changes
      //and quit Microsoft Excel.
      objBook.Close(COleVariant((short)FALSE), VOptional, VOptional);
      objApp.Quit();

5. Compile the project and run it.

6. Click Run. The values in cells A1:C8 are displayed in the Output

   window.

REFERENCES

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

   ARTICLE-ID: Q186120
   TITLE     : HOWTO: Use MFC to Automate Excel and Fill a Range with an
               Array

   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

   ARTICLE-ID: Q167668
   TITLE     : HOWTO: Pass a SafeArray of Strings in a VARIANT*

Additional query words:

Keywords          : kbcode kbinterop kbole kbAutomation kbMFC kbVC500 kbVC600 kbExcel97 
Version           : WINDOWS:97; WINNT:5.0
Platform          : WINDOWS winnt
Issue type        : kbhowto

Last Reviewed: January 8, 1999