» Control Word from Excel using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions 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