Control Word from Excel

The two example macros below demonstrates how you can send information to Word (e.g. creating a new document) and how you can retrieve information from Word (e.g. reading information from a document).

Note! Read and edit the example code before you try to execute it in your own project!

Sub CreateNewWordDoc()
' to test this code, paste it into an Excel module
' add a reference to the Word-library
' create a new folder named C:\Foldername or edit the filnames in the code
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim i As Integer
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Add ' create a new document
    ' or
    'Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\Filename.doc") 
    ' open an existing document
    ' example word operations
    With wrdDoc
        For i = 1 To 100
            .Content.InsertAfter "Here is a example test line #" & i
            .Content.InsertParagraphAfter
        Next i
        If Dir("C:\Foldername\MyNewWordDoc.doc") <> "" Then
            Kill "C:\Foldername\MyNewWordDoc.doc"
        End If
        .SaveAs ("C:\Foldername\MyNewWordDoc.doc")
        .Close ' close the document
    End With
    wrdApp.Quit ' close the Word application
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub


Sub OpenAndReadWordDoc()
' assumes that the previous procedure has been executed
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim tString As String, tRange As Word.Range
Dim p As Long, r As Long
    Workbooks.Add ' create a new workbook
    With Range("A1")
        .Formula = "Word Document Contents:"
        .Font.Bold = True
        .Font.Size = 14
        .Offset(1, 0).Select
    End With
    r = 3 ' startrow for the copied text from the Word document
    Set wrdApp = CreateObject("Word.Application")
    'wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\MyNewWordDoc.doc")
    ' example word operations
    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) 
            ' exclude the paragraph-mark
            ' check if the text has the content you want
            If InStr(1, tString, "1") > 0 Then 
                ' fill into active worksheet
                ActiveSheet.Range("A" & r).Formula = tString
                r = r + 1
            End If
        Next p
        .Close ' close the document
    End With
    wrdApp.Quit ' close the Word application
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
    ActiveWorkbook.Saved = True
End Sub

Document last updated 2000-04-12 12:49:32 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-12-07 23:45:13 CET):

Re: adding a new doc from a template
This is the correct syntax when you are not assigning the resulting document object to a variable:
Documents.Add Template:= "template name"

This is the correct syntax when you are assigning the resulting document objet to a variable:
objNewDoc = Documents.Add(Template:="template name")

DiGiMac from Lake Garda, Italy wrote (2006-12-07 17:17:09 CET):

adding a new doc from a template
Hi Ole,
I'm really having fun with your help to get Word and Excel to talk.
With the example here I'm just having difficulty openeing a new doc based on a template. The usual syntax would be
Documents.Add Template:= "template name"
But this code via Excel doesn't seem to like the space after Add
It thinks the command should stop there.
Do you know of a way around this ?

Ole P. from Norway wrote (2005-04-01 07:54:20 CET):

Re: Error conde on Dim statement
Read the article about OLE basics, this will explain that you have to set a reference to the "foreign" object library you want to use.

Stewart King from NC wrote (2005-03-31 17:11:02 CET):

Error conde on Dim statement
Dim Wdapp As word.Application gives an error code of: "User defined type not defined"
Cannot seem to get over this.. any suggs?

Rick wrote (2005-02-04 17:02:23 CET):

what about the images?????
Hey guys!

I'm tying to make some kind of word viewer, I already know how to append text from the .doc source to the RichTextBox control, but how in heaven can I add the images pasted in my .doc source file??

thekn you so much!

Ole P. from Norway wrote (2004-12-13 11:44:03 CET):

Re: add a reference to the Word-library by VBA program
This should not be necessary.
Check this link to learn about early and late object binding.

Rob Fontaine from The Netherlands wrote (2004-12-13 09:20:30 CET):

add a reference to the Word-library by VBA program
Hi,

How can i add a reference to the Word-library by VBA program. Because on forehand i don't no which office version is in use on the target computer.
When i send an update of my VBA script i find it user unfrendly to let de user selcet the right library item.

Thank you in advance

Ole P. from Norway wrote (2004-11-05 14:21:05 CET):

Re: expand imported information into more cells
You can do this using something like the example procedure below like this from another macro:

CopyDocParagraphs objWordDoc, ThisWorkbook.Worksheets(1)

Sub CopyDocParagraphs(objDoc As Word.Document, wsTarget As Worksheet)
Dim p As Paragraph, varrItems As Variant, r As Long, c As Long
    If objDoc Is Nothing Then Exit Sub
    If wsTarget Is Nothing Then Exit Sub
    For Each p In ThisDocument.Content.Paragraphs
        If Len(p.Range.Text) > 0 Then
            r = r + 1 ' next row number
            ' wsTarget.Cells(r,1).Formula = p.Range.Text ' insert paragraph into worksheet
            ' split the paragraph content into multiple columns using space as the separator character, this is optional
            varrItems = Split(p.Range.Text, " ", -1, vbBinaryCompare)
            For c = 0 To UBound(varrItems)
                wsTarget.Cells(r, c).Formula = varrItems(c)
            Next c
        End If
    Next p
    Set p = Nothing
    Erase varrItems
End Sub

ande from new hamshire wrote (2004-11-05 13:44:28 CET):

Is it possible to expand imported information into more cells (ex. paragrapghs split into A1,B1,C1)?
I need to import a word document into excel that splits the word doc. into seperate cells (i.e. the .doc sheet breaks down as follows:
23145 Filet Beef 23 45 61 12 34

There are nine rows total, how do i get a macro to spit this? i know how to import sheet, but not split.

Thank you in advance

Ole P. from Norway wrote (2004-09-23 23:10:57 CET):

Re: What about an entire sheet with headers and footers?
You can transfer header and footer information from Excel to Word.
Use the macro recorder to create a code example on how to edit the headers/footers in Word and Excel.
You can copy a picture of a worksheet range into Word if you want to transfer a exact copy of what you see in Excel.