Controlling Word from 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

Generate accurate VBA code in seconds with AutoMacro

AutoMacroExample

AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.

Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.

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.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

10 thoughts on “Controlling Word from Excel using VBA

  1. Mitchell Allen says:

    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.

  2. Al Chavez says:

    I really feel that your website is an excellent example of how you specifically teach people how to learn excel from a user friendly perspective.

  3. Guilherme Friedrich Boiko says:

    Thanks for the guidance. By the way, is there any code that saves the Word file in .doc format instead of .docx by Excel?

    • Excel Off The Grid says:

      I’m not sure there are any that focus on this specifically. But you can achieve a lot just by using the Macro recorder or IntelliSense. A solid understanding of VBA is always a good start.

Leave a Reply

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