Helpful Tip For Automation

[JoaoTL]

Hi, maybe this will help anyone who automate with Office.


Function LaunchAppForAutomation(Optional varClass As opgOfficeApp, _
Optional strVersion As String = "9", Optional lngOption As _
opgAutomationInstance = CREATE_NEW, _
Optional strFileName As String = "&quot As Object
' Create or get instance of Office application for Automation.
' You must pass in either varClass or strFileName argument.
' For the lngOption argument, specify whether to create a new
' instance of the application (CREATE_NEW), get an existing
' instance (GET_CURRENT), or create a new instance and open
' specified file (GET_FILE).
' This procedure is late-bound rather than early-bound, because
' if the Automation server is not on the computer, the procedure
' will not compile.

Dim objApp As Object
Dim strClass As String
Dim strMsg As String

On Error GoTo LaunchAppForAutomation_Err

' If neither application's class nor a file name were passed in,
' exit procedure.
If IsMissing(varClass) Then
If Len(strFileName) = 0 Then
Set LaunchAppForAutomation = Nothing
GoTo LaunchAppForAutomation_End
End If
End If

' Determine class name for application to create and
' store in string variable.
Select Case varClass
Case opgOfficeApp.APP_ACCESS
strClass = "Access.Application" & "." & strVersion
Case opgOfficeApp.APP_EXCEL
strClass = "Excel.Application" & "." & strVersion
Case opgOfficeApp.APP_FRONTPAGE
' FrontPage 2000 is version 4.0, not version 9.0
strClass = "FrontPage.Explorer.4.0"
Case opgOfficeApp.APP_OUTLOOK
strClass = "Outlook.Application" & "." & strVersion
Case opgOfficeApp.app_ppt
strClass = "PowerPoint.Application" & "." & strVersion
Case opgOfficeApp.APP_WORD
strClass = "Word.Application" & "." & strVersion
Case Else
Set LaunchAppForAutomation = Nothing
GoTo LaunchAppForAutomation_End
End Select

Select Case lngOption
' Determine whether to create new instance, get
' existing instance, or create new instance and
' open specified file.
Case opgAutomationInstance.CREATE_NEW
Set objApp = CreateObject(strClass)
Case opgAutomationInstance.GET_CURRENT
' If there is no current instance,
' attempt to create new instance silently.
On Error Resume Next
Set objApp = GetObject(, strClass)
If Err = CANT_CREATE_OBJECT Then
On Error GoTo LaunchAppForAutomation_Err
Set objApp = GetObject("", strClass)
End If
Case opgAutomationInstance.GET_FILE
Set objApp = GetObject(strFileName)
Case Else
Set LaunchAppForAutomation = Nothing
GoTo LaunchAppForAutomation_End
End Select

Set LaunchAppForAutomation = objApp

LaunchAppForAutomation_End:
Exit Function

LaunchAppForAutomation_Err:
' Handle Automation errors.
Select Case Err.Number
Case CANT_CREATE_OBJECT
strMsg = "Can't create Automation object. The application " _
& "may not exist on your computer, or you may have " _
& "specified an incorrect version number."
Case FILE_NAME_NOT_FOUND
strMsg = "Can't create Automation object. You may have " _
& "specified a file name that does not exist."
Case Else
strMsg = "Error: " & Err.Number & vbCrLf & Err.Description
End Select
MsgBox strMsg, vbOKOnly + vbCritical, "Error Creating Automation Object"
Set LaunchAppForAutomation = Nothing
Resume LaunchAppForAutomation_End
End Function


Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access