How to Open a Word Document from Excel and Copy Excel Data to the Word Document using VBA

You can open a new word file or document from Excel using VBA. Here in this post I’ll show you an example on how to open a word file from Excel using a Macro. I’ll also show you how to copy Excel data, a table, into the newly created Word document using a Macro.

Open a Word Document from Excel and Copy Excel Data to Word using VBA

Create and Open a New Word Document using Macro

First, let’s create a new word document using a Macro. We can use the CreateObject() method in VBA to create a new instance of a Word document.

Dim oWord As Object       ' DECLARE OBJECT
Set oWord = CreateObject(Class:="Word.Application")		' INITIALIZE THE OBJECT.
oWord.Visible = True      ' OPEN THE WORD FILE. 
Set oWord = Nothing

In the above example, I am just creating new instance of a Word document and making it visible, that is, it will open the word file (or document).

Now, we need to add a blank document to the word file. This is where you write your stuff, add table etc.

oWord.Documents.Add       ' ADD A BLANK DOCUMENT.
oWord.Activate            ' ACTIVATE.

Activating the document, will set focus on the word file. You can skip the .Activate part, Excel will simply create a word file, and you can see the file in your task bar.

So, now you know how to create a new Word document from Excel using VBA. It’s a simple method. Let’s write the entire code in a procedure.

Sub CreateWordFile()
    Dim oWord As Object       ' DECLARE OBJECT
    Set oWord = CreateObject(Class:="Word.Application")   ' INITIALIZE THE OBJECT.
    oWord.Visible = True      ' OPEN THE WORD FILE.
    
    oWord.Documents.Add       ' ADD A BLANK DOCUMENT.
    oWord.Activate            ' ACTIVATE.

    Set oWord = Nothing
End Sub

You can call the above procedure from a button click event, or execute the code when your workbook loads.

Copy Excel Data into a Newly Created Word Document

Now the real code. Did you see the first image in this post? It shows what I am going to do here in this example.

I have range of data in Excel, a table with few columns and rows. I also have a button (an ActiveX control). I wish to create a new word file from my VBA macro and copy the Excel data in the Word file (or document).

I want the data in table format in my word document. Its a very interesting example. You should.

First, create a range of data in Excel. You and add or remove columns or rows from the range. Since, in the example, I am using the UsedRange property to extract data from the worksheet.

Next, add an ActiveX button Control to the sheet.

The Macro

Option Explicit

Private Sub CommandButton1_Click()
    openWordFile
End Sub

Public Sub openWordFile()
    On Error Resume Next
    
    ' FIRST GET THE ROWS COLUMNS OF A USED RANGE.
    
    Dim iTotalRows As Integer   ' GET TOTAL USED RANGE ROWS.
    iTotalRows = Worksheets("sheet1").UsedRange.Rows.Count

    Dim iTotalCols As Integer   ' GET TOTAL COLUMNS.
    iTotalCols = Worksheets("sheet1").UsedRange.Columns.Count

    ' WORD OBJECT.
    Dim oWord As Object
    Set oWord = CreateObject(Class:="Word.Application")
    oWord.Visible = True
    oWord.Activate
    
    ' ADD A DOCUMENT TO THE WORD OBJECT.
    Dim oDoc
    Set oDoc = oWord.Documents.Add

    ' CREATE A RANGE FOR THE TABLE INSIDE WORD DOCUMENT.
    Dim oRange
    Set oRange = oDoc.Range

    ' CREATE AND  DEFINE TABLE STRUCTURE USING
        ' THE ROWS AND COLUMNS EXTRACTED FROM EXCEL USED RANGE.
    oDoc.Tables.Add oRange, iTotalRows, iTotalCols

    ' CREATE A TABLE OBJECT.
    Dim oTable
    Set oTable = oDoc.Tables(1)
    oTable.Borders.Enable = True      ' YES, WE WANT BORDERS.

    Dim iRows, iCols As Integer

    ' LOOP THROUGH EACH ROW AND COLUMN TO EXTRACT DATA IN EXCEL.
    For iRows = 1 To iTotalRows
        For iCols = 1 To iTotalCols
            Dim txt As Variant
            txt = Worksheets("Sheet1").Cells(iRows, iCols)
            oTable.cell(iRows, iCols).Range.Text = txt        ' COPY (OR WRITE) DATA TO THE TABLE.

            ' BOLD HEADERS.
            If Val(iRows) = 1 Then
                objTable.cell(iRows, iCols).Range.Font.Bold = True
            End If
        Next iCols
    Next iRows
    
    Set oWord = Nothing
End Sub

Open an Existing Word Document from Excel using VBA

You can open an existing DOC file (a word document), using the above methods. Instead of using documents.Add() method, you’ll have to use documents.Open() method. Simply tell the method, which file to open (give it a filename).

Public Sub openExistingWordFile()
    Dim oWord As Object           
    Set oWord = CreateObject(Class:="Word.Application")
    oWord.Visible = True

    oWord.documents.Open fileName:="E:\vba.docx"      ' OPEN AN EXISTING FILE.
    oWord.Activate

    Set oWord = Nothing
End Sub

← PreviousNext →