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,
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.
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.
How do I identify an open Word file from excel?
That is The Word file is already open when the excel macro is initiated.
do you have any tutotiral on how to make it other way round? doc to xls?
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