Calling Visual Basic for Applications Code from Visual Basic .NET

 

Frank Rice
Microsoft Corporation

May 2004

Applies to:
    Microsoft® Visual Basic® .NET
    Microsoft Office Access 2003
    Microsoft Office Excel 2003
    Microsoft Office Word 2003

Summary: Using automation and COM Interop to combine the capabilities of Microsoft Visual Basic .NET applications with those of Microsoft Visual Basic for Applications (VBA) provides a convenient way to create solutions using the best of both. In this article, learn how to run VBA procedures in key Microsoft Office programs directly from Visual Basic .NET. (10 printed pages)

Contents

Introduction
Create the Visual Basic .NET Automation Client
Create the Excel Code
Create the Access Code
Create the Word Code
Run and Test the Automation Client
Conclusion

Introduction

In the days before the Microsoft® .NET Framework, the vast majority of applications were built using Component Object Model (COM) objects. In many cases, the COM objects were actually libraries of functions that could be re-used by other applications. Thus, the main goal of COM was to promote interoperability between applications by breaking up the software into self-contained, reusable components that could communicate with other applications. Now fast-forward to the world of .NET where applications are built in units called assemblies which are also self-contained and reusable by other assemblies. So what is the problem? Natively, .NET-based assemblies don't recognize COM objects and conversely, COM objects are foreign to .NET-based assemblies. This would be a substantial problem if you have a sizable amount of your code stored in COM applications and you were moving your development environment to the .NET Framework. However, to bridge this gap, the .NET developers came up with processes collectively called COM Interop which includes Primary Interop Assemblies (PIAs).

Simply put, a PIA contains mappings (also called proxies or wrappers) which allow .NET assemblies to call and use COM code for specific COM components (components for which a code-signed PIA is written). One of the things you see in this article is how to set a reference to a COM object (library) from inside Microsoft Visual Basic® .NET. This makes it possible to call COM-based Visual Basic for Applications (VBA) procedures in Microsoft Office programs from assemblies in Visual Basic .NET.

Specifically, this article describes how to call Microsoft Office 2003 procedures from a Visual Basic .NET application using automation. Automation is the process of controlling one application from another application. In this case, the .NET-based application is known as the automation client and the Office program is the automation server. You can use Microsoft Office automation to do such things as display records in a database or return a file name from a directory path.

The following example code manipulates an Office automation server (running Microsoft Office Access 2003, Microsoft Office Excel 2003, or Microsoft Office Word 2003) from an automation client based on your selection in a combo box on a form.

Note   In the next procedure you may be prompted to created PIA wrappers for the different object libraries you select. To ensure that you have the correct version, you should verify that you have installed the PIAs during Office setup. You can do this by double-clicking on the Control Panel, clicking Add/Remove Programs, and then clicking Microsoft Office Professional Edition 2003. Next, click Change, select Add or Remove Features, and click Next. Then, select Choose advanced customization of applications and click Next. Finally, expand the listing for Access, Excel, and Word, verify that the setting for .NET Programmability Support is Run from My Computer, and click Update.

