Referencing Word and other applications in Excel VBA
Part one of a four-part series of blogs

Even if you're writing Visual Basic macros within Excel, there's nothing to stop you inserting text into Word documents, manipulating PowerPoint slides or sending Outlook emails. This blog explains how to reference other applications, and write macros in Excel to link to other Microsoft Office software

  1. Creating References to Other Applications in VBA (this blog)
  2. Using Tools References to Create a Reference to Word
  3. Using VBA to Link Applications
  4. Using Bookmarks to Fill in a Word Document from Excel

This blog is part of a complete Excel VBA tutorial. You can also learn to program in VBA on one of our VBA macros courses.

Posted by Andy Brown on 05 December 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating References to Other Applications in VBA

There comes a magical moment in every Excel Visual Basic programmer's life when he - or she - suddenly realises just how wide their horizons are.  Which is a poetic way of saying: VBA isn't limited just to Excel!

Talking to Word (say) from within Excel

Wouldn't it be great if you could (say) copy information from Excel into a Word document programmatically?  However, the following diagram proves this isn't possible - doesn't it?

Trying to create Word reference

If you could create a reference to Microsoft Word from within Excel, it would be included in this list. Wouldn't it?

 

However, it is possible to expand Excel's horizons so that it knows about Word:

Choosing Word from autocompletion

Here we somehow have made available the Word object library within Excel - the rest of this blog shows how to do this, and how to use the resulting reference.

 

Microsoft Applications you can Link to

You can link to the following applications using VBA (among many, many others):

Application Notes
Word Microsoft Word has unpredictable VBA, but its saving grace is that you can record macros in Word, just like in Excel.
PowerPoint You can program presentations and slides in PowerPoint VBA, but from version 2007 onwards you can no longer record macros.
Access I've written a separate blog showing how to add, edit, delete and list records and fields in an Access database from within Excel.
SharePoint SharePoint Designer supports VBA (although as for PowerPoint and Access, you can't record macros).
Outlook You can write macros within Excel to manipulate mail messages and contacts held in Outlook.

This is just a tiny selection of the applications which support VBA (the technical phrase is that they support the Component Object Model).

Our Initial Example

We'll write a macro which takes a set of jugglers (well, why not?) and lists them out in a new Word document:

List of jugglers in Excel

Initially we'll have a list of jugglers in an Excel worksheet.

 

When we run our macro it will create a list of the same jugglers in Word:

List of jugglers in Word

The end result of running the macro we'll write: a list of the jugglers in Word. Clearly we could have done something more exciting with them, like creating a table or chart.

 

And with all that preamble out of the way - let's begin by creating a reference to Word.

 

This blog has 1 thread Add post
15 Sep 16 at 15:33

I have one query regarding add references in VBA. I know that if I want to add/use objects and properties for any application then I add the reference. But I don't understand which library I should use, and how I can find it to get object/properties?. For example if I want to use another application (like VLC media player or Google chrome), which support automation then how do I found the library file for them?

Thank you,

Jitender

19 Sep 16 at 13:26

I don't think there's a single, straightforward answer to this question; you just have to search the Internet for each separate application that you want to use from within VBA.  For example, for VLC, I Googled:

VLC VBA object model

This led me to the fact that I needed to create a reference to the VideoLAN VLC ActiveX Plug-In, using the VBA Tools => References menu.  After adding the reference to this library, I then created this subroutine:

Sub ShowMovie()

    'start up a copy of VLC in memory
    Dim vlc As New VLCPlugin2
    
    'make sure you can see it on screen!
    vlc.Visible = True
    
    'add a video to the playlist, then play it
    vlc.playlist.Add "Interesting video.AVI"
    vlc.playlist.Play
    
    vlc.Visible = True
    
End Sub

Sadly, when I run it it doesn't actually work!  Although it compiles and runs perfectly, no VLC window opens.  I'm not sure what I've missed out, but the principle shown is good.

Chrome is a different kettle of fish (a good English expression last used by anyone else about 50 years ago).  Chrome doesn't support VBA (neither does Firefox, for that matter), so the best you can hope to do is to open up a shell application in a Chrome window.  For example:

Sub RunChrome()

      Dim ChromePath As String
      Dim ChromeUrl As String
        
        'tell VBA to open Chrome
      ChromePath = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"
      ChromeUrl = "http://www.wiseowl.co.uk/"
    
      Shell (ChromePath & " -url " & ChromeUrl)

End Sub

Note that you don't need to create a reference for this, as you're not using an object library. The only way round this that I know is to use a third-party add-in like Selenium, which will allow you to automate browsers from VBA.

Finally, to go back to your original general question, I'd just Google the name of the application and the letters VBA. So if you want to find out whether you can use VBA to automate your favourite screen capture program, you could type in:

SnagIt VBA

Or try:

SnagIt VBA object

Or even:

Does SnagIt support the component object model

Hope this all helps!