Stories
Slash Boxes
Comments
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)

Beatnik
  (email not shown publicly)
http://www.ldl48.org/

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 [sf.net], 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
DocumentTemplate.Content.Copy
' Paste the fancy header into the newly created document
DocumentRange.Paste
Set ExcelWorksheet = ThisWorkbook.Worksheets.Item(1)
' Assume 100 columns
For i = 1 To 100
  DocumentRange.Collapse Direction:=wdCollapseEnd
  ' Paste the formatted table stuff
  TableTemplate.Content.Copy
  DocumentRange.Paste

  ' In the fancy template, I had some markers..
  ' Replace counter - NUMBERMARKER
  WordDocument.ActiveWindow.Selection.Find.ClearFormatting
  WordDocument.ActiveWindow.Selection.Find.Replacement.ClearFormatting
  With WordDocument.ActiveWindow.Selection.Find
   .Text = "NUMBERMARKER"
   ' 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
DocumentTemplate.Close
TableTemplate.Close
WordApplication.Visible = True
WordApplication.Activate
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.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.