[FAQ's: OD] How do I automate an Office App using VB 6?

[RobDog888]

You can automate just about any of the Office suite application. Office OneNote 2003 is one app that has limited programmatical support. Only two functions are exposed.

Excel, Word and Outlook are the Office Apps that get all the attention because of their vast ability to be customized and automated, not to mention that they themselves are widely used and popular.


Office Automation involves using COM (Microsoft_® OLE Component Object Model).

There are several aspects to be concerned with when performing automation of Office applications:

• Early vs Late Binding
• The Office Application Version(s)
• Security
• Distribution

If your planning on only supporting one version of the Office application your automating then you dont have to worry about other versions so Early Binding would be good. Also, you get the Intelisense popups with Early Binding and not with Late. So Late Binding is best if your going to be supporting multiple versions of Office as the referene needs to be dynamic and attach when created in your program. Late Binding also requires you to either use the actual value of all constants or define them yourself with the appropriate values.

If your not going to be distributing the Program or Add-In then your reducing the scope of Operating System versions and Office versions that you would need to program support. This makes like allot easier but if you ever upgrade your Office version then you will need to recompile your program or Add-In after you have updated the references to your new version.

Security is another aspect to consider when choosing the type of program architecture your going to use. If its just a small VB 6 program automating and Office app, VBA IDE project code, or an all out Add-In. Each type has its pros and cons. VBA project code is easiest but has the limit of VBA controls. Sure you can add the use of powerful API calls in it but its the least secure when it comes to users prying eyes messing with the code or allowing someone to modify it etc. It does provide the easiest to program in since your in the native environment of the Office programs development environment. A VB 6 program can offer more flexibility as many programmers are more comfortable writting in the language and IDE that they most often use. Plus, they are more powerful and more secure as they keep the code from any access or modification. They do preset security issues between the Office Application and the VB 6 program as the Office Applications have some built in security and you are also performing cross process marshalling. For the most power and security you would want to write a COM Add-In using VB 6. You can write on some of the Office apps an Add-In using the VBA IDE to create it but it still falls a bit short from COM. With a COM Add-In you get an easier way to distribute the program, more security for your code, and security between Applications.


For more on VB 6 Add-Ins please see my other FAQ thread - "How do I use VB 6 to make an Add-In for an Office application?"

[RobDog888]

Outlook 97-2007 And VB 6 Late Binding Automation Code Example:

Option Explicit
'Late Binding
'Declare our app object as a generic unknown Object
Private moApp As Object
 
Private Sub Command1_Click()
    'Declare our late bound object and constant
    Dim oMail As Object
    Const olMailItem As Long = 0
    Const olFormatPlain As Long = 1
    
    'Create a new email item passing our constant to identify a MailItem type object
    Set oMail = moApp.CreateItem(olMailItem)
 
    'Set the new items properties.
    With oMail
        .BodyFormat = olFormatPlain
        .Body = "Blah, blah, blah."
        .Recipients.Add "vbofficeguru@example.com"
        .Recipients.ResolveAll
        .Subject = "Late Bound Automation Example"
        .To = "you@example.com"
        .Save
        .Send 'Send our Spam, err I mean email lol.
    End With
    Set oMail = Nothing
End Sub
 
Private Sub Form_Load()
    'Error trap
    On Error GoTo MyError
    'Create a new instance of Outlook or attach to an already running instance of Outlook.
    'Either one but not both or the last one initialized will win out.
    'Create a new instance of Outlook.
    Set moApp = CreateObject("Outlook.Application")
    
    'Attach to an already running instance of Outlook.
    Set moApp = GetObject(, "Outlook.Application")
    Exit Sub
MyError:
    'Error trap for 429 in case Office is not installed/cant be created
    If Err.Number = 429 Then
        MsgBox "The desired Office application is not installed or can not be created.", vbExclamation+vbOkOnly
    Else
        MsgBox Err.Number & " - " & Err.Description, vbOkOnly+vbExclamation
    End If
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    'If we created a new application object then lets quit it and clean up
    moApp.Quit
    Set moApp = Nothing
    
    'If we attached to an already running instance, if we want we can leave it running but still clean up
    Set moApp = Nothing
End Sub

[RobDog888]

Outlook 97-2007 And VB 6 Early Binding Automation Code Example:

Option Explicit
'Early Binding
'Declare our app object by adding a reference to
'MS Outlook xx.0 Object Library
Private moApp As Outlook.Application
 
Private Sub Command1_Click()
    'Declare our Early Bound objects
    Dim oMail As Outlook.MailItem
 
    'Create a new email item passing our constant to identify a MailItem type object
    Set oMail = moApp.CreateItem(olMailItem)
    
    'Set the new items properties.
    With oMail
        .BodyFormat = olFormatPlain
        .Body = "Blah, blah, blah."
        .Recipients.Add "vbofficeguru@example.com"
        .Recipients.ResolveAll
        .Subject = "Early Bound Automation Example"
        .To = "you@example.com"
        .Save
        .Send 'Send our Spam, err I mean email lol.
    End With
    Set oMail = Nothing
End Sub
 
Private Sub Form_Load()
    'Error trap in case Office is not installed
    On Error GoTo MyError
    'Create a new instance of Outlook or attach to an already running instance of Outlook.
    'Use only one method or the last one initialized will win out.
    'Create a new instance of Outlook.
    Set moApp = CreateObject("Outlook.Application")
    'Or another method specific to Early Binding...
    Set moApp = New Outlook.Application
    
    'Attach to an already running instance of Outlook.
    Set moApp = GetObject(, "Outlook.Application")
    Exit Sub
MyError:
    If Err.Number = 429 Then
        MsgBox "The desired Office application is not installed or can not be created.", vbOkOnly+vbExclamation
    Else
        MsgBox Err.Number & " - " & Err.Description, vbOkOnly+vbExclamation
    End If
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    'If we created a new application object then lets quit it and clean up
    moApp.Quit
    Set moApp = Nothing
    
    'If we attached to an already running instance, if we want we can leave it running but still clean up
    Set moApp = Nothing
End Sub

[RobDog888]

Excel 2000-2007 And VB 6 Late Binding Automation Code Example:

Option Explicit
'Late Binding:
'Declare our app object
Private moApp As Object
    
Private Sub Command1_Click()
    'Declare our Late Bound object(s)
    Dim oWB As Object
    'Create a new workbook
    Set moWB = moApp.Workbooks.Add
    'Do other stuff to it like populate cells etc.
    '...
    Set oWB = Nothing
End Sub
 
Private Sub Form_Load()
    'Error trap in case Office is not installed
    On Error GoTo MyError
    'Create a new instance of Excel or attach to an already running instance of Excel.
    'Use only one method or the last one initialized will win out.
    'Create a new instance of Excel.
    Set moApp = CreateObject("Excel.Application")
    'Attach to an already running instance of Excel.
    'Set moApp = GetObject(, "Excel.Application")
    Exit Sub
MyError:
    If Err.Number = 429 Then
        MsgBox "The desired Office application is not installed or can not be created.", vbOKOnly + vbExclamation
    Else
        MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
    End If
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    If moApp.Workbooks.Count > 0 Then
        moApp.Workbooks.Close
    End If
    moApp.Quit
    Set moApp = Nothing
End Sub

[RobDog888]

Excel 2000-2007 And Early Binding Automation Code Example:

Option Explicit
'Early Binding:
'Declare our app object by adding a reference to
'MS Excel xx.0 Object Library
Private moApp As Excel.Application
    
Private Sub Command1_Click()
    'Declare our Early Bound object(s)
    Dim oWB As Excel.Workbook
    'Create a new workbook
    Set moWB = moApp.Workbooks.Add
    'Do other stuff to it like populate cells etc.
    '...
    Set oWB = Nothing
End Sub
 
Private Sub Form_Load()
    'Error trap in case Office is not installed
    On Error GoTo MyError
    'Create a new instance of Excel or attach to an already running instance of Excel.
    'Use only one method or the last one initialized will win out.
    'Create a new instance of Excel.
    Set moApp = CreateObject("Excel.Application")
    'Or (optional) a different method specific to Early Binding...
    'Set moApp = New Excel.Application
    'Attach to an already running instance of Excel.
    'Set moApp = GetObject(, "Excel.Application")
    Exit Sub
MyError:
    If Err.Number = 429 Then
        MsgBox "The desired Office application is not installed or can not be created.", vbOKOnly + vbExclamation
    Else
        MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
    End If
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    If moApp.Workbooks.Count > 0 Then
        moApp.Workbooks.Close
    End If
    moApp.Quit
    Set moApp = Nothing
End Sub

[gibra]

We can use Early and Late binding 'together'
-Early binding in IDE
-Late binding in EXE

EARLY binding and LATE binding: using together!
http://www.vbforums.com/showthread.php?t=680455