Knowledge Base

How To Use MFC to Create a Microsoft Excel Chart

Article ID: 178783

Article Last Modified on 1/22/2007

APPLIES TO

Microsoft Foundation Class Library 4.2, when used with:
- Microsoft Visual C++ 5.0 Standard Edition
- Microsoft Visual C++ 6.0 Service Pack 5
- Microsoft Excel 97 Standard Edition
Microsoft Office XP Developer Edition
Microsoft Office 2000 Developer Edition
Microsoft Excel 2002 Standard Edition

This article was previously published under Q178783

SUMMARY

This article discusses how to use version 4.2 of the Microsoft Foundation Class (MFC) library installed with Microsoft Visual C++ versions 5.0 and 6.0 to automate Microsoft Excel so that it will populate a worksheet with data and create charts.

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 Excel type library. The primary benefit comes from reading and understanding the code so that you can modify the example, or write code from scratch to automate Microsoft Excel 97, Excel 2000, or Excel 2002.

Notes for Automating Microsoft Excel 2000 and later:

Some methods and properties have changed for Microsoft Excel 2000 and later. For additional information about using the sample code described in this article with the Microsoft Excel 2000 and later type library, please see the following article in the Microsoft Knowledge Base:

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

Steps to Create the Project

In Microsoft Excel, create a new workbook named Test.xls and save it in the root directory of drive C.
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 Excel type library:

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

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

      #include "excel8.h" // excel9.h for Excel 2000, excel.h for Excel 2002

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

Sample Code

      try
      {
       _Application app;  // app is the Excel _Application object.
       _Workbook book;
       _Worksheet sheet;
       _Chart chart;

       Workbooks books;
       Worksheets sheets;
       Range range;
       ChartObjects chartobjects;
       Charts charts;
       LPDISPATCH lpDisp;

       // Common OLE variants. These are easy variants to use for
       // calling arguments.
       COleVariant
                  covTrue((short)TRUE),
                  covFalse((short)FALSE),
                  covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

       // Start Excel and get the Application object.
       if(!app.CreateDispatch("Excel.Application"))
       {
        AfxMessageBox(
             "Couldn't start Excel and get an application 0bject");
        return;
       }

       // Set visible.
       app.SetVisible(TRUE);

       // Get Workbooks collection.
       lpDisp = app.GetWorkbooks();  // Get an IDispatch pointer.
       ASSERT(lpDisp);
       books.AttachDispatch( lpDisp );  // Attach the IDispatch pointer
                                        // to the books object.
          // Open a workbook.
          lpDisp = books.Open("C:\\Test",
                      covOptional, covOptional, covOptional, covOptional,
                      covOptional, covOptional, covOptional, covOptional,
                      covOptional, covOptional, covOptional, covOptional,
                      covOptional, covOptional, covOptional); // Excel 2000 requires only 13 arguments
          ASSERT(lpDisp);  // It should have worked.

       // Attach to a Workbook object.
       book.AttachDispatch( lpDisp );  // Attach the IDispatch pointer
                                       // to the Workbook object.

       // Get sheets.
       lpDisp = book.GetSheets();
       ASSERT(lpDisp);
       sheets.AttachDispatch(lpDisp);

       lpDisp = sheets.GetItem( COleVariant((short)(1)) );
       ASSERT(lpDisp);

       // Attach the lpDisp pointer to a Worksheet object.
       sheet.AttachDispatch(lpDisp);

       lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("W40"));
                               // The range is from A1 to W40.
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer
                                      // to the range object.
       range.Clear();  // Could be ClearContents().

       ::Sleep(500); // So you can see it happen.

       lpDisp = sheet.GetRange(COleVariant("A3"), COleVariant("A3"));
                                                 // From A3 to A3.
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer
                                      // the range object.
       range.SetValue(COleVariant("March")); // Excel 97 & Excel 2000.

range.SetValue2(COleVariant("March")); // Insert March into range.


       // Following is a series of repetitive steps to populate the
       // worksheet's cells with a series of Months and values to be
       // used in the Chart object, which is yet to be constructed.

       lpDisp = sheet.GetRange(COleVariant("B3"), COleVariant("B3"));

       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("12")); // 97 & 2000

range.SetValue2(COleVariant("12")); //  Value for March.


       lpDisp = sheet.GetRange(COleVariant("A4"), COleVariant("A4"));
            // Months will be in column A, values in column B.
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("April"));// Excel 97 & Excel 2000

