Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Beatnik (493)

  (email not shown publicly)

A 29 year old belgian who likes Mountain Dew, Girl Scout Cookies, Tim Hortons French Vanilla Flavoured Cappucinno, Belgian beer, Belgian chocolate, Belgian women, Magners Cider, chocolate chipped cookies and Perl. Likes snowboarding, snorkling, sailing and silence. Bach can really cheer him up! He still misses his dog.

Project Daddy of Spine [], a mod_perl based CMS.

In his superhero time (8.30 AM to 5.30 PM), he works on world peace.

Journal of Beatnik (493)

Tuesday March 18, 2008
02:49 PM

VBA: Generating Word files from Excel data

[ #35931 ]
I had data in an Excel file that I wanted to get into Word in a very nice layout, with some fancy introduction text. I came up with the following VBA code:

Public Sub GenDoc()
Dim WordDocument As Word.Document
Dim TableTemplate As Word.Document
Dim DocumentTemplate As Word.Document
Dim WordApplication As Word.Application
Dim DocumentRange As Word.Range
Dim ExcelWorksheet As Excel.Worksheet

Dim i As Integer
Set WordApplication = CreateObject("Word.Application")

' Template for fancy introduction
' Open as read-only
Set DocumentTemplate = WordApplication.Documents.Open("template1.doc", False, True)

' Template for fancy layout per Excel row
' Open as read-only
Set TableTemplate = WordApplication.Documents.Open("template2.doc", False, True)

WordApplication.Visible = False
Set WordDocument = WordApplication.Documents.Add
Set DocumentRange = WordDocument.Content

' This bit apparently guarantees that pasted blocks are appended, not overwriting the selected block
DocumentRange.Collapse Direction:=wdCollapseEnd
' Paste the fancy header into the newly created document
Set ExcelWorksheet = ThisWorkbook.Worksheets.Item(1)
' Assume 100 columns
For i = 1 To 100
  DocumentRange.Collapse Direction:=wdCollapseEnd
  ' Paste the formatted table stuff

  ' In the fancy template, I had some markers..
  ' Replace counter - NUMBERMARKER
  With WordDocument.ActiveWindow.Selection.Find
   ' Replace NUMBERMARKER with i
   .Replacement.Text = i
   .Wrap = wdFindStop
   .Format = False
   .MatchWholeWord = True
   .MatchSoundsLike = False
   .MatchAllWordForms = False
  End With
  WordDocument.ActiveWindow.Selection.Find.Execute Replace:=wdReplaceAll
Next i

' Wrap it up
WordApplication.Visible = True
End Sub

I also had some fancy progress bar in there somewhere.. I'm getting quite good at this drive-by VBA coding.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.