How To embed and automate Office documents with Visual Basic

Article
10/22/2021
Applies to:

Microsoft Office

Summary

When developing an application that uses data in an Office document, it might be beneficial to have that data presented directly within your Visual Basic application so that the user can see and edit the data without having to switch to the Office application. You can accomplish this in Visual Basic using the OLE Container Control.

This article demonstrates how to dynamically create and Automate an Office document using the OLE Container Control.

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.<?xm-insertion_mark_start author="v-thomr" time="20070327T040420-0600"?> If this code is run in Microsoft Office
 ' Excel 2007, <?xm-insertion_mark_end?><?xm-deletion_mark author="v-thomr" time="20070327T040345-0600" data=".."?><?xm-insertion_mark_start author="v-thomr" time="20070327T040422-0600"?>change the file name to Test.xlsx.<?xm-insertion_mark_end?>
   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.