range.SetValue2(COleVariant("April")); // Excel 2002


       lpDisp = sheet.GetRange(COleVariant("B4"), COleVariant("B4"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("8")); // Excel 97 & Excel 2000

range.SetValue2(COleVariant("8")); // Excel 2002

       lpDisp = sheet.GetRange(COleVariant("A5"), COleVariant("A5"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("May"));

range.SetValue2(COleVariant("May"));

       lpDisp = sheet.GetRange(COleVariant("B5"), COleVariant("B5"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("2"));

range.SetValue2(COleVariant("2"));

       lpDisp = sheet.GetRange(COleVariant("A6"), COleVariant("A6"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("June"));

range.SetValue2(COleVariant("June"));

       lpDisp = sheet.GetRange(COleVariant("B6"), COleVariant("B6"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("11"));

range.SetValue2(COleVariant("11"));

       lpDisp = sheet.GetRange(COleVariant("A7"), COleVariant("A7"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("July"));

range.SetValue2(COleVariant("July"));

       lpDisp = sheet.GetRange(COleVariant("B7"), COleVariant("B7"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("16"));

range.SetValue2(COleVariant("16"));

       // The cells are populated. To start the chart,
       // declare some long variables and site the chart.
       long left, top, width, height;
       left = 100;
       top = 10;
       width = 350;
       height = 250;

       lpDisp = sheet.ChartObjects(covOptional);

       ASSERT(lpDisp);
       chartobjects.AttachDispatch(lpDisp); // Attach the lpDisp pointer
                                  // for ChartObjects to the chartobjects
                                  // object.
       ChartObject chartobject = chartobjects.Add(left, top, width, height); 
                                //defines the rectangle, 
                                // adds a new chart at that rectangle and 
                                // assigns its object reference to a 
                                // ChartObject variable named chartobject
       chart.AttachDispatch(chartobject.GetChart()); // GetChart() returns
                                         // LPDISPATCH, and this attaches 
                                         // it to your chart object.

       lpDisp = sheet.GetRange(COleVariant("A3"), COleVariant("B7"));
                         // The range containing the data to be charted.
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);

       VARIANT var; // ChartWizard needs a Variant for the Source range.
       var.vt = VT_DISPATCH; // .vt is the usable member of the tagVARIANT
                             // Struct. Its value is a union of options.
       var.pdispVal = lpDisp; // Assign IDispatch pointer
                              // of the Source range to var.

       chart.ChartWizard(var,                    // Source.
                        COleVariant((short)11),  // Gallery: 3d Column.
                        covOptional,             // Format, use default.
                        COleVariant((short)1),   // PlotBy: xlRows.
                        COleVariant((short)0),   // CategoryLabels.
                        COleVariant((short)1),   // SeriesLabels.
                        COleVariant((short)TRUE),  // HasLegend.
                        COleVariant("Use by Month"),  // Title.
                        COleVariant("Month"),    // CategoryTitle.
                        COleVariant("Usage in Thousands"),  // ValueTitles.
                        covOptional              // ExtraTitle.
                        );
       // The return is void.
       ::Sleep(3000);
       chartobject.Delete();  // Removes the first chartobject, sets the
       // ChartObjects.Item() count to 0. The next chart will restore the
       // item count to 1.
       ::Sleep(3000);  // Set the selected range to be erased.
       range.Clear();  // Erase the usage data.

       // Beginning of chart 2.
       lpDisp = sheet.GetRange(COleVariant("B3"), COleVariant("B3"));
                                      // From B3 to B3.
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer
                                      // to the range object.
       range.SetValue(COleVariant("Chocolate")); // Insert Chocolate into
                                                 // the range object.
range.SetValue2(COleVariant("Chocolate")); // Insert Chocolate

       // Following is a series of repetitive steps to populate the
       // worksheet's cells with a series of Flavors and values to be
       // used in the chart object, your second chart.

       lpDisp = sheet.GetRange(COleVariant("B4"), COleVariant("B4"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("12")); // Value for Chocolate.

range.SetValue2(COleVariant("12")); // Value for Chocolate.

       lpDisp = sheet.GetRange(COleVariant("C3"), COleVariant("C3"));
            // Flavors will be in row 3, values in row 4.
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("Vanilla"));

range.SetValue2(COleVariant("Vanilla"));

       lpDisp = sheet.GetRange(COleVariant("C4"), COleVariant("C4"));

       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("8"));

range.SetValue2(COleVariant("8"));

       lpDisp = sheet.GetRange(COleVariant("D3"), COleVariant("D3"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("Orange"));

range.SetValue2(COleVariant("Orange"));

       lpDisp = sheet.GetRange(COleVariant("D4"), COleVariant("D4"));
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);
       range.SetValue(COleVariant("6"));

range.SetValue2(COleVariant("6"));

       // The cells are populated. To start the chart,
       // define the bounds, and site the chart.

       left = 250;
       top = 40;
       width = 300;

       height = 300;

       lpDisp = sheet.ChartObjects(covOptional);

       ASSERT(lpDisp);
       chartobjects.AttachDispatch(lpDisp); // Attach the lpDisp pointer
                                   // for ChartObjects to the chartobjects
                                   // object.
       chartobjects.Add(left, top, width, height); // Adds 1 to item count.

       //**************************************
       lpDisp = chartobjects.Item( COleVariant((short)(1)) );  // It was
           // zero, but just added one at a new location,
           // with new left, top, width, and height.
       ASSERT(lpDisp);
       chartobject.AttachDispatch(lpDisp); // Use definition of new chart
                                           // site.
       chart.AttachDispatch(chartobject.GetChart());
       //**************************************

       lpDisp = sheet.GetRange(COleVariant("B3"), COleVariant("D4"));
                         // Range containing the data to be charted.
       ASSERT(lpDisp);
       range.AttachDispatch(lpDisp);

       var.pdispVal = lpDisp; // Contains IDispatch pointer
                              // to the Source range.

       chart.ChartWizard(var,                    // Source.
                        COleVariant((short)11),  // Gallery = 3D Column.
                        covOptional,             // Format, use default.
                        COleVariant((short)2),   // PlotBy xlColumns.
                        COleVariant((short)0),   // CategoryLabels.
                        COleVariant((short)1),   // SeriesLabels.
                        COleVariant((short)TRUE),  // HasLegend.
                        COleVariant("Use by Flavor"),  // Title.
                        COleVariant("Flavor"),    // CategoryTitle.
                        COleVariant("Usage in Barrells"),  // ValueTitles.
                        covOptional              // ExtraTitle.
                        );
       // The return is void.
       ::Sleep(3000);

       //Show the chart in Print Preview.
       chart.PrintOut(COleVariant((short)1),     // From (page #).
                      COleVariant((short)1),     // To (page #).
                      COleVariant((short)1),     // Copies.
                      COleVariant((short)TRUE),  // Preview.
                      covOptional,               // ActivePrinter.
                      covFalse,                  // PrintToFile.
                      covFalse                   // Collate.
                      covOptional                // PrToFileName // 2002 only
                      );

       book.SetSaved(TRUE); // Avoids the 'Save changes?' dialog box.
       app.Quit(); // Excel departs.

       // By default, the pointer references for the objects
       // range, book, chart, chartobjects, sheet, and app
       // are automatically released when they go out of scope.
       // ReleaseDispatch()s are unnecessary.

       ::Sleep(1000);
       AfxMessageBox("Just executed App.Quit()");

      }  // End of processing logic.

      catch(COleException *e)
      {
        char buf[1024];

        sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);
        ::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
      }

      catch(COleDispatchException *e)
      {
       char buf[1024];
       sprintf(buf,
               "COleDispatchException. SCODE: %08lx, Description: \"%s\".",
               (long)e->m_wCode,
               (LPSTR)e->m_strDescription.GetBuffer(1024));
       ::MessageBox(NULL, buf, "COleDispatchException",
                    MB_SETFOREGROUND | MB_OK);
      }

      catch(...)
      {
       ::MessageBox(NULL, "General Exception caught.", "Catch-All",
                    MB_SETFOREGROUND | MB_OK);
      }

You might need to modify the code in CAutoProjectDlg::OnRun() to indicate the correct path for your workbook Test.xls. The workbook is referenced in the following line:
```
      lpDisp = books.open("C:\\Test", . . .);
                    
```

REFERENCES

For additional information about the Automation of Office applications, click the article number below to view the article in the Microsoft Knowledge Base:

222101 How To Find and Use Office Object Model Documentation

Additional query words: IDispatch graph xl8 Excel 8.0 Excel97 xl97

Keywords: kbautomation kbhowto kbinterop KB178783