Control Excel from Word using VBA in Microsoft Excel 2010

There are times we have to copy data from a word document to an excel file. We can do this very easily with a macro in all version of office. The macro will open an existing / new excel file, copy the contents and then save and close the file. Lets see how this is done.

Option Explicit

Sub OpenAndReadWordDoc()
Dim tString As String
Dim p As Long, r As Long
Dim wrdApp As Object, wrdDoc As Object
Dim wb As Workbook
Dim trange As Variant

Set wb = Workbooks.Add
With wb.Worksheets(1).Range(“A1”)
.Value = “Word Document Contents:”
.Font.Bold = True
.Font.Size = 14
.Offset(1,0).Select
End With

r = 3

Set wrdApp = CreateObject(“Word.Application”)
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open(“B:\Test\MyNewWordDoc.docx”)

With wrdDoc
For p = 1 to .Paragraphs.Count
Set trange = .Range(Start:=.Paragraphs(p).Range.Start, _
End:=.Paragraphs(p).Range.End)
tString = trange.Text
tString = Left(tString, Len(tString)-1)

If Instr(1, tString,”1”) > 0 Then
wb.Worksheets(1).Range(“A” & r).Value = tString
r=r+1
End If

Next p
.Close

End With

wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
wb.Saved = True

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 –tstring as a string to hold text (we will see what text later). 2 variables “p” & “r” as counters. These are of the “long” type. Then we have the 2 object variables wrdApp and wrdDoc. wrdApp is the word application object and wrdDoc is the Word document object. Wb is our workbook variable for the new workbook which is created in the code. If you are opening an existing workbook, you can assign this variable to that instead. The last variable is the trange variant type of variable which will have the contents that need to be transferred from the word doc to the excel file.

Set wb = Workbooks.Add

This assigns the new workbook to the wb variable. If you do not want to add a new workbook but open an existing workbook, then you can alter this line as follows –
Set wb – Workbooks.Open(“B:\Test\File1.xlsx”)

With wb.Worksheets(1).Range(“A1”)
.Value = “Word Document Contents:”
.Font.Bold = True
.Font.Size = 14
.Offset(1,0).Select
End With

The With wb.Worksheets(1).range(“A1”) is a quick way of referencing it. So you do not have to repeat it for each of the code lines between the With and End with statements.

These lines of code put in the text “Word Document Contents:” with a bold font and font size 14 in cell A1 of the 1^st worksheet in the new workbook. And then .Offset(1,0).Select selects the next row.

r = 3

The variable “r” is assigned a value 3 as this is the starting row in the excel file for the data to be copied from the word document.

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.Open("B:\Test\MyNewWordDoc.docx")

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

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 p = 1 to .Paragraphs.Count

This is the “For” loop which will loop from the 1^st to last paragraphs in the word document. The word file which contains the data has 100 lines of information, each stored as a separate paragraph. The loop will increment from 1 to 100 and copy the paragraphs. If certain conditions are set, the copy paste will be based on those conditions.

Set trange = .Range(Start:=.Paragraphs(p).Range.Start, End:=.Paragraphs(p).Range.End)

This assigns the start and end of each paragraph to range as the loop increments.

tString = trange.text
tString = Left(tString,len(tString)-1)

First the text from trange is passed on to TString. Then each paragraph has a paragraph character at the end of the sentence. This is removed using the Left function. From the left side, all characters except the last one is stored in the tString variable.

If Instr(1, tString, “1”) > 0 Then
wb.Worksheets(1).Range(“A” & r).Value = tString
r=r+1
End If

This IF function checks if the text in tString contains the number 1. If it is true, then it copies the contents of tString to the next available row in the workbook. “r” first had a value of 3. Using r=r+1, we increment it by 1, so that the next entry can be placed below the previous entry.

Next p
.Close

The Next p line of code increments to the next paragraph.

.Close closes the document once all the paragraphs have been worked on. This is the output we get in the excel file –

You will see that only those paragraphs which contain number 1 anywhere in the number are included in the output.

wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
wb.Saved = True

wrdApp.Quit will close the word Application. Set wrdDoc = Nothing and Set wrdApp = Nothing will release the memory taken by these 2 object variables and set them to Nothing. wb.Saved = True will save the workbook.

With above code we can control which data to be copied from word file to excel file.

Guest001
May 29, 2014 at 4:18 am

Thank you very much for your VBA examples. I can save a lot of time. I modify your VBA codes as follows:

In the first & second procedure,
Dim xlApp As Excel.Application --> As Object
Dim xlWB As Excel.Workbook --> As Object

In the second procedure, put a '.' in front of the Cells method.

While .Cells(r, 1).Formula ""
tString = .Cells(r, 1).Formula

Now they work fine. Thank you so much.

Reply ↓