How To Use MFC to Automate Excel and Navigate Worksheets

Article translations Article translations
Article ID: 178782 - View products that this article applies to.
This article was previously published under Q178782
Expand all | Collapse all

On This Page

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. Specifically, it shows how to navigate between the worksheets in a workbook and place data in the cells of each worksheet.

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

  1. In Microsoft Excel, create a new workbook named Test.xls. Save it to C:\Test.xls.
  2. 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:
    178749 How To Create an Automation Project Using MFC and a Type Library
  3. At the top of the AutoProjectDlg.cpp, add the following line:
          #include "excel8.h"
    						
    For Excel 2000, change the file name to excel9.h. For Excel 2002, change the file name to excel.h.
  4. Add the following code to CAutoProjectDlg::OnRun() in the AutoProjectDLG.cpp file.

    Sample Code

          // This example walks through three worksheets and places
    
          // literal strings in cells A1 and B2 on each sheet.
    
          try
           {
           _Application app;  // app is an _Application object.
           _Workbook book;
           _Worksheet sheet;
           Workbooks books;
           Worksheets sheets;
           Range range;
           char buf[1024];
           LPDISPATCH lpDisp; // IDispatch *; pointer reused many times.
           long count; // Count of the number of worksheets.
           int i;
    
           // 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 Application object.
           if(!app.CreateDispatch("Excel.Application"))
           {
            AfxMessageBox("Couldn't CreateDispatch on Excel");
            return;
           }
    
           // Set visible.
           app.SetVisible(TRUE);
    
           // Get Workbooks collection.
           lpDisp = app.GetWorkbooks();  // Get an IDispatch pointer
    
           ASSERT(lpDisp);               // or fail.
           books.AttachDispatch( lpDisp );  // Attach the IDispatch pointer
                                            // to the books object.
    
              // Open a workbook.
              lpDisp = books.Open("C:\\Test.xls",
                             covOptional, covOptional, covOptional, covOptional,
                             covOptional, covOptional, covOptional, covOptional,
                             covOptional, covOptional, covOptional, covOptional,
                             covOptional, covOptional, covOptional); // Excel 2000 has 13 parameters
              ASSERT(lpDisp);  // It worked!
    
    
    
           // Attach to a Workbook object.
           book.AttachDispatch( lpDisp );  // Attach the IDispatch pointer
                                           // to the book object.
    
           // Get sheets.
           lpDisp = book.GetSheets();
           ASSERT(lpDisp);
           sheets.AttachDispatch(lpDisp);
    
           // Get the number of worksheets in this book.
           count = sheets.GetCount();
           sprintf(buf, "%ld worksheets in this Workbook.", count);
           ::MessageBox(NULL, buf, "Sheet Count", MB_OK | MB_SETFOREGROUND);
    
           // Enumerate through worksheets in book and activate in
           // succession.
           for(i=0; i<count; i++)
           {
            // Get the sheet. Note that 1 is added to the index to make sure
            // it is 1-based, not zero-based. Otherwise, you will get odd
            // exceptions.
            lpDisp = sheets.GetItem( COleVariant((short)(i+1)) ); // 'Item' in
                                  // the Worksheets collection = worksheet #.
            ASSERT(lpDisp);
            sheet.AttachDispatch(lpDisp);
            // Activate and sleep for two seconds so you can see it happen.
            sheet.Activate();
            ::Sleep(2000);
    
            lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("W40"));
                                                       // From cell# To cell#.
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer
                                           // to the range.
            range.Clear();  // Could be ClearContents().
            ::Sleep(500);
            lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
                                                           // From and To.
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);// Attach the IDispatch pointer
                                         // to the range.
            range.SetValue(COleVariant("A1A1")); // Excel 97 and Excel 2000.
    
    range.SetValue2(COleVariant("A1A1")); // Excel 2002. A1A1 is data string
    
    
            lpDisp = sheet.GetRange(COleVariant("B2"), COleVariant("B2"));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("B2B2")); // B2B2 is data also.
    range.SetValue2(COleVariant("B2B2")); // Excel 2002
            ::Sleep(2000);  // Could be Sleep(1000) for a one second pause.
            // Release dispatch pointer.
            sheet.ReleaseDispatch();
            } // End of For loop. You walked through all three sheets of the
              // workbook, and stuffed data into cells A1 and B2.
           AfxMessageBox("Waiting...");
    
           // Set the workbook back to sheet 1 so that it starts there next
           // time it is opened.
           lpDisp = sheets.GetItem( COleVariant((short)(1)) );
    
           ASSERT(lpDisp);
           sheet.AttachDispatch(lpDisp);
           sheet.Activate();
    
           lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("W40"));
                                                      // From and To.
           ASSERT(lpDisp);
           range.AttachDispatch(lpDisp);// Attach the IDispatch pointer
                                        // to the range object.
           range.Clear();  // Could be ClearContents().
    
           lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
           ASSERT(lpDisp);
           range.AttachDispatch(lpDisp);
           range.SetValue(COleVariant("GoodBye"));// Excel 97 and Excel 2000
    range.SetValue2(COleVariant("GoodBye")); // Excel 2002
    
           ::Sleep(3000);
    
           range.Clear();  // Could be ClearContents().
           book.SetSaved(TRUE); // Forestall the 'Save ?' dialog box.
           app.Quit();  //Excel departs.
    
           }  // 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);
          }
    					
  5. You may 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.xls", . . .);
    					

REFERENCES

For additional information about the Automation of Office applications, see Microsoft Knowledge Base article, click the article number below to view the article in the Microsoft Knowledge Base:
222101 How To Find and Use Office Object Model Documentation

Properties

Article ID: 178782 - Last Review: January 22, 2007 - Revision: 4.1
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
Keywords: 
kbautomation kbhowto kbinterop KB178782

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com