How To Embed and Automate Office Documents with Visual Basic (242243)

MORE INFORMATION

The ability to insert an Office document into Visual Basic is made possible by Microsoft's Object Linking and Embedding (OLE) technology. OLE is designed to let one application host an object belonging to another application in a manner that is convenient for the end user, but doesn't require the applications involved to know the internal details of the other. Visual Basic provides the OLE Container Control to let Visual Basic programmers add OLE objects to a form.

Once the object is embedded, most OLE servers support Automation to let the host application programmatically change or manipulate the object from code. To get a reference to the Automation object, use the Object property of the OLE container. This property returns the Automation object that closely matches the particular item embedded.

Create a Visual Basic Application that Hosts an Excel Workbook

Start Visual Basic and create a new standard project. Form1 is created by default.
From the control toolbox, add three Command Buttons to Form1. Then add an instance of the OLE Container control. When the OLE Container control is placed on the form, it prompts you for the type of object you want to insert. For this sample, you need to add the object dynamically, so click Cancel to dismiss the dialog without adding an object.

In the Code window for Form1, add the following code:

Option Explicit

Dim oBook As Object
Dim oSheet As Object

Private Sub Command1_Click()
   On Error GoTo Err_Handler

 ' Create a new Excel worksheet...
   OLE1.CreateEmbed vbNullString, "Excel.Sheet"
   
 ' Now, pre-fill it with some data you
 ' can use. The OLE.Object property returns a
 ' workbook object, and you can use Sheets(1)
 ' to get the first sheet.
   Dim arrData(1 To 5, 1 To 5) As Variant
   Dim i As Long, j As Long
   
   Set oBook = OLE1.object
   Set oSheet = oBook.Sheets(1)
   
 ' It is much more efficient to use an array to
 ' pass data to Excel than to push data over
 ' cell-by-cell, so you can use an array.
 
 ' Add some column headers to the array...
   arrData(1, 2) = "April"
   arrData(1, 3) = "May"
   arrData(1, 4) = "June"
   arrData(1, 5) = "July"
   
 ' Add some row headers...
   arrData(2, 1) = "John"
   arrData(3, 1) = "Sally"
   arrData(4, 1) = "Charles"
   arrData(5, 1) = "Toni"
   
 ' Now add some data...
   For i = 2 To 5
      For j = 2 To 5
         arrData(i, j) = 350 + ((i + j) Mod 3)
      Next j
   Next i
   
 ' Assign the data to Excel...
   oSheet.Range("A3:E7").Value = arrData
   
   oSheet.Cells(1, 1).Value = "Test Data"
   oSheet.Range("B9:E9").FormulaR1C1 = "=SUM(R[-5]C:R[-2]C)"
   
 ' Do some auto formatting...
   oSheet.Range("A1:E9").Select
   oBook.Application.Selection.AutoFormat
   
   Command1.Enabled = False
   Command2.Enabled = False
   Command3.Enabled = True
   Exit Sub
   
Err_Handler:
   MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub

Private Sub Command2_Click()
   On Error GoTo Err_Handler
      
 ' Create an embedded object using the data
 ' stored in Test.xls...
   OLE1.CreateEmbed "C:\Test.xls"
   
   Command1.Enabled = False
   Command2.Enabled = False
   Command3.Enabled = True
   Exit Sub
   
Err_Handler:
   MsgBox "The file 'C:\Test.xls' does not exist" & _
          " or cannot be opened.", vbCritical
End Sub

Private Sub Command3_Click()
   On Error Resume Next
   
 ' Delete the existing test file (if any)...
   Kill "C:\Test.xls"
   
 ' Save the file as a native XLS file...
   oBook.SaveAs "C:\Test.xls"
   Set oBook = Nothing
   Set oSheet = Nothing
   
 ' Close the OLE object and remove it...
   OLE1.Close
   OLE1.Delete
   
   Command1.Enabled = True
   Command2.Enabled = True
   Command3.Enabled = False
End Sub

Private Sub Form_Load()
   Command1.Caption = "Create"
   Command2.Caption = "Open"
   Command3.Caption = "Save"
   Command3.Enabled = False
End Sub

Press the F5 key to run the program. Click the Create button. This embeds a new worksheet and Automates Excel to add data directly to the sheet. Note that if you double-click the object, it activates in-place and the user can edit the data directly. Now click Save to save the data to a file and close the OLE object. The Open button lets you open a copy of a previously saved file.

Create a Visual Basic Application that Hosts a Word Document

Start Visual Basic and create a new standard project. Form1 is created by default.
From the control toolbox, add three Command Buttons to Form1. Then add an instance of the OLE Container control. When the OLE Container control is placed on the form, it prompts you for the type of object you want to insert. For this sample, we need to add the object dynamically, so click Cancel to dismiss the dialog without adding an object.