How To Use MFC to Automate Excel and Navigate Worksheets

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", . . .);