[转贴]在.net 中调用VBA

Calling Visual Basic for Applications Code from Visual Basic .NET
原文地址
http://msdn2.microsoft.com/en-us/library/aa159913(office.11).aspx#odc_vbnetcallsvba_createtheaccesscode

Microsoft Office 2003 Technical Articles

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:
   Copy Code

      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:
   Copy Code

   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:
   Copy Code

   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:
      Copy Code

      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:
      Copy Code

      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:
   Copy Code

   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.