ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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.

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address