Microsoft KB Archive/306686

From BetaArchive Wiki

Article ID: 306686

Article Last Modified on 6/29/2007


APPLIES TO

  • Microsoft Access 2002 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft PowerPoint 2002 Standard Edition
  • Microsoft Word 2002 Standard Edition
  • Microsoft Visual C++ .NET 2002 Standard Edition
  • Microsoft Visual C++ .NET 2003 Standard Edition


This article was previously published under Q306686

For a Microsoft Visual Basic .NET version of this article, see 306682.
For a Microsoft Visual C# version of this article, see 306683.


SUMMARY

With Microsoft Office Automation, you can open a document or create a new document that contains a Visual Basic for Applications (VBA) macro and execute the macro at run time. This article demonstrates how to call Office macros from a Visual C++ .NET Automation client.

MORE INFORMATION

The following sample Automation client manipulates an Office Automation server (Access, Excel, PowerPoint or Word) based on your selection on a form. After the client starts the Automation server, it opens a document and then calls two macros. The first macro, DoKbTest, has no parameters and the second macro, DoKbTestWithParameter, takes a single parameter of type String.

Create Office Documents Containing Macros

  1. Create a Word document named C:\Doc1.doc. To do this, follow these steps:
    1. In Word, create a new document.
    2. Press ALT+F11 to open the Visual Basic Editor.
    3. On the Insert menu, click Module.

    4. Paste the following macro code into the new module:

      'Display a message box that displays the application name.
Public Sub DoKbTest()
   MsgBox "Hello from " & Application.Name
End Sub

'Display a message box with the string passed from the
'Automation client.
Public Sub DoKbTestWithParameter( sMsg As String )
   MsgBox sMsg
End Sub
    5. Close the Visual Basic Editor, save the Word document, and quit Word.
  2. Create an Excel workbook named C:\Book1.xls by using steps similar to those that you used to create the Word document.
  3. Create a PowerPoint presentation named C:\Pres1.ppt by using steps similar to those that you used to create the Word document.
  4. Create a new Access database named C:\Db1.mdb. To do this, follow these steps:
    1. On the Insert menu, click Module.
    2. Paste the macro code in the new module.
    3. Save the module and quit Access.

Create the Visual C++ .NET Automation Client

  1. Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Select Managed C++ Applicationfor Visual Studio .NET 2002 or Console Application (.NET) for Visual Studio .NET 2003 from the Visual C++ Projects types. Name the project OfficeMacros and create the project in C:\.
  2. If you have not already done so, download, install, and then register the Microsoft Office XP Primary Interop Assemblies (PIAs). For additional information about Office XP PIAs, click the article number below to view the article in the Microsoft Knowledge Base:

    328912 INFO: Microsoft Office XP PIAs Are Available for Download

    Install the PIAs in C:\Program Files\Microsoft Visual Studio .NET\Enterprise Frameworks or another equally accessible folder. They will also be registered in the Global Assembly Cache.
  3. Add the folder in which you installed the Primary Interop Assembly DLL files to the list of directories that are searched to resolve file references that are passed to the #using directive. To do this, follow these steps:
    1. In the Visual Studio .NET Solution Explorer pane, right-click the project and click Properties.
    2. In the left pane of the Property Pages dialog box, click the C/C++ folder and then click the General folder.
    3. In the Resolve #using References dialog box, add the C:\Program Files\Microsoft Visual Studio .NET\Enterprise Frameworks path, and then click OK. If you installed the PIAs in a different folder, use the full path to that folder, instead.

  4. Open OfficeMacros.cpp in the source editor and replace the contents with the following code:

    // This is the main project file for VC++ application project 
// generated using an Application Wizard.

