Control Word from Excel using VBA in Microsoft Excel 2010

Many a times, you have to copy data from an excel file to a word document.  You can do this with a macro very easily. The macro will open a new / existing word doc, copy the contents and then save and close the word doc. Lets see how this is done.

The code which you can use is –

Option Explicit

Sub CreateNewWordDoc()
Dim i As Integer
Dim wrdApp As Object, wrdDoc As Object
Set wrdApp = CreateObject(“Word.Application”)
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add
With wrdDoc
For i = 1 to 100
.Content.InsertAfter “Here is an example test line #” & i
.Content.InsertParagraphAfter
Next i
If Dir(“B:\Test\MyNewWordDoc.docx”) <> “” Then Kill “B:\Test\MyNewWordDoc.docx”
.SaveAs (“B:\Test\MyNewWordDoc.docx”)
.Close
End With
wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

To copy the above code to your file,

  • Press Alt + F11 on the keyboard
  • On the left hand side, you will see Microsoft Excel Objects
  • Right click and select Insert
  • Then click on Module
  • Copy the code to the code window on the right

Now lets see how this code works –

First we declare the variables we need –i as integer to increment each row as its being populated from the excel file to the word doc. Then the 2 object variables wrdApp and wrdDoc, wrdApp is the Word Application object and wrdDoc is the Word Document object.

Set wrdApp = CreateObject(“Word.Application”)

If Word is already running in your system,CreateObject will create a new instance of Word. So this line assigns the object variable wrdApp to the Word Application which you can use later in the code.

wrdApp.Visible = True

The newly created instance of Word will not be visible when its being created. To make it visible, you need to set wrdApp.Visible = True so that its visible.

Set wrdDoc = wrdApp.Documents.Add

We created the new instance of the word application but we haven’t opened a blank word document yet.  So this command will open a new word document. The object wrdDoc has been assigned to this new document so we can use it later in the code.

Incase you do not want to open a new workbook but open an existing workbook, then you can replace this line with

Set wrdDoc = wrdApp.Documents.Open(“B:\My Documents\WordDocs\Doc1.docx”)

This line opens an existing word document which has been saved at the location specified.

With wrdDoc….End With

This is our “With” loop which will work entirely with the wrdDoc object. Once you open this loop, you do not have to repeat the text “wrdDoc” again in this loop. You can directly start with the dot (“.”) before any objects relating to wrdDoc. This loop ends with the End With statement. Once the End With statement has been input, you cannot refer to the objects after wrdDoc with just the “.”.

For i = 1 to 100
.Content.InsertAfter “Here is a example test line #” &i
.Content.InsertParagraphAfter
Next i

This is the “For” loop. It increments from 1 to 100 and at each increment, it inserts a line with text “Here is a example test line #” and then adds the increment number. Then it inserts a line break / paragraph break so that the next increment appears on the next line, like a new paragraph.

So this process will be repeated 100 times, since the for loop states For i = 1 to 100.  This is the output you will get –

 

img2

 

If Dir(“B:\Test\MyNewWordDoc.Docx”) <> “” Then Kill “B:\Test\MyNewWordDoc.docx”

  .SaveAs (“B:\Test\MyNewWordDoc.docx”)

 .Close

Once the “For” loop is complete, the code will check if the file MyNewWordDoc.docx exists. If it does, it will delete it. And then it will save the new file in this same name and close the file.

wrdApp.Quit

Set wrdDoc = Nothing
Set wrdApp=Nothing

The word application is closed and then the 2 objects which were created will be released or set to “Nothing”, so that the memory occupied by these objects is released. This is the entire code which takes care of copying contents from excel to word.

 

image 48

Users are saying about us...

  1. You are great. My knowledge of VBA is much less. I was looking for some code that reads some data from a Word file to put it into Excel.

    But it’s difficult. My needs are not so big to use such a code. Better to use the method: copy-paste, in my case because its only a simple address (2 or 3 lines). I can do it with Ctrl-C – Ctrl-V.

    Best Regards. Mirek from Poland

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube