Exporting Excel Data Into A Word Document Using VBA

If you’ve got been working with Excel for some time, eventually you will have to export knowledge right into a Word doc for quite a lot of causes. Perhaps you might want to use the info as a part of a mail-merge or Word is the popular software in your office.

Instead of utilizing copy and paste, this brief code snippet will present you learn how to export knowledge immediately right into a Word doc.

Opening The Word Application And Exporting The Data

First, you will want so as to add the “Microsoft Word 10,zero Object Library” to your undertaking which might be discovered beneath instruments then references, within the code window.

You may need some easy gross sales info which seems like this in Excel:

Names Sales

Peter one hundred
John one hundred twenty
Mary one hundred thirty
Maria 102
Jacques 122
Henri ninety eight
Mary eighty five

We’ll write some VBA code which can copy the info, and embrace the present date in a brand new Word doc.

First, we’ll choose the info from the Excel worksheet:

Dim r As Range

Set r = ActiveCell.CurrentRegion

Next we’ll create the phrase doc and specify the trail of the brand new file.

myFolder = ActiveWorkbook.Path
Dim myWord As New Word.Application
Set myWord = CreateObject("Word.Application")
myWord.Visible = True

Now we will copy and paste the Excel knowledge into the brand new Word file, putting the present date on the prime of the file.

With myWord.Selection

.Font.Bold = True
.TypeText "Report for " & FormatDateTime(Now(), vbLongDate)
.TypeText vbNewLine
End With
Application.CutCopyMode = False

Finally, utilizing a choice field the file could be saved and closed.

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

If t = -M Then
myWord.ActiveDocument.SaveAs fileName:=myFolder & "check.doc"
End If

Set myWord = Nothing

This produces the next textual content within the Word doc:

Report for Thursday, H November 2012

Names Sales
Peter one hundred
John one hundred twenty
Mary one hundred thirty
Maria 102
Jacques 122
Henri ninety eight
Mary eighty five

If you are creating a totally automated course of, you possibly can disguise the Word software and shut the doc as soon as the code has completed with none consumer interplay.

You can even format the doc on the fly. For instance, to print the title in italics simply add the next code earlier than the sort textual content command.

.Font.Italic = True

You can course of different duties comparable to sorting the desk or filtering the info, immediately in Excel or utilizing VBA earlier than exporting the knowledge into Word.


Using VBA in Excel to work together with different purposes is a particular plus in any developer’s library. If a part of your job includes producing common knowledge oriented stories, then automating Word from Excel ought to be an important a part of your talent set.

Content Management SystemDynamic WebsitesHTMLHTML 5ITJQuery

html snippetSEO tipsweb development tipswordpress tipsworodpress snippets

Leave a Reply

Your email address will not be published. Required fields are marked *