[FAQ's: OD] How do I automate an Office App using VB 6?-VBForums
Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,152

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

    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?"


    Last edited by RobDog888; Aug 23rd, 2006 at 05:32 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  2. #2

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,152

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

    Outlook 97-2007 And VB 6 Late Binding Automation Code Example:
    VB Code:
    1. Option Explicit
    2. 'Late Binding
    3. 'Declare our app object as a generic unknown Object
    4. Private moApp As Object
    5.  
    6. Private Sub Command1_Click()
    7.     'Declare our late bound object and constant
    8.     Dim oMail As Object
    9.     Const olMailItem As Long = 0
    10.     Const olFormatPlain As Long = 1
    11.    
    12.     'Create a new email item passing our constant to identify a MailItem type object
    13.     Set oMail = moApp.CreateItem(olMailItem)
    14.  
    15.     'Set the new items properties.
    16.     With oMail
    17.         .BodyFormat = olFormatPlain
    18.         .Body = "Blah, blah, blah."
    19.         .Recipients.Add "vbofficeguru@example.com"
    20.         .Recipients.ResolveAll
    21.         .Subject = "Late Bound Automation Example"
    22.         .To = "you@example.com"
    23.         .Save
    24.         .Send 'Send our Spam, err I mean email lol.
    25.     End With
    26.     Set oMail = Nothing
    27. End Sub
    28.  
    29. Private Sub Form_Load()
    30.     'Error trap
    31.     On Error GoTo MyError
    32.     'Create a new instance of Outlook or attach to an already running instance of Outlook.
    33.     'Either one but not both or the last one initialized will win out.
    34.     'Create a new instance of Outlook.
    35.     Set moApp = CreateObject("Outlook.Application")
    36.    
    37.     'Attach to an already running instance of Outlook.
    38.     Set moApp = GetObject(, "Outlook.Application")
    39.     Exit Sub
    40. MyError:
    41.     'Error trap for 429 in case Office is not installed/cant be created
    42.     If Err.Number = 429 Then
    43.         MsgBox "The desired Office application is not installed or can not be created.", vbExclamation+vbOkOnly
    44.     Else
    45.         MsgBox Err.Number & " - " & Err.Description, vbOkOnly+vbExclamation
    46.     End If
    47. End Sub
    48.  
    49. Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    50.     'If we created a new application object then lets quit it and clean up
    51.     moApp.Quit
    52.     Set moApp = Nothing
    53.    
    54.     'If we attached to an already running instance, if we want we can leave it running but still clean up
    55.     Set moApp = Nothing
    56. End Sub
    Last edited by RobDog888; Dec 30th, 2008 at 02:45 AM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  3. #3

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,152

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

    Outlook 97-2007 And VB 6 Early Binding Automation Code Example:
    VB Code:
    1. Option Explicit
    2. 'Early Binding
    3. 'Declare our app object by adding a reference to
    4. 'MS Outlook xx.0 Object Library
    5. Private moApp As Outlook.Application
    6.  
    7. Private Sub Command1_Click()
    8.     'Declare our Early Bound objects
    9.     Dim oMail As Outlook.MailItem
    10.  
    11.     'Create a new email item passing our constant to identify a MailItem type object
    12.     Set oMail = moApp.CreateItem(olMailItem)
    13.    
    14.     'Set the new items properties.
    15.     With oMail
    16.         .BodyFormat = olFormatPlain
    17.         .Body = "Blah, blah, blah."
    18.         .Recipients.Add "vbofficeguru@example.com"
    19.         .Recipients.ResolveAll
    20.         .Subject = "Early Bound Automation Example"
    21.         .To = "you@example.com"
    22.         .Save
    23.         .Send 'Send our Spam, err I mean email lol.
    24.     End With
    25.     Set oMail = Nothing
    26. End Sub
    27.  
    28. Private Sub Form_Load()
    29.     'Error trap in case Office is not installed
    30.     On Error GoTo MyError
    31.     'Create a new instance of Outlook or attach to an already running instance of Outlook.
    32.     'Use only one method or the last one initialized will win out.
    33.     'Create a new instance of Outlook.
    34.     Set moApp = CreateObject("Outlook.Application")
    35.     'Or another method specific to Early Binding...
    36.     Set moApp = New Outlook.Application
    37.    
    38.     'Attach to an already running instance of Outlook.
    39.     Set moApp = GetObject(, "Outlook.Application")
    40.     Exit Sub
    41. MyError:
    42.     If Err.Number = 429 Then
    43.         MsgBox "The desired Office application is not installed or can not be created.", vbOkOnly+vbExclamation
    44.     Else
    45.         MsgBox Err.Number & " - " & Err.Description, vbOkOnly+vbExclamation
    46.     End If
    47. End Sub
    48.  
    49. Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    50.     'If we created a new application object then lets quit it and clean up
    51.     moApp.Quit
    52.     Set moApp = Nothing
    53.    
    54.     'If we attached to an already running instance, if we want we can leave it running but still clean up
    55.     Set moApp = Nothing
    56. End Sub


    Last edited by RobDog888; Dec 30th, 2008 at 02:45 AM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  4. #4

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,152

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

    Excel 2000-2007 And VB 6 Late Binding Automation Code Example:
    vb Code:
    1. Option Explicit
    2. 'Late Binding:
    3. 'Declare our app object
    4. Private moApp As Object
    5.    
    6. Private Sub Command1_Click()
    7.     'Declare our Late Bound object(s)
    8.     Dim oWB As Object
    9.     'Create a new workbook
    10.     Set moWB = moApp.Workbooks.Add
    11.     'Do other stuff to it like populate cells etc.
    12.     '...
    13.     Set oWB = Nothing
    14. End Sub
    15.  
    16. Private Sub Form_Load()
    17.     'Error trap in case Office is not installed
    18.     On Error GoTo MyError
    19.     'Create a new instance of Excel or attach to an already running instance of Excel.
    20.     'Use only one method or the last one initialized will win out.
    21.     'Create a new instance of Excel.
    22.     Set moApp = CreateObject("Excel.Application")
    23.     'Attach to an already running instance of Excel.
    24.     'Set moApp = GetObject(, "Excel.Application")
    25.     Exit Sub
    26. MyError:
    27.     If Err.Number = 429 Then
    28.         MsgBox "The desired Office application is not installed or can not be created.", vbOKOnly + vbExclamation
    29.     Else
    30.         MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
    31.     End If
    32. End Sub
    33.  
    34. Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    35.     If moApp.Workbooks.Count > 0 Then
    36.         moApp.Workbooks.Close
    37.     End If
    38.     moApp.Quit
    39.     Set moApp = Nothing
    40. End Sub
    Last edited by RobDog888; Dec 30th, 2008 at 02:44 AM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  5. #5

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,152

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

    Excel 2000-2007 And Early Binding Automation Code Example:
    vb Code:
    1. Option Explicit
    2. 'Early Binding:
    3. 'Declare our app object by adding a reference to
    4. 'MS Excel xx.0 Object Library
    5. Private moApp As Excel.Application
    6.    
    7. Private Sub Command1_Click()
    8.     'Declare our Early Bound object(s)
    9.     Dim oWB As Excel.Workbook
    10.     'Create a new workbook
    11.     Set moWB = moApp.Workbooks.Add
    12.     'Do other stuff to it like populate cells etc.
    13.     '...
    14.     Set oWB = Nothing
    15. End Sub
    16.  
    17. Private Sub Form_Load()
    18.     'Error trap in case Office is not installed
    19.     On Error GoTo MyError
    20.     'Create a new instance of Excel or attach to an already running instance of Excel.
    21.     'Use only one method or the last one initialized will win out.
    22.     'Create a new instance of Excel.
    23.     Set moApp = CreateObject("Excel.Application")
    24.     'Or (optional) a different method specific to Early Binding...
    25.     'Set moApp = New Excel.Application
    26.     'Attach to an already running instance of Excel.
    27.     'Set moApp = GetObject(, "Excel.Application")
    28.     Exit Sub
    29. MyError:
    30.     If Err.Number = 429 Then
    31.         MsgBox "The desired Office application is not installed or can not be created.", vbOKOnly + vbExclamation
    32.     Else
    33.         MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
    34.     End If
    35. End Sub
    36.  
    37. Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    38.     If moApp.Workbooks.Count > 0 Then
    39.         moApp.Workbooks.Close
    40.     End If
    41.     moApp.Quit
    42.     Set moApp = Nothing
    43. End Sub
    Last edited by RobDog888; Dec 30th, 2008 at 02:44 AM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  6. #6
    Fanatic Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    669

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

    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.