This post may contain affiliate links. Please read my disclosure for more info:

Controlling Word from Excel using VBA

Contolling Word form Excel using VBA

Contolling Word form Excel using VBA

This post is the second in a series about controlling other applications from Excel using VBA. In the first part we looked at the basics of how to reference other applications using Early Binding or Late Binding.  In this post, we will look at how we can control Word from Excel even though we don’t know any VBA code for Word . . .  yet.  The process we will use for this is as follows:

  1. Enable the Word Developer menu
  2. Record a Word macro
  3. Add the code to Excel VBA and amend
  4. Record macros in Excel if necessary
  5. Repeat the previous steps until macro complete

I am not an Excel VBA expert (I’m more of an Excel VBA tinkerer), and I am certainly not a Word VBA expert.  The process I am about to show you may not create the most efficient code, but I know this process works, because I have used it myself to automate lots tasks using Microsoft Word.

 

Enable the Word Developer menu

If you have enabled the Excel Developer menu it is the same process in Word.

In Word: File -> Options -> Customize Ribbon

Then tick the Developer Ribbon option, OK.

Enable Word Developer Tab

 

Record a Word Macro

The key to the success of this method is taking small sections of code and building up a complex macro bit by bit.  Using the Word Macro Recorder is again, similar to the Excel Macro recorder.

Click on: Developer -> Record Macro

Word VBA Record Macro

For the example in this post, we will create a macro which will open a new Word document, then copy a chart from Excel and paste it into that Word document.  We will tackle this one stage at a time.  Firstly, lets create the macro to open a new word document.

Click – Developer -> Record Macro.  The Record Macro window will open.

Word Record Macro Window

Make a note of the “Store macro in” option, as we will need to know where to find the recorded code later.  Normal.dotm is fine for now.  Click OK – the Macro Recorder is now running.

Open a new Word Document – File -> New -> Blank Document

Stop the Macro from recording – Developer -> Stop Recording

Word VBA Stop Recording

We can now view the code for opening a new Word Document in the Visual Basic Editor. Click: Developer -> Visual Basic.

Word Visual Basic Editor

Find the location of your recorded code in the Visual Basic Editor. In this example: Normal -> Modules -> NewMacros.

Word Visual Basic Editor Open Document

Your code should look like the following.  It may be slightly different, but not significantly.

Sub Macro1()
'
' Macro1 Macro
'
'
    Documents.Add Template:="Normal", NewTemplate:=False, DocumentType:=0
    Windows("Document1").Activate
    Windows("Document2").Activate
End Sub

 

Add the code to Excel VBA and amend

Let’s head back to the Excel VBA Editor and use the Early Binding method to control to Microsoft Word.  In the Visual Basic Editor click Tools -> References select Microsoft Word x.xx Object Library.  Then click OK.

VBA Word Object Library

As we are using Early Binding we need to declare the Application as a variable as follows:

Dim WordApp As Word.Application
Set WordApp = New Word.Application

Now copy and paste the code from the Word VBA Editor into the Excel VBA Editor.

The Word VBA code started with Documents.Add, all we have to do is add our application variable to the front of that line of code. Now becomes WordApp.Documents.Add . . .

Often, Selecting and Activating Objects is not required in VBA code, so I have not copied those statements into the code below.

Sub CreateWordDocument()

'Connect using Early Binding.
'Remember to set the reference to the Word Object Library
'In VBE Editor Tools -> References -> Microsoft Word x.xx Object Library
Dim WordApp As Word.Application
Set WordApp = New Word.Application

WordApp.Documents.Add Template:="Normal", NewTemplate:=False, DocumentType:=0
WordApp.Visible = True 'New Apps will be hidden by default, so make visible

Set WordApp = Nothing 'release the memory

End Sub

A point to note, when an application is opened with VBA, it is normally opened in the background.  To make the Word document visible I have added the following code:

WordApp.Visible = True

 

Record macros in Excel (if necessary)

If we want to copy Excel content into a Word document, we will need to copy that content using Excel VBA.  We can use the Macro Recorder in Excel to obtain the VBA code for copying, then we can use the Word Macro Recorder to obtain the VBA code for pasting.

Macro Recording from Excel – selecting a worksheet and copying chart

