How To Automate Linked and Embedded OLE Documents

MORE INFORMATION

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 type library, Excel8.olb, and add all the classes in the type library to your project. For Excel 97, the type library is located in Excel8.olb. For Excel 2000, the type library is located in Excel9.olb, and for Excel 2002, the type library is located in Excel.exe.
   
   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: If you are automating Excel 2000, this file will be Excel9.h, and if you are automating Excel 2002, this file will be Excel.h.

   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!"));
   
              //NOTE: If you are automating Excel 2002, the Range.SetValue               //method has an additional optional parameter specifying the
              //data type. Because the parameter is optional, existing code
              //will still work correctly, but new code should use the new
              //convention. The call for Excel2002 should look
              //like the following:
   
              //range.SetValue( ColeVariant( (long)DISP_E_PARAMNOTFOUND,
              //               VT_ERROR ),
              //               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.