How To Run Office Macros Using Automation From Visual C++ .NET

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.

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

   (http://support.microsoft.com/kb/328912/EN-US/ )

   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.

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: For more information and resources about Office Automation, visit the following Microsoft Web sites: