Exporting Excel Data Into A Word Document Using VBA

If you've been working with Excel for a while, sooner or later you'll need to export data into a Word document for a variety of reasons. Perhaps you need to use the data as part of a mail-merge or Word is the preferred application in your workplace.

Instead of using copy and paste, this short code snippet will show you how to export data directly into a Word document.

Opening The Word Application And Exporting The Data

First, you'll need to add the "Microsoft Word 10,0 Object Library" to your project which can be found under tools then references, in the code window.

You might have some simple sales information which looks like this in Excel:

Names Sales

Peter 100

John 120

Mary 130

Maria 102

Jacques 122

Henri 98

Mary 85

We'll write some VBA code which will copy the data, and include the current date in a new Word document.

First, we'll select the data from the Excel worksheet:

Dim r As Range

Set r = ActiveCell.CurrentRegion

r.Copy

Next we'll create the word document and specify the path of the new file.


myFolder = ActiveWorkbook.Path

Dim myWord As New Word.Application

Set myWord = CreateObject("Word.Application")

myWord.Visible = True

myWord.Documents.Add

Now we can copy and paste the Excel data into the new Word file, placing the current date at the top of the file.

With myWord.Selection

.Font.Bold = True

.TypeText "Report for " & FormatDateTime(Now(), vbLongDate)

.TypeParagraph

.TypeText vbNewLine

.PasteSpecial

End With

Application.CutCopyMode = False

Finally, using a decision box the file can be saved and closed.

t = myWord.WordBasic.MsgBox("Save and close Word", vbYesNo)

If t = -1 Then

myWord.ActiveDocument.SaveAs fileName:=myFolder & "\test.doc"

myWord.Quit

End If

Set myWord = Nothing

This produces the following text in the Word document:

Report for Thursday, 8 November 2012

Names Sales
Peter 100
John 120
Mary 130
Maria 102
Jacques 122
Henri 98
Mary 85

If you're creating a fully automated process, you can hide the Word application and close the document once the code has finished without any user interaction.

You can also format the document on the fly. For example, to print the title in italics just add the following code before the type text command.

.Font.Italic = True

You can process other tasks such as sorting the table or filtering the data, directly in Excel or using VBA before exporting the information into Word.

Summary

Using VBA in Excel to interact with other applications is a definite plus in any developer's library. If part of your job involves producing regular data orientated reports, then automating Word from Excel should be an essential part of your skill set.