564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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
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.
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.
You can link to the following applications using VBA (among many, many others):
Microsoft Word has unpredictable VBA, but its saving grace is that you can record macros in Word, just like in Excel.
You can program presentations and slides in PowerPoint VBA, but from version 2007 onwards you can no longer record macros.
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 Designer supports VBA (although as for PowerPoint and Access, you can't record macros).
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).
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|
|When:||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?
|When:||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:
'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.Visible = True
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:
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)
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 object
Does SnagIt support the component object model
Hope this all helps!
25 Aytoun Street