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
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
“Great Blogpost! You post interesting articles here. Your blog deserves_ much more visitors.”
“Great Blogpost! Way cool! Some extremely valid points! I _appreciate you writing this post and also the rest of the website is also very good.”
“Great Blogpost! great threat, i like it, thanks very_ much.”