Bra Utbildning AB

 

<-- Back
VBA & Object models
Work with text
Bookmarks
Ranges
Fields
Form fields
Autotext
Doc var & Doc Prop
Built-in commands
Built-in dialogs
Automation
External databases

Work with Automation


An application which exposes its object model and supports automation can be referenced and launched from other applications. An Excel solution could start Word or Access and perform actions (including closing and cleaning up) within the launched  application - without any interaction with the user. An application like an  invoice application, possibly created with VB, could for instance use the best suited application for printing professional reports. Such a choice could be Word. The developer doesn't have to worry about print routines and error handlers as they come in the off-the-shelf Word-package when delivered to you.

Example
We will launch Excel from for instance Word or Access and verify that the number of sheets in the workbook at least amounts to 3 and change the name of the first three worksheets.  After renaming the sheets the file is saved and closed and Excel is shut down. With the described technique Excel is running in the back and the end user will not be aware that a second application has been launched (and terminated).

This piece of code could be found in a code module in for instance Word or Access. The code also requires a reference to the application you are about to automate, that is to Excel. You set a reference in the VB-editor, selecting Tools-References. This makes it possible to create object variables with  early (name) binding at runtime.



Dim
oApp As Excel.Application
Dim oBook As Excel.Workbook
Dim iNumberOfSheets As Long

' See GetObject and CreateObject in the next example, will also do
Set oApp = New Excel.Application

Set oBook = oApp.Workbooks.Add
With oBook
      iNumberOfSheets = .Worksheets.Count
      If iNumberOfSheets < 3 Then .Worksheets.Add Count:=3 - iNumberOfSheets

      .Worksheets(1).Name = "Total amount"
      .Worksheets(2).Name = "January"
      .Worksheets(3).Name = "February"

      .Close SaveChanges:=True, FileName:="C:\Data\TestReport.xls"
End With

oApp.Quit
Set oApp = Nothing 


This is an example how you automate Word from for instance Access or Excel. 
A reference to the Word library has to be set (Tools-References) in the VB-editor. This makes it possible to create object variables with  early (name) binding at runtime.

Sub CallWord()
Dim oApp As Word.Application
Dim oDoc As Word.Document
Dim bClosed As Boolean

On Error GoTo Errorhandler 
Set oApp = GetObject(, "Word.Application")

With oApp 
   Set oDoc = .Documents.Add("path and templatename.dot") 
   oDoc.PrintOut 
   oDoc.Close wdDoNotSaveChanges

   'Close Word if it wasn't running
   If bClosed Then
      .Quit
   End If

End With
Bye:

set oApp = Nothing

Exit Sub
'---------------------------------------- 
Errorhandler: 
   Select Case Err 
      Case 429 
         Set oApp = CreateObject("Word.Application")
         bClosed = True
         Resume Next 
      Case Else 
         MsgBox "An error message " & Err.Description 
         Resume Bye
   End Select
End Sub

This is an example how you automate Word from for instance Access or Excel. Background/set up: 
Suppose you have no idea which version of Word the user will be using. You are not willing to take the chance that a reference to an old version (like Word97) would work fine also in later versions of Word. Here no reference is set to Word (Tools-References). The object variables created will use late binding. Using late binding slows down execution of code and will not supply you with the IntelliSense drop-downs when developing code.

Sub CallWordOnceMore()
Dim oApp As Object
Dim oDoc As Object
Dim bClosed As Boolean

On Error GoTo Errorhandler 
Set oApp = GetObject(, "Word.Application")

With oApp 
   Set oDoc = .Documents.Add("path and templatename.dot") 
   oDoc.PrintOut 
   oDoc.Close 0

   'Close Word if it wasn't running
   If bClosed Then
      .Quit
   End If

End With
Bye:

Set oDoc = Nothing
Set oApp = Nothing

Exit Sub
'---------------------------------------- 
Errorhandler: 
   Select Case Err 
      Case 429 
         Set oApp = CreateObject("Word.Application")
         bClosed = True
         Resume Next 
      Case Else 
         MsgBox "An error message " & Err.Description 
         Resume Bye
   End Select
End Sub