This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 Creating References to Other Applications in VBA (this blog) Using Tools References to Create a Reference to Word Using VBA to Link Applications 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.

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

Creating References to Other Applications in VBA (this blog)
Using Tools References to Create a Reference to Word
Using VBA to Link Applications
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

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!

Note that you can also learn about linking applications together using VBA on one of our advanced VBA courses.

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?

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:

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:

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:

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.

Parts of this blog
Creating References to Other Applications in VBA (this blog) Using Tools References to Create a Reference to Word Using VBA to Link Applications Using Bookmarks to Fill in a Word Document from Excel

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	Jitender
When:	15 Sep 16 at 15:33

Reply to Jitender

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

From:	Andy B
When:	19 Sep 16 at 13:26

Reply to Andy B

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 = "https://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!

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC