Technical Guide: Embedding and Automating Office Documents via Visual Basic

🚀 1. Overview

For IT administrators and developers managing legacy or specialized business applications, providing a seamless user experience often requires integrating Microsoft Office functionality directly within a custom interface. Instead of forcing users to toggle between a standalone Visual Basic (VB) application and Microsoft Excel or Word, developers can utilize Object Linking and Embedding (OLE) technology.

By leveraging the OLE Container Control, a VB application can host Office documents internally. This allows the application to not only display data but also to programmatically manipulate that data via Automation. Once a document is embedded, the application can access the Office server’s object model, enabling the automation of complex tasks such as data entry, formatting, and reporting without leaving the primary application window.

⚙️ 2. Key Technical Details

The integration relies on the OLE Container Control acting as a bridge between the Visual Basic form and the Office “server” application.

🔹 Object Linking and Embedding (OLE) vs. Automation

• OLE Technology: This allows one application to host an object (like a spreadsheet or a document) created by another application. The host doesn’t need to understand the internal data structure of the object; it simply provides a space for the “server” application to render its content.
• Automation: Once an object is embedded, the Object property of the OLE container provides a reference to the underlying Automation object. For Excel, this returns a Workbook; for Word, it returns a Document. This allows the developer to write code that controls the Office application programmatically.

🔹 Implementing Excel Integration

When automating Excel, efficiency is critical. Rather than pushing data to the spreadsheet cell-by-cell—which creates significant overhead—it is recommended to use a Variant Array to pass blocks of data in a single operation.

The following code demonstrates dynamic embedding and data population in Excel:

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. If this code is run in Microsoft Office
 ' Excel 2007, change the file name to Test.xlsx.
   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

🔹 Implementing Word Integration

Embedding Word documents follows a similar logic. The OLE1.Object provides access to the Selection and ParagraphFormat objects, allowing the application to generate professional documents dynamically.

Option Explicit

Dim oDocument As Object

Private Sub Command1_Click()
   On Error GoTo Err_Handler

' Create a new Word Document...
   OLE1.CreateEmbed vbNullString, "Word.Document"

' Add some text to the document. The OLE.Object
 ' property returns the document object...
   Set oDocument = OLE1.object

oDocument.Content.Select
   With oDocument.Application.Selection

' Add a heading at the top of the document...
      .Style = oDocument.Styles("Heading 1")
      .Font.Color = &HFF0000
      .TypeText "Blue Sky Airlines"
      .ParagraphFormat.Alignment = 1 '[wdAlignParagraphCenter]
      .TypeParagraph
      .TypeParagraph

' Now add some text...
      .TypeText "Dear Mr. Smith,"
      .TypeParagraph
      .TypeParagraph
      .TypeText "Thank you for your interest in our current fares " & _
                "from Oakland to Sacramento. We guarantee to be " & _
                "the lowest price for local flights, or we'll " & _
                "offer to make your next flight FREE!"
      .TypeParagraph
      .TypeParagraph
      .TypeText "The current fare for a flight leaving Oakland " & _
                "on October 4, 1999 and arriving in Sacramento " & _
                "the same day is $54.00."
      .TypeParagraph
      .TypeParagraph
      .TypeText "We hope you will choose to fly Blue Sky Airlines."
      .TypeParagraph
      .TypeParagraph
      .TypeText "Sincerely,"
      .TypeParagraph
      .TypeParagraph
      .TypeParagraph
      .TypeText "John Taylor"
      .TypeParagraph
      .Font.Italic = True
      .TypeText "Regional Sales Manager"
      .TypeParagraph

End With

' Zoom to see entire document...
   OLE1.SizeMode = 3
   OLE1.DoVerb -1

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.doc. If this code is run in Microsoft Office
 ' Word 2007, change the file name to Test.docx.
   OLE1.CreateEmbed "C:\Test.doc"

Command1.Enabled = False
   Command2.Enabled = False
   Command3.Enabled = True
   Exit Sub

Err_Handler:
   MsgBox "The file 'C:\Test.doc' does not exist" & _
          " or cannot be opened.", vbCritical
End Sub

Private Sub Command3_Click()
   On Error Resume Next

' Delete the existinf test file (if any)...
   Kill "C:\Test.doc"

' Save the file as a native Word DOC file...
   oDocument.SaveAs "C:\Test.doc"
   Set oDocument = 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

🔹 Advanced Deployment Considerations

• Data Binding: The OLE Container is data-aware. It can be bound to an Access database (OLE Object field). When the record is accessed, the document is automatically loaded into the container, and changes are synced back to the database upon closing.
• Menu Negotiation: By setting the NegotiateMenus property of a form to True, the host application can merge its own menu items with those of the Office application when the object is active.
• ActiveX Documents: For scenarios requiring the document to remain “always-active,” ActiveX Documents (supported by Excel, Word, and PowerPoint) are preferred. While VB lacks a native ActiveX Document host, the WebBrowser control can be used for this purpose.

🛡️ 3. Impact

Implementing OLE and Automation significantly impacts both the end-user experience and application maintenance:

• User Efficiency: Users can perform complex Office tasks (like using Excel formulas) without switching context, leading to higher productivity and a cleaner workflow.
• Data Integrity: Embedding creates a copy of the data. Admin should be aware that SaveToFile creates an OLE-specific container file, not a standard Office file. To ensure files remain compatible with standalone Office applications, developers must use the SaveAs method via Automation.
• UI Limitations: In-place activation often replaces the host application’s menus and does not support docked toolbars (though floating toolbars can be enabled via code). Admins must account for these UI shifts when training users.
• Version Sensitivity: Note that certain features, such as specific command bar manipulations, may not apply to Office 2007 and newer versions.

⚠️ Important: To ensure an OLE object is programmatically activated for editing, use the DoVerb method with the vbOLEShow (-1) constant.

---

Official Source: Read the full article on Microsoft.com