Microsoft KB Archive/303871

= How To Create an Excel Macro by Using Automation from Visual Basic .NET =

Article ID: 303871

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q303871



For a Microsoft Visual C# .NET version of this article, see 303872.

IN THIS TASK

 * SUMMARY
 * Steps to Create the Sample Visual Basic .NET Application
 * Additional Notes for Office XP
 * REFERENCES



SUMMARY
This step-by-step article describes how to automate Microsoft Excel from Microsoft Visual Basic .NET to create a workbook that contains a new macro that is associated with a CommandBar button.

back to the top

Steps to Create the Sample Visual Basic .NET Application
 Start Microsoft Visual Basic .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 references to Microsoft Excel Object Library, Microsoft Office Object Library, and Microsoft Visual Basic for Applications Extensibility Library. To do this, follow these steps:  On the Project menu, click Add Reference. Click the COM tab, click Microsoft Excel Object Library, and then click Select.

Note Microsoft Office 2003 includes Primary Interop Assemblies (PIAs). Microsoft Office XP does not include PIAs, but they may be downloaded. For additional information about Office XP PIAs, click the following article number to view the article in the Microsoft Knowledge Base:

328912 INFO: Microsoft Office XP PIAs Are Available for Download

 Select Microsoft Visual Basic for Applications Extensibility Library, and then click Select. Click OK in the Add References dialog box to accept your selections.</li></ol> </li> On the View menu, click Toolbox to display the Toolbox, and add a button to Form1.</li>  Double-click Button1. The code window opens at the onClick event for Button1. Add the following line above Public Class Form1: Imports Office = Microsoft.Office.Core </li>  In the code window, add the following code: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As     System.EventArgs) Handles Button1.Click Dim oExcel As Excel.Application Dim oBook As Excel.Workbook Dim oModule As VBIDE.VBComponent Dim oCommandBar As Office.CommandBar Dim oCommandBarButton As Office.CommandBarControl Dim sCode As String

' Create an instance of Excel, and show it to the user. oExcel = New Excel.Application

' Add a workbook. oBook = oExcel.Workbooks.Add

' Create a new VBA code module. oModule = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule)

sCode = &quot;sub VBAMacro&quot; & vbCr & _ &quot;  msgbox &quot;&quot;VBA Macro called&quot;&quot; &quot; & vbCr & _ &quot;end sub&quot;

' Add the VBA macro to the new code module. oModule.CodeModule.AddFromString(sCode)

Try ' Create a new toolbar, and show it to the user. oCommandBar = oExcel.CommandBars.Add(&quot;VBAMacroCommandBar&quot;) oCommandBar.Visible = True

' Create a new button on the toolbar. oCommandBarButton = oCommandBar.Controls.Add(Office.MsoControlType.msoControlButton) ' Assign a macro to the button. oCommandBarButton.OnAction = &quot;VBAMacro&quot; ' Set the caption of the button. oCommandBarButton.Caption = &quot;Call VBAMacro&quot; ' Set the icon on the button to a picture. oCommandBarButton.FaceId = 2151 Catch exc As Exception MessageBox.Show(&quot;VBAMacroCommandBar already exists.&quot;, &quot;Error&quot;) End Try

oExcel.Visible = True ' Set the UserControl property so that Excel does not shut down. oExcel.UserControl = True

' Release the variables. oCommandBarButton = Nothing oCommandBar = Nothing oModule = Nothing oBook = Nothing oExcel = Nothing

' Force garbage collection. GC.Collect

End Sub </li>  Add the following code to the top of Form1.vb: Imports Office = Microsoft.Office.Core Imports Microsoft.Office.Interop Imports VBIDE = Microsoft.Vbe.Interop </li> Press F5 to build and then run the program.</li> Click Button1 to start Excel, insert the Visual Basic for Applications (VBA) code, and then add a new CommandBar control. Click the button on the CommandBar to run the VBA macro.</li></ol>

back to the top

Additional Notes for Office XP
Microsoft Office XP and Microsoft Office 2003 applications have a security option that allows programmatic access to the VBA object model. If this setting is Off (the default), you may receive an error when you run the sample code. For additional information about this setting and how you can correct the error, click the following article number to view the article in the Microsoft Knowledge Base:

282830 PRB: Programmatic Access to Office XP VBA Project Is Denied

back to the top

<div class="references_section">