Using Excel VBA to read and write Microsoft Word documents

These examples are written and tested in Excel 2003.

Create, Write and Close Word Document
Read and Append
 
 

Create, Write and Close Word Document

The following code illustrates the use of Excel VBA to create a Microsoft Word Document and save it to disk. This script will create a document in your My Documents folder.

'In Tools > References, add reference to "Microsoft Word XX.X Object Library" before running.
Option Explicit

Sub CreateNewWordDoc()
    
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Set wdApp = CreateObject("Word.Application")
    Dim myFile As String
    
    'Alternatively, we can use Late Binding
    'Dim wdApp As Object
    'Dim wdDoc As Object
    'Set wdApp = CreateObject("word.Application")
        
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Add
    
    ' define a place to put file
    myFile = Environ("UserProfile") & "\My Documents\" & "MyWordDoc.docx"
    
    ' or like this
    myFile = Environ("UserProfile") & "\Documents\" & "MyWordDoc.docx"

    ' have to wrap name in quotes here because directory contains a space
    If Dir("""&myFile&""") <> "" Then
        ' kill = delete existing file
        Kill """&myFile&"""
    End If

    With wdDoc
        .Content.InsertAfter "Hello World"
        .Content.InsertParagraphAfter
        .SaveAs myFile
        .Close
    End With
    
    wdApp.Quit
    Set wdDoc = Nothing
    Set wdApp = Nothing

End Sub

Some VBA Vocabulary

Option Explicit When Option Explicit On or Option Explicit appears in a file, you must explicitly declare all variables by using the Dim or ReDim statements. If you try to use an undeclared variable name, an error occurs at compile time. The Option Explicit Off statement allows implicit declaration of variables. This statement reduces risk of error by mis-typing variable names.

Word.Application Represents the Microsoft Word application. The Application object includes properties and methods that return top-level objects. To use Automation to control Word from another application, use the Microsoft Visual Basic CreateObject or GetObject function to return a Word Application object.

Word.Document Represents a Word document. The Document object is a member of the Documents collection. The Documents collection contains all the Document objects that are currently open in Word.

InsertAfter Inserts the specified text at the end of a range or selection. After this method is applied, the range or selection expands to include the new text.

Kill Deletes files from a disk.

DirThe Microsoft Excel DIR function returns the first filename that matches the pathname and attributes specified. To retrieve additional filenames that match pathname and attributes, call DIR again with no arguments.

Early and Late Binding

An object is early bound when it is assigned to a variable declared to be of a specific object type. By contrast, an object is late bound when it is assigned to a variable declared to be of type Object. Objects of this type can hold references to any object, but lack many of the advantages of early-bound objects

Early binding: The first step in using early binding is to ensure that the compiler "knows" about the object to which you want to bind. If you are working with a Excel or Word, then the controls in the toolbox are all known, or intrinsic, objects, and you can create references to them without any concern.

Late Binding: With late binding, the code does not make an explicit reference to the type of object when the variable is declared. Instead, it will simply use the "Object" type.

 
 

Read and Append to a Microsoft Word Document

The following code illustrates the use of Excel VBA to read and appaend to a Microsoft Word Document. This script will trad a document in your My Documents folder.

'In Tools > References, add reference to "Microsoft Word XX.X Object Library" before running.
Option Explicit

Sub CreateNewWordDoc()
    
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Set wdApp = CreateObject("Word.Application")
    Dim myFile As String
    
    'Alternatively, we can use Late Binding
    'Dim wdApp As Object
    'Dim wdDoc As Object
    'Set wdApp = CreateObject("word.Application")
    
    myFile = Environ("UserProfile") & "/My Documents/" & "MyWordDoc.docx"
    Set wdDoc = wdApp.Documents.Open(myFile)
    wdApp.Visible = True
    
    With wdDoc
        
        .Content.InsertAfter "Hello World"
        .Content.InsertParagraphAfter

    End With
    
End Sub