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

r.Copy

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

myWord.Documents.Add

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)

.TypeParagraph

.TypeText vbNewLine

.PasteSpecial

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"

myWord.Quit

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.

Summary

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.