VBA Code to Interact with MS Word

Recently, I had the task of writing a program to populate an SLA (Service Level Agreement) Report. The SLA Report was a Word document with many tables of data and charts, but the input came from nine spreadsheets. Using the methods just detailed, I was able to write VBA code that worked through the individual spreadsheets, extract the relevant data, and place the figures in the correct tables or charts in the Word document. Previously, it had taken someone half a day to do this manually, but the code accomplished it in under five minutes.

The following code will show you how to deal and interact with Word. We can create Word document from scratch or modify the existing document using Excel VBA.

Sub sbWord_FormatingWordDoc()

‘Declarations
Dim oWApp As Word.Application
Dim oWDoc As Word.Document
Dim sText As String
Dim iCntr As Long

Set oWApp = New Word.Application
Set oWDoc = oWApp.Documents.Add() ‘(“C:\Documents\Doc1.dot”) ‘You can specify your template here

‘Adding new Paragraph

Dim para As Paragraph
Set para = oWDoc.Paragraphs.Add

para.Range.Text = “Paragraph 1 – My Heading”
para.Format.Alignment = wdAlignParagraphCenter
para.Range.Font.Size = 18
para.Range.Font.Name = “Cambria”

For i = 0 To 2
Set para = oWDoc.Paragraphs.Add
para.Space2
Next

Set para = oWDoc.Paragraphs.Add
With para
.Range.Text = “Paragraph 2 – Some Text for the next Paragraph”
.Alignment = wdAlignParagraphLeft
.Format.Space15
.Range.Font.Size = 14
.Range.Font.Bold = True
End With

oWDoc.Paragraphs.Add

Set para = oWDoc.Paragraphs.Add
With para
.Range.Text = “Paragraph 3 – This is another Paragraph, you can create number of paragraphs like this and format it”
.Alignment = wdAlignParagraphLeft
.Format.Space15
.Range.Font.Size = 12
.Range.Font.Bold = False
End With

oWApp.Visible = True

End Sub

This is a very simple example of manipulating Word from within Excel using VBA. This can be very useful, for example; you could have a standard document with tables that the macro populated from the spreadsheet data. You can run your macro and the data will be transferred into the document tables.

Check out how to interact Excel with Outlook, Powerpoint, IE and other applications here and learn to automate your processes with ease.

Happy Excelling – www.Excelgoodies.com

4 thoughts on “VBA Code to Interact with MS Word

  1. no credit check loans

    Wow that was strange. I just wrote an incredibly long comment but after I clicked submit my comment didn’t show up. Grrrr… well I’m not writing all that over again. Anyway, just wanted to say great blog

    Reply

Leave a Reply

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