Sheets("Sheet1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Copy

Macro Recording from Word – pasting a chart into a document

Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
        Placement:=wdInLine, DisplayAsIcon:=False

We can add both Macro recordings into our Excel macro.  Remember to add WordApp. at the start of each statement of Word VBA code.

Sub CreateWordDocument()

'Connect using Early Binding.
'Remember to set the reference to the Word Object Library
'In VBE Editor Tools -> References -> Microsoft Word x.xx Object Library
Dim WordApp As Word.Application
Set WordApp = New Word.Application

WordApp.Documents.Add Template:="Normal", NewTemplate:=False, DocumentType:=0
WordApp.Visible = True 'New Apps will be hidden by default, so make visible

'code copied from Excel Macro recorder
Sheets("Sheet1").Select
Selection.ChartObjects("Chart 1").ChartArea.Copy

'code copied from Word Macro recorder with WordApp. added to the front.
WordApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
        Placement:=wdInLine, DisplayAsIcon:=False

Set WordApp = Nothing 'release the memory 

End Sub

This code is not particularly efficient; it contains a few unnecessary sections code.  However . . .  it works!

 

Repeat the previous steps until macro complete

By repeating the same steps above; recording short actions, then transferring the code into Excel, we can slowly build up much more complex Macros.  The key is to keep the actions short, if you do too many actions with the Macro Recorder, code starts to look long and scary.

If you’ve you tried to use the Macro Recorder before you will know that this is not as easy as it seems.  And this simple tutorial may make you think it is easy, when it’s not.  Sometimes, it can be quite frustrating trying to find out where the issues and errors are.  The key to success is recording very short actions, such as those below and copying them into the Visual Basic Editor.

'Pressing the Enter Key to move to a new line in Word
WordApp.Selection.TypeParagraph

'Turn on/off Bold Text
WordApp.Selection.Font.Bold = wdToggle

'Change Font Size
WordApp.Selection.Font.Size = 16

'Type some text
WordApp.Selection.TypeText Text:="Here is some text"

You will soon build up a standard library of code that you can use to control Word for most basic tasks.

In recorded VBA code from Word, the word “Selection” in the code often refers to the document itself.  It is possible to make the code a little bit more efficient by declaring the document as a variable.  If we were opening a specific document, we could include this at the start, just below the declaration of the application.

'Declare a specific document as a variable
Dim WordDocument As Object
Set WordDocument = WordApp.Documents.Open(sourceFileName)

Or, if we created a new document we could include the following below the declaration of the application variable.

'Delcare a new document as a variable
Dim WordDocument As Object
Set WordDocument = WordApp.Documents.Add Template:="Normal", _
NewTemplate:=False, DocumentType:=0

If we have created the document as a variable we can then reference the specific document.  This code:

WordApp.Selection.TypeParagraph

Would become this code:

WordDocument.TypeParagraph

Or this code:

WordApp.Selection.TypeText Text:="Here is some text"

Would become this code:

WordDocument.TypeText Text:="Here is some text"

This method is much better, as it doesn’t rely on the Selection of the user being in the right place.

 

Conclusion

We have seen in this post that it is possible to create complex Macros to control Word from within Excel using VBA.  By understanding how to declare variables for the application and documents we can create much more robust macros, even without knowing a lot of VBA code.

2 thoughts on “Controlling Word from Excel using VBA

  1. Mark, this is an excellent way to learn. In fact, I built my freelancing career out of recording, then optimizing, complex Word and Excel macros!

    This is out of scope for most folks, but the REAL power comes when you leverage these automation skills with VB.Net or C. In the old days, I used Visual Basic 6 – not even .Net! – and was able to create some powerful utilities, reusable code libraries and other cool stuff.

    I’m going off to read the other posts in this series. It’s fun to see the work of a kindred spirit!

    Cheers,

    Mitch

    • Excel Off The Grid says:

      Hi Mitch,

      So many ‘advanced’ Excel courses teach how to record a Macro, but not the small amount of knowledge of how to build up the recorded code into something useful. I like that you managed to start a freelancing career on this basis, it just goes to show what a great tool the Macro Recorder is (when used well).

      Some of us, me included, can only dream of using VB.NET and C. But you’re right, the automation capabilities are astounding.

Leave a Reply

Your email address will not be published. Required fields are marked *