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