#include "stdafx.h"

   #using <mscorlib.dll>

   #using "Office.dll"
   #using "Microsoft.Office.Interop.word.dll"
   #using "Microsoft.Office.Interop.excel.dll"
   #using "Microsoft.Office.Interop.powerpoint.dll"
   #using "Microsoft.Office.Interop.Access.dll"
   #using "Microsoft.vbe.interop.dll"

   using namespace System;
   //using namespace System::Diagnostics;
   using namespace System::Reflection;
   using namespace Microsoft::Office::Core;
   using namespace Microsoft::Office::Interop;

   #include <tchar.h>

   void PrintMenu();
   void AutoCallAccess();
   void AutoCallExcel();
   void AutoCallPowerPoint();
   void AutoCallWord();
   void CallMacro(Object* oApp, Object* oArgs[]);

   int _tmain(void)
   {
    PrintMenu();
    String* s = Console::ReadLine();
    while( !s->ToLower()->Equals(S"q") )
    {
        Int32 i;
        try
        {
            i = Convert::ToInt32(s,10);
        }
        catch( Exception* e )
        {
            goto print;
        }

        // Select the Office application to automate based on user input.

        switch( i )
        {
        case 1:
            AutoCallAccess();
            break;
        case 2:
            AutoCallExcel();
            break;
        case 3:
            AutoCallPowerPoint();
            break;
        case 4:
            AutoCallWord();
            break;
        default:
            ;
        }
   print:
        PrintMenu();
        s = Console::ReadLine();
    }
    return 0;
   }

   void PrintMenu()
   {
    Console::WriteLine(S"\n\nEnter the number of the application you'd like to automate.");
    Console::WriteLine(S"Enter 'q' to quit the application.\n");
    Console::WriteLine(S"\t\t1. Microsoft Access");
    Console::WriteLine(S"\t\t2. Microsoft Excel");
    Console::WriteLine(S"\t\t3. Microsoft PowerPoint");
    Console::WriteLine(S"\t\t4. Microsoft Word\n");
    Console::Write(S"\tSelection:");
   }

   void AutoCallAccess()
   {
    try{
        //Start Access, make it visible, and open C:\Db1.mdb.
        Console::WriteLine("\nStarting Microsoft Access...");
        Access::ApplicationClass* pAccess = new Access::ApplicationClass();
        pAccess->Visible = true;
        pAccess->OpenCurrentDatabase("c:\\db1.mdb", false, "");
        //Run the macros.
        System::Object* oParams[] = {new String("DoKbTest"), 
            System::Reflection::Missing::Value};
        CallMacro(pAccess, oParams);
        oParams[0] = new String("DoKbTestWithParameter");
        oParams[1] = new String("Hello From Visual C++ .NET (AutoCallAccess)");
        CallMacro(pAccess, oParams);    
        //Quit Access and clean up.
        pAccess->get_DoCmd()->Quit(Access::AcQuitOption::acQuitSaveNone);
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pAccess);
        GC::Collect();
    }
    catch(Exception* e)
    {
        Console::WriteLine(S"Error automating Access...");
        Console::WriteLine(e->get_Message());
    }
   }

   void AutoCallExcel()
   {
    try{
        //Start Excel, make it visible, and open C:\Book1.xls.
        System::Object* oMissing = System::Reflection::Missing::Value;
        Console::WriteLine("\nStarting Microsoft Excel...");
        Excel::ApplicationClass* pExcel = new Excel::ApplicationClass();
        pExcel->Visible = true;
        Excel::Workbooks* pBooks = pExcel->get_Workbooks();
        Excel::_Workbook* pBook = pBooks->Open("c:\\book1.xls", oMissing, oMissing,
            oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, 
            oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

        //Run the macros.
        System::Object* oParams[] = {new String("DoKbTest"), oMissing};
        CallMacro(pExcel, oParams);
        oParams[0] = new String("DoKbTestWithParameter");
        oParams[1] = new String("Hello From Visual C++ .NET (AutoCallExcel)");
        CallMacro(pExcel, oParams);
        //Quit Excel and clean up.
        pBook->Close(false, oMissing, oMissing);
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pBook);
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pBooks);
        pExcel->Quit();
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pExcel);
        GC::Collect();
    }
    catch(Exception* e)
    {
        Console::WriteLine(S"Error automating Excel...");
        Console::WriteLine(e->get_Message());
    }
   }

   void AutoCallPowerPoint()
   {
    try{
        //Start PowerPoint, make it visible, and open C:\Pres1.ppt.
        Console::WriteLine("\nStarting Microsoft PowerPoint...");
        PowerPoint::ApplicationClass* pPPT = new PowerPoint::ApplicationClass();
        pPPT->Visible =  Microsoft::Office::Core::MsoTriState::msoTrue;
        
        PowerPoint::Presentations* pPresSet = pPPT->get_Presentations();
        PowerPoint::_Presentation* pPres = pPresSet->Open("C:\\pres1.ppt", 
            MsoTriState::msoFalse, 
            MsoTriState::msoFalse,
            MsoTriState::msoTrue);
        
        //Run the macros.
        System::Object* oParams[] = {new String("'pres1.ppt'!DoKbTest"), 
            System::Reflection::Missing::Value};
        CallMacro(pPPT, oParams);
        oParams[0] = new String("'pres1.ppt'!DoKbTestWithParameter");
        oParams[1] = new String("Hello From Visual C++ .NET (AutoCallPowerPoint)");
        CallMacro(pPPT, oParams);       
        //Quit PowerPoint and clean up.
        pPres->Close();
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pPres);
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pPresSet);
        pPPT->Quit();
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pPPT);
        GC::Collect();
    }
    catch(Exception* e)
    {
        Console::WriteLine(S"Error automating PowerPoint...");
        Console::WriteLine(e->get_Message());
    }
   }

   void AutoCallWord()
   {
    try{
        //Start Word, make it visible, and open C:\Doc1.doc.
        System::Object* oMissing = System::Reflection::Missing::Value;
        Console::WriteLine("\nStarting Microsoft Word...");
        Word::ApplicationClass* pWord = new Word::ApplicationClass;
        pWord->Visible = true;
        Word::Documents* pDocs = pWord->Documents;
        System::Object* oFile = new System::Object;
        oFile = S"c:\\doc1.doc";
        Word::_Document* pDoc = pDocs->Open(&oFile, &oMissing,
            &oMissing, &oMissing, &oMissing, &oMissing, &oMissing,
            &oMissing, &oMissing, &oMissing, &oMissing, &oMissing,
            &oMissing, &oMissing, &oMissing);

        //Run the macros.
        System::Object* oParams[] = {new String("DoKbTest"), oMissing};
        CallMacro(pWord, oParams);
        oParams[0] = new String("DoKbTestWithParameter");
        oParams[1] = new String("Hello From Visual C++ .NET (AutoCallWord)");
        CallMacro(pWord, oParams);      
        //Quit Word and clean up.
        pDoc->Close(&oMissing, &oMissing, &oMissing);
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pDoc);
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pDocs);
        pWord->Quit(&oMissing, &oMissing, &oMissing);
        System::Runtime::InteropServices::Marshal::ReleaseComObject(pWord);
        GC::Collect();
    }
    catch(Exception* e)
    {
        Console::WriteLine(S"Error automating Word...");
        Console::WriteLine(e->get_Message());
    }
   }

   void CallMacro(Object* oApp, Object* oArgs[])
   {
    Console::WriteLine("Calling Macro...");
    oApp->GetType()->InvokeMember("Run",
        BindingFlags(BindingFlags::Default | BindingFlags::InvokeMethod),
        NULL, oApp, oArgs);

   }

Run and Test the Automation Client

  1. Press F5 to build and run the Automation client.

  2. When the client runs, you are presented with the following selection:

    Enter the number of the application you'd like to automate.
Enter 'q' to quit the application.

                1. Microsoft Access
                2. Microsoft Excel
                3. Microsoft PowerPoint
                4. Microsoft Word

        Selection:
  3. At the prompt, type 1 and press ENTER. The Automation client starts Access, runs the macros in the database that you created, and then quits Access.
  4. Repeat the previous step to automate Excel, PowerPoint and Word.
  5. When finished, type q and press ENTER to quit the Automation client.


REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

303872 How To Create an Excel Macro Using Automation from Visual C# .NET


177760 VBA: How to Run Macros in Other Office Programs


317157 PRB: Errors When You Reference the Access 10.0 Type Library with Visual Studio .NET


For more information and resources about Office Automation, visit the following Microsoft Web sites:

Microsoft Office Development with Visual Studio
http://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx

FAQs And Highlights for Office Development
http://support.microsoft.com/ofd


Keywords: kbautomation kbhowto KB306686



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/306686&oldid=379974’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki