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:
- Enable the Word Developer menu
- Record a Word macro
- Add the code to Excel VBA and amend
- Record macros in Excel if necessary
- 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.
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
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.
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
We can now view the code for opening a new Word Document in the Visual Basic Editor. Click: Developer -> Visual Basic.
Find the location of your recorded code in the Visual Basic Editor. In this example: Normal -> Modules -> NewMacros.
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.
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:
Would become this code:
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.
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.