How To Run Office Macros by Using Automation from Visual Basic .NET

http://support.microsoft.com/default.aspx?scid=kb;EN-US;306682


Create the Visual Basic .NET Automation Client
Start Microsoft Visual Studio .NET. On the File menu, click New, and then click Project. Select Windows Application from the Visual Basic Projects types. Form1 is created by default.
Add a reference to the Access, Excel, PowerPoint, and Word object libraries. To do this, follow these steps:
On the Project menu, click Add Reference.
On the COM tab, locate Microsoft Word 10.0 Object Library or Microsoft Word 11.0 Object Library, and then click Select.NOTE: If you are using Microsoft Office XP and you have not already done so, Microsoft recommends that you download and then install 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
Repeat the previous step for the Access, Excel, and PowerPoint object libraries.
Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes.NOTE: If you receive an error message when you reference the Access 10.0 object library, see the "Troubleshooting" section.

On the View menu, click ToolBox. Add a combo box and a button to Form1.
Double-click Button1 to generate a definition for the button's Click event handler.
Paste the following code in the Button1_Click procedure:
Select Case ComboBox1.SelectedItem

Case "Access"

Dim oAccess As Access.ApplicationClass

'Start Access and open the database.
oAccess = CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase("c:\db1.mdb", False)

'Run the macros.
oAccess.Run ("DoKbTest")
oAccess.Run("DoKbTestWithParameter", "Hello from VB .NET Client")

'Clean-up: Quit Access without saving changes to the database.
oAccess.DoCmd().Quit (Access.AcQuitOption.acQuitSaveNone)
System.Runtime.InteropServices.Marshal.ReleaseComObject (oAccess)
oAccess = Nothing

Case "Excel"

Dim oExcel As Excel.ApplicationClass
Dim oBook As Excel.WorkbookClass
Dim oBooks As Excel.Workbooks

'Start Excel and open the workbook.
oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oBooks = oExcel.Workbooks
oBook = oBooks.Open("c:\book1.xls")

'Run the macros.
oExcel.Run ("DoKbTest")
oExcel.Run("DoKbTestWithParameter", "Hello from VB .NET Client")

'Clean-up: Close the workbook and quit Excel.
oBook.Close (False)
System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook)
oBook = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject (oBooks)
oBooks = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject (oExcel)
oExcel = Nothing

Case "PowerPoint"

Dim oPP As PowerPoint.ApplicationClass
Dim oPresSet As PowerPoint.Presentations
Dim oPres As PowerPoint.PresentationClass

'Start PowerPoint and open the presentation.
oPP = CreateObject("PowerPoint.Application")
oPP.Visible = True
oPresSet = oPP.Presentations
oPres = oPresSet.Open("c:\pres1.ppt", , , True)

'Run the macros.
oPP.Run ("'pres1.ppt'!DoKbTest")
oPP.Run("'pres1.ppt'!DoKbTestWithParameter", "Hello from VB .NET Client")

'Clean-up: Close the presentation and quit PowerPoint.
oPres.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject (oPres)
oPres = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject (oPresSet)
oPresSet = Nothing
oPP.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject (oPP)
oPP = Nothing

Case "Word"

Dim oWord As Word.ApplicationClass

'Start Word and open the document.
oWord = CreateObject("Word.Application")
oWord.Visible = True
oWord.Documents.Open ("C:\Doc1.doc")

'Run the macros.
oWord.Run ("DoKbTest")
oWord.Run("DoKbTestWithParameter", "Hello from VB .NET Client")

'Quit Word.
oWord.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWord)
oWord = Nothing

End Select

GC.Collect()
On the View menu, click Designer and double-click Form1 to generate a definition for the form's Load event.
Paste the following code in the Form1_Load procedure:
ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
Dim a As String() = {"Access", "Excel", "PowerPoint", "Word"}
ComboBox1.Items.AddRange(a)
ComboBox1.SelectedIndex = 0

Add the following code to the top of Form1.vb:
Imports Access = Microsoft.Office.Interop.Access
Imports Excel = Microsoft.Office.Interop.Excel
Imports Word = Microsoft.Office.Interop.Word
Imports PowerPoint = Microsoft.Office.Interop.PowerPoint

Run and Test the Automation Client
Press F5 to run the application.
Select an Office application from ComboBox1, and then click Button1. The Office application that you selected is started and the DoKBTest and DoKBTestWithParameter macros are executed.
Troubleshooting
When you reference the Access 10.0 object library in a Visual Basic .NET project, you may receive an error message that states that conversion of the library to a .NET assembly failed. For additional information about how to resolve this problem so that you can successfully reference the Access 10.0 object library, click the article number below to view the article in the Microsoft Knowledge Base:
317157 PRB: Errors When Referencing the Microsoft Access 10.0 Type Library