HOWTO: Use MFC to Automate Excel 97 and Navigate Worksheets

Last reviewed: February 6, 1998
Article ID: Q178782
The information in this article applies to:
  • The Microsoft Foundation Classes (MFC) included with: - Microsoft Visual C++, 32-bit Editions, version 5.0
  • Microsoft Excel 97 for Windows

SUMMARY

This article discusses how to use version 4.2 of the Microsoft Foundation Class (MFC) library installed with Microsoft Visual C++ version 5.0 to automate Microsoft Excel 97. 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 Excel8.olb 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.

Steps to Create the Project

  1. In Microsoft Excel, create a new workbook named 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:

          ARTICLE-ID: Q178749
    
          TITLE     : HOWTO: 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"
    

  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:\\My Docs\\Test",
                          covOptional, covOptional, covOptional, covOptional,
                          covOptional, covOptional, covOptional, covOptional,
                          covOptional, covOptional, covOptional, covOptional
                          );
           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, NULL, MB_OK | MB_SETFOREGROUND);
    
           // Enumerate through worksheets in book and activate in
           // succession.
           for(i=0; i<count; i++)
           {
            // Get i'th 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")); // A1A1 is just data.
    
            lpDisp = sheet.GetRange(COleVariant("B2"), COleVariant("B2"));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("B2B2")); // B2B2 is data also.
            ::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"));
           ::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:\\My Docs\\Test", . . .);
    


Additional query words: IDispatch MFC automate excel8 xl8 excel 8.0 xl97
Keywords : MfcOLE kbcode kbinterop
Technology : kbOle
Version : Win95:5.0; WINNT:5.0
Platform : Win95 winnt
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 6, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.