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 –

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 –

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.