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:
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
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
Now we can copy and paste the Excel data into the new Word file, placing the current date at the top of the file.
.Font.Bold = True
.TypeText "Report for " & FormatDateTime(Now(), vbLongDate)
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"
Set myWord = Nothing
This produces the following text in the Word document:
Report for Thursday, 8 November 2012
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.
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.