Create the Visual Basic .NET Automation Client

  1. 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.

  2. Add a reference to the Access, Excel, and Word object libraries. Setting these references actually uses the PIAs to connect to the COM object libraries. To do this, follow these steps:

    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate Microsoft Word 11.0 Object Library, and then click Select.
    3. Repeat the previous step for the Access and Excel object libraries.
    4. 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.
  3. On the View menu, click ToolBox. Add a combo box and a button to Form1.

  4. Double-click Button1 to generate a definition for the button's Click event handler.

  5. 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
    
             'You will need to put the path to your own database here.
             oAccess.OpenCurrentDatabase("C:\Program Files\Microsoft Office
        \OFFICE11\Samples\Northwind.mdb", False)
    
             'Run the macro.
             oAccess.Run("ImportTxtFile")
    
             'Quit Access without saving 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:\Program Files\Book1.xls")
    
             'Run the subroutine.
             oExcel.Run("FillWorksheet")
    
             '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 "Word"
    
             Dim oWord As Word.ApplicationClass
    
             'Start Word and open the document.
             oWord = CreateObject("Word.Application")
             oWord.Visible = True
             oWord.Documents.Open("C:\Program Files\Doc1.doc")
    
             'Run the macros.
             oWord.Run("Document_New")
    
             'Quit Word.
             oWord.Quit()
             System.Runtime.InteropServices.Marshal. _
                ReleaseComObject(oWord)
             oWord = Nothing
       End Select
    
    GC.Collect()
    End Sub 
    
  6. On the View menu, click Designer and double-click Form1 to generate a definition for the form's Load event.

  7. Paste the following code in the Form1_Load procedure:

    ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
    Dim a As String() = {"Excel", "Word", "Access"}
    ComboBox1.Items.AddRange(a)
    ComboBox1.SelectedIndex = 0
    
  8. 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
    
  9. This procedure will be run after adding the following procedures to the respective Office programs.

When the code runs, the setting of the combo box determines which of the Select...Case blocks of code for a particular application is selected. The blocks of code use automation to start the application with the CreateObject method, and make application visible. The particular procedure in the Office program then runs. The application is then stopped and any objects are released.

Create the Excel Code

The following procedures fills an Excel worksheet based on a recordset created from an Access table.

  1. Create an Excel workbook named C:\Program Files\Book1.xls and then open the Visual Basic Editor. To do this, follow these steps:
    1. Start Excel.
    2. On the File menu, click New, and then click Blank Workbook on the New Workbook task panel. Save the workbook as C:\Program Files\Book1.xls.
    3. Press ALT+F11 to open the Visual Basic Editor.
  2. To add a reference to the Microsoft Excel 11.0 Object Library and the Microsoft ActiveX® Data Object 2.7 Library:
    1. On the Tools menu, click References.
    2. In the References box, verify the Microsoft Excel 11.0 Object Library is selected or select it if necessary.
    3. Next, scroll and click to select Microsoft ActiveX Data Object 2.7 Library and then click OK.
  3. Next, create a module and paste the code:
    1. On the Insert menu, click Module.

    2. Paste the following code into the new module:

      Sub FillWorksheet()
      Dim rst1 As ADODB.Recordset
      Dim sConnect As String
      Dim sSQL As String
      
      ' Create the connection string.
      sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=C:\Program Files\" & _
                 "Microsoft Office\OFFICE11\Samples\Northwind.mdb;"
      
      'Create the SQL statement.
      sSQL = "SELECT CompanyName, CompanyName " & _
             "FROM Customers " & _
             "WHERE Country = 'USA' " & _
             "ORDER BY CompanyName"
      
      'Create the recordset and run the query.
      Set rst1 = New ADODB.Recordset
      rst1.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
      
      'Check for records returned.
      If Not rst1.EOF Then
         'Dump the recordset contents in the worksheet.
         Sheet1.Range("A2").CopyFromRecordset rst1
         'Close the recordset.
         rst1.Close
         'Add headers to the worksheet.
         With Sheet1.Range("A1:B1")
            .Value = Array("Company Name", "Contact Name")
            .Font.Bold = True
         End With
         'Adjust the columns to the data.
         Sheet1.UsedRange.EntireColumn.AutoFit
      Else
         MsgBox Err.Number & " " & Err.Description
      End If
      
      'Close the recordset if it is open.
      If CBool(rst1.State And adStateOpen) Then rst1.Close
      Set rst1 = Nothing
      
      End Sub
      
  4. This procedure runs after adding the following procedures to the respective Office programs.

This procedure uses a Microsoft ActiveX® Data Object (ADO) recordset. ADO is used for data access between a client (Excel in this case) and a server (Access here). The Open method of the Recordset object uses the adCmdText enumeration to indicate that the data source is a SQL text query as evidence by sSQL. The procedure does not modify the worksheet until you populate the recordset so that the worksheet doesn't have to be undone if you cannot create the recordset. The CopyFromRecordset method of the Excel object is used to copy just the data and not the field names to the worksheet.

Create the Access Code

The following procedure uses the TransferText method to copy data from a text file to a table created in the current Access database.

  1. Open the Northwind.mdb sample database, create the text file needed for the sample, and the Visual Basic Editor with the following steps:
    1. Start Access by clicking on the Northwind.mdb sample database. The default location for the Northwind.mdb sample database is C:\Program Files\Microsoft Office\OFFICE11\Samples\Northwind.mdb.
    2. Create a comma delimited text file from the Employees table by clicking Export from the File menu.
    3. In the Export Table dialog box, click Text Files in the Save as type in the drop down list, type the file name (in the example, c:\employees.txt), and click Export. Follow the directions in the Export Text Wizard.
  2. To add the code in the module, press ALT+F11 to open the Visual Basic Editor.
    1. To add a reference to the Microsoft Access 11.0 Object Library, on the Tools menu, click References.

    2. In the References box, scroll to and verify that the Microsoft Access 11.0 Object Library is selected or select it if necessary.

    3. Repeat step 2 for the Microsoft ActiveX Data Objects 2.7 Library and then click OK.

    4. On the Insert menu, click Module.

    5. Paste the following code into the new module:

      Sub ImportTxtFile()
      
      'Import a delimited text file (Employees.txt) to the ImportedFile table.
      DoCmd.TransferText _
          acLinkDelim, , _
          "ImportedFile", _
          "c:\Employees.txt"
      
      End Sub
      
  3. This procedure runs after adding the following procedures to the respective Office program.

Looking at the procedure, the TransferText method in this procedure specifies just three arguments. You can specify input and output format other than acLinkDelim. For example, you can specify acImportDelim or acImportHTML. The second argument is a specification name. The third argument is the name of the table that contains the text file data. The forth table contains the directory path to the text file.

Create the Word Code

This procedure creates a set of records from the table in a sample Access database. The code then concatenates the fields of a record and inserts that String into a Word document.

To create a Word document and add code to it:

  1. In Word, create a document and save the document as C:\Program Files\Doc1.doc.

  2. Press ALT+F11 to open the Visual Basic Editor.

  3. To add a reference to the Microsoft Word 11.0 Object Library, on the Tools menu, click References.

  4. In the References box, scroll to and verify the Microsoft Word 11.0 Object Library is selected or select it, and then click OK.

  5. Repeat the previous step for the Microsoft ActiveX Data Objects 2.7 Library and then click OK.

  6. On the Insert menu, click Module.

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

    Private Sub Document_New()
    
       Dim Conn As New Connection
       Dim rsProducts As New Recordset
       Dim SQL As String
    
       Dim sDBPath As String
       Dim sConnection As String
       Dim sLine As String
    
       'You will need to put the path to your own database here.
       sDBPath = "C:\Program Files\Microsoft Office\OFFICE11\" & _
          "Samples\Northwind.mdb"
    
       sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Persist Security Info=False;" & _
          "Data Source=" & sDBPath
    
       Conn.Open sConnection
       SQL = "SELECT ProductName, UnitPrice FROM Products"
    
       rsProducts.Open SQL, Conn, adOpenStatic, adLockReadOnly
    
       Do While Not rsProducts.EOF
          sLine = rsProducts("ProductName") & vbTab & _
             Format(rsProducts("UnitPrice"), "Currency") & vbCrLf
    
          'Insert the name and price into the document. 
          ActiveDocument.Range.InsertAfter sLine
    
         'Move to the next record.
           rsProducts.MoveNext
       Loop
    
    End Sub
    
  8. This procedure runs in the next section.

This procedure creates and opens a Connection object, creating a link to the sample database. Next, a Recordset object is created that retrieves the list of products and prices. Then, using a Do...Next loop, it creates and inserts a String consisting of the ProductName and UnitName into a range in the active document. The pointer moves to the next record using the MoveNext function and the process starts over again.

Run and Test the Automation Client

  1. In Visual Basic .NET, press F5 to run the application.
  2. Select an Office application from ComboBox1, and then click Button1. The Office application that you select start and the specific procedure executes.

Conclusion

The ability to call COM-based applications from .NET is increasingly important as many developers transition from one development environment to another. This ability is provided by COM Interop which includes PIAs. In this article, we demonstrated the use of the PIAs by using them to automate various Office programs from Visual Basic .NET and running VBA procedures.

© Microsoft Corporation. All rights reserved.