HOWTO: Automate Linked and Embedded ActiveX Documents

• The Microsoft Foundation Classes (MFC), used with:
  • Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0
  • Microsoft Office 97 for Windows

SUMMARY

This article describes how to automate an embedded or linked ActiveX document. It contains sample code for acquiring an IDispatch interface pointer to the embedded or linked document's server, as well as step-by- step instructions for automating a linked Microsoft Excel 97 worksheet. While you can use the code in your application, the real benefit comes from reading and understanding the sample.

MORE INFORMATION

Follow these steps to create a sample application that shows how to automate embedded or linked documents by automating a linked Excel 97 worksheet.

1. Use the AppWizard in Visual Studio to create a new MFC (EXE) project named "AutomateLink".



2. Select Container as the type of Compound Document Support to include, and accept all other default settings.



3. Click on the ResourceView tab in the Workspace, and go to the menus. Add a new menu item, "Automate Excel", with id ID_AUTOMATEXL, to the bottom of the file menu of IDR_AUTOMATYPE and IDR_AUTOMATYPE_CNTR_IP.



4. Save and close the menu resources, and click ClassWizard on the View menu of Visual Studio. Select the Message Maps tab, and select the CAutomateLinkView class from the "Class name" list box. Add a Command handler for the ID_AUTOMATEXL message. Accept the default name "OnAutomatexl."



5. While still in ClassWizard (or, click ClassWizard on the View menu) select the Automation tab, click the Add Class button and choose From Type Library. Locate the Microsoft Excel 97 type library, Excel8.olb, and add all the classes in the type library to your project.
   
   This generates a file of IDispatch wrapper classes (excel8.cpp) and its associated header file. Add the following line to the top of the AutomateLinkView.cpp file:




      #include "excel8.h" 

Note: The default folder for Excel8.olb is:

C:\Program Files\Microsoft Office\Office\

6. Add a new public member function to CAutomateLinkView in CAutomateLinkView.h:




      HRESULT GetDocIDispatch( LPDISPATCH* ppDisp ); 

7. Add the following code to CAutomateLinkView.cpp:




      void CAutomateLinkView::OnAutomatexl()
      {
         // Query for the dispatch pointer for the embedded object. In
         // this case, this is the Excel worksheet.
         LPDISPATCH lpDisp;
         HRESULT hr = GetDocIDispatch( &lpDisp );

         // If you got an IDispatch, then use it to Automate Excel.
         if (SUCCEEDED(hr)){
            _Workbook wb;
            Worksheets wsSet;
            _Worksheet ws;
            Range range;
                
            //Set _Workbook wb to use lpDisp, the IDispatch* of the
            //embedded workbook
            wb.AttachDispatch(lpDisp);
        
            //Then get the first worksheet in the workbook
            wsSet = wb.GetWorksheets();
            ws = wsSet.GetItem(COleVariant((short)1));

            //Get the  Range object corresponding to cell A1 
            range = ws.GetRange(COleVariant("A1"), COleVariant("A1"));

            // Fill A1 with the string "Hello, World!"
            range.SetValue(COleVariant("Hello, World!"));
         }
      }

      /********************************************************************
      *  GetDocIDispatch -- This method determines if the document is
      *  embedded or linked and acquires an IDispatch pointer to the
      *  embedded document server for use in Automation. The document must
      *  be activated for this method to succeed.
      *
      *  Parameters: ppDisp -- The address of an LPDISPATCH to be filled
      *                        with the IDispatch pointer of the embedded
      *                        document server.
      *  Returns: S_OK if successful, otherwise
      *           an HRESULT reporting the error that occurred.
      ********************************************************************/ 
      HRESULT CAutomateLinkView::GetDocIDispatch(LPDISPATCH *ppDisp)
      {
         HRESULT hr = S_OK;
         IOleLink* lpLink = NULL;
         IMoniker* lpMoniker = NULL;
         IRunningObjectTable* lpROT = NULL;
         IUnknown* lpUnk = NULL;

         // First, try to get an IOleLink interface from the document. If
         // successful, this indicates that the document is linked as
         // opposed to embedded.
         hr = m_pSelection->m_lpObject->QueryInterface(IID_IOleLink,
            (void**)&lpLink);

         if (SUCCEEDED(hr))
         {
            // Get the moniker of the source document for this link. You
            // need this to find the ActiveX Document Server.
            hr = lpLink->GetSourceMoniker( &lpMoniker );

            if (SUCCEEDED(hr))
            {
               // For linked documents, search the Running Object
               //Table for the relevant server. Do this through the
               //IRunningObjectTable interface, which you can get through
               // an API call.
               hr = GetRunningObjectTable(0, &lpROT );

               if (SUCCEEDED(hr))
               {
                  // Search the Running Object Table for the ActiveX
                  // Document Server of this document. You'll get back an
                  // IUnknown pointer to that server.
                  hr = lpROT->GetObject( lpMoniker, &lpUnk );

                  if (SUCCEEDED(hr))
                  {
                     // Finally, get the IDispatch pointer from the
                     // IUnknown pointer.
                     hr = lpUnk->QueryInterface( IID_IDispatch,
                        (void**)ppDisp );
                  }
               }
            }
         }

         // If that failed, try for a direct IDispatch pointer. This
         // indicates that the document is embedded.
         else
         {
            hr = m_pSelection->m_lpObject->QueryInterface( IID_IDispatch,
               (void**)ppDisp );
         }

         // Clean up interface pointers you may have acquired along the
         // way.
         if (lpLink)
            lpLink->Release();
         if (lpMoniker)
            lpMoniker->Release();
         if (lpROT)
            lpROT->Release();
         if (lpUnk)
            lpUnk->Release();

         return hr;
      } 

8. Compile and run the application. Insert an existing Excel worksheet, and activate it. Select Automate Excel from the File menu. The OnAutomatexl method gets an IDispatch pointer to the embedded document server and uses it to fill cell A1 with "Hello, World!". Close that document, without saving changes, and select New from the File menu to create a new document. Insert the existing Excel worksheet, this time as a link, and activate it. To do so, point to LinkedWorksheetObject on the Edit menu, and then click Edit. Then, in your application, click Automate Excel on the File menu and OnAutomatexl again finds the correct IDispatch pointer to put "Hello, World!" in cell A1.

REFERENCES

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

Q184663 HOWTO: Embed and Automate a Microsoft Excel worksheet with MFC

Q179706 HOWTO: Use MFC to Automate Excel & Create/Format a New Workbook

Q186120 HOWTO: Use MFC to Automate Excel and Fill a Range with an Array

Q186427 HOWTO: Catch Microsoft Excel 97 Application Events Using VC++

Additional query words:

Keywords : kbcode kbinterop kbole kbAutomation kbExcel kbMFC kbVC500 kbVC600 kbMFC600 kbOffice
Version : winnt:5.0,6.0
Platform : winnt
Issue type : kbhowto