[Post] Call VBA in. net

Calling Visual Basic for Applications code from Visual Basic. net
Original article address
Http://msdn2.microsoft.com/en-us/library/aa159913 (office.11). aspx # odc_vbnetcallsvba_createtheaccesscode

Microsoft Office 2003 Technical articlescalling Visual Basic for Applications code from Visual Basic. net

 

Frank rice
Microsoft Corporation

May 1, 2004

Applies:
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 our cases, the COM objects were actually libraries of functions that cocould 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 t Hat coshould communicate with other applications. now fast-forward to the world. 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. net-based assemblies. this wocould 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. net Framework. however, to bridge this gap,. NET developers came up with processes collectively called com InterOP which provided des 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,. 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.

NoteIn 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 shocould verify that you have installed the PIAs during office setup. you can do this by double-clicking on the control panel, clickingAdd/Remove Programs, And then clickingMicrosoft Office Professional Edition 2003. Next, clickChange, SelectAdd or remove features, And clickNext. Then, selectChoose advanced customization of applicationsAnd clickNext. Finally, expand the listing for access, Excel, and word, verify that the setting. Net programmability supportIsRun from my computer, And clickUpdate.

Create the Visual Basic. Net automation Client

1. Start Microsoft Visual Studio. NET. OnFileMenu, clickNew, And then clickProject. SelectWindows ApplicationFrom 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. OnProjectMenu, clickAdd reference.
   2. OnComTab, locateMicrosoft Word 11.0 Object Library, And then clickSelect.
   3. Repeat the previous step for the access and Excel Object libraries.
   4. ClickOKInAdd referencesDialog Box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, clickYes.
3. OnViewMenu, clickToolbox. Add a combo box and a button to form1.
4. Double-click button1 to generate a definition for the button'sClickEvent Handler.
5. Paste the following code in the button#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. OnViewMenu, clickDesignerAnd double-click form1 to generate a definition for the form'sLoadEvent.
7. Paste the following code in the formateload 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 ofSelect... caseBlocks of code for a participant application is selected. The blocks of code use automation to start the application withCreateobjectMethod, and make application visible. The particle 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. OnFileMenu, clickNew, And then clickBlank workbookOnNew workbookTask 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. OnToolsMenu, clickReferences.
   2. InReferencesBox, verifyMicrosoft Excel 11.0 Object LibraryIs selected or select it if necessary.
   3. Next, scroll and click to selectMicrosoft ActiveX Data Object 2.7 LibraryAnd then clickOK.
3. Next, create a module and paste the Code:
   1. OnInsertMenu, clickModule.
   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).OpenMethod ofRecordsetObject uses the adcmdtext enumeration to indicate that the data source is a SQL text query as evisponby 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. theCopyfromrecordsetMethod ofExcelObject is used to copy just the data and not the field names to the worksheet.

Create the access code

The following procedure usesTransfertextMethod 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 clickingExportFromFileMenu.
   3. InExport tableDialog box, clickText FilesInSave as typeIn the drop down list, type the file name (in the example, c: \ employees.txt), and clickExport. 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, onToolsMenu, clickReferences.
   2. InReferencesBox, scroll to and verify thatMicrosoft Access 11.0 Object LibraryIs selected or select it if necessary.
   3. Repeat Step 2 forMicrosoft ActiveX Data Objects 2.7 LibraryAnd then clickOK.
   4. OnInsertMenu, clickModule.
   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,TransfertextMethod in this procedure specifies just three arguments. You can specify input and output format otherAclinkdelim. For example, you can specifyAcimportdelimOrAcimporthtml. 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 thatStringInto 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, onToolsMenu, clickReferences.
4. InReferencesBox, scroll to and verifyMicrosoft Word 11.0 Object LibraryIs selected or select it, and then clickOK.
5. Repeat the previous step forMicrosoft ActiveX Data Objects 2.7 LibraryAnd then clickOK.
6. OnInsertMenu, clickModule.
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 opensConnectionObject, creating a link to the sample database. Next,RecordsetObject is created that retrieves the list of products and prices. Then, usingDo... NextLoop, it creates and insertsStringConsisting ofProductnameAndUnitnameInto a range in the active document. The pointer moves to the next record usingMovenextFunction and the process starts over again.

Run and test the automation Client

1. In Visual Basic. net, pressF5To run the application.
2. Select an office application fromCombobox1, And then clickButton1. 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 your 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 varous office programs from Visual Basic. net and running VBA procedures.