Visual Basic 6 and VBA code fragments for Automation

In the Visual Basic 6 code fragments below are included in the Code-VB fragments library.
Fragments are snippets of code you can insert in your procedures.
Each item between braces correspond to a variable of the given type.
They are replaced with the new or existing variables by Fragment Builder in Code-VB tools.

• Automate Access
• Automate Access Using Late Binding
• Automate Excel
• Automate MSProject
• Automate Outlook
• Automate PowerPoint
• Automate Word

Automate Access

Dim acApp As Access.Application
Const ERR_APP_NOTRUNNING As Long = 429

On Error Resume Next
' Attempt to reference running instance of Access.
Set acApp = GetObject(, "Access.Application")
' If Access isn't running, create a new instance.
If Err = ERR_APP_NOTRUNNING Then
    Set acApp = New Access.Application
End If

With acApp
' Code to automate Access here.
    
End With

' If instance of Access was started by this code,
' shut down application.
If Not acApp.UserControl Then
    acApp.Quit
    Set acApp = Nothing
End If

Automate Access Using Late Binding

Dim objApp As Object
Const ERR_APP_NOTFOUND As Long = 429
    
  
On Error Resume Next    
' Attempt to create late-bound instance of Access application.
Set objApp = CreateObject("Access.Application")
If Err = ERR_APP_NOTFOUND Then
    MsgBox "Access isn't installed on this computer. " _
        & "Could not automate Access."
    Exit Sub
End If

With objApp
' Code to automate here.
    
End With

objApp.Quit
Set objApp = Nothing

Automate Excel

Dim xlApp As Excel.Application
Const ERR_APP_NOTRUNNING As Long = 429

' Set {BOOLEAN:bUserControl} to True as default.
{BOOLEAN:bUserControl} = True
On Error Resume Next
' Attempt to open current instance of Excel.
Set xlApp = GetObject(, "Excel.Application")
' If no instance, create new instance.
    If Err = ERR_APP_NOTRUNNING Then
    Set xlApp = New Excel.Application
    ' Store current state of UserControl property.
    {BOOLEAN:bUserControl} = xlApp.UserControl
End If

With xlApp
' Code to automate Excel here.
        
End With

' Check original value of UserControl property.
If {BOOLEAN:bUserControl} = False Then
    xlApp.Quit
    Set xlApp = Nothing
End If

Automate MSProject

Dim pjApp As MSProject.Application    

' Because Microsoft Project is a multi-use COM component, if
' Project is not running, initializing an object variable
' to work with the Application object creates new hidden instance.
' But if Project is already running, initializing the object
' variable returns a reference to the running instance.
Set pjApp = New MSProject.Application
' Show this instance of Project.
pjApp.Visible = True

With pjApp
' Code to automate Microsoft Project here.
        
End With

pjApp.Quit
Set pjApp = Nothing

Automate Outlook

Dim olApp As Outlook.Application

' Because Outlook is a multi-use COM component, if
' Outlook is not running, initializing an object variable
' to work with the Application object creates new hidden instance.
' But if Outlook is already running, initializing the object
' variable returns a reference to the running instance.
Set olApp = New Outlook.Application

With olApp
' Code to automate Outlook here.
	
End With

olApp.Quit
Set olApp = Nothing

Automate PowerPoint

Dim ppApp As PowerPoint.Application

' Because PowerPoint is a multi-use COM component, if
' PowerPoint is not running, initializing an object variable
' to work with the Application object creates new hidden instance.
' But if PowerPoint is already running, initializing the object
' variable returns a reference to the running instance.
Set ppApp = New PowerPoint.Application
' Show this instance of PowerPoint.
ppApp.Visible = True
With ppApp
' Code to automate PowerPoint here.
    
End With

ppApp.Quit
Set ppApp = Nothing

Automate Word

Dim wdApp As Word.Application
    

' Create new hidden instance of Word.
Set wdApp = New Word.Application
' Show this instance of Word.
wdApp.Visible = True
With wdApp
' Code to automate Word here.
    
End With


wdApp.Quit
Set wdApp = Nothing