Excel Macro to Auto-Populate a Word Template

[Dan Thorman]

I am using Excel to pull data together from multiple sources, and am trying
to set it up as, essentially, a dashboard for a user. I have it set so that
the user hits a button and all the data that they need is automatically
pulled in to the workbook. However, I have a MS Word form that I would like
the data to be pushed to, and I cannot figure out how to write the macro to
automatically populate that particular form. I would normally just write the
macro so that it created the document from scratch every time, but there is a
LOT of static data in the form, and it changes often.

I am using Excel 2003

Can anyone provide an example of the code for how to do this? I am very new
to VBA programming (and not a programmer by trade), so an example should
allow me to figure out how to make it work in this particular instance.

Thanks in advance for all your help!

 

[Ed]

Dan: If the data is the same data points each time, just different data,
then create a template in Word (a Word doc saved as ".dot") that has all of
your data point locations as bookmarks. In your Excel macro, set an object
to Word and open a new doc based on that template (see
http://word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm). Then iterate
through the document's Bookmarks collection and set your data into each
bookmark, then SaveAs with your doc name.

HTH
Ed

 

[Tim of Calgary]

That's a bit of a poser. Normally I would use the strategy of pulling the
data straight into Word and let the Word Document run the process, since it
is the furthest downstream. However, if Excel is running things I can at
least steer you in the right direction, I hope.

This is designed so just about everything is done by the properties of the
document and only minimal VBA is needed.

1. Set up the Word document as a mail-merge drawing from your spreadsheet
(this works best if your data is pulled to a consistent place in the Excel
Sheet).

2. Create a macro in the Word document (not in Normal.dot) something like
this:

Sub AutoOpen()
ActiveDocument.Fields.Update
End Sub

If you have set up the mailmerge fields properly, this should update
everything to match your data source (i.e. the Excel document). Bob's your
uncle. You may have to create a tab in your Excel document called "datatab"
or something like that to hold the data in a form Word likes.

Now, as to opening the Word document from Excel, I must leave that to the
Cross-Application wizards; I am a single-application geek.

I hope this helps somewhat...

Tim Smith.

 

[Dan Thorman]

This seems like the way to go. So, seeing as how new I am to all of this, I
have been looking for some code examples of how to iterate through the
bookmarks in the template i have created and populate them with cells from
the Excel spreadsheet. Unfortunately, I cannot seem to find any such
examples. Does anybody happen to know of any?

P.S. Thanks for your help Ed!

 

[Keith]

Here is one, you'll have to wade through and use what is relevant to your
needs... this shows both manual population of two bookmarks, and also
populating a series of bookmarks by looping through an array.
HTH
Keith

Sub MakeGuideA()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
Dim BMRange As Word.Range
Dim toc As Word.TableOfContents

wrdApp.Visible = True
wrdApp.DisplayAlerts = wdAlertsNone

Set wrdDoc = wrdApp.Documents.Open(CurrentPath & "Interview_GuideA" &
".doc", , True)

'word operations
With wrdDoc
For deleteComp = 1 To 18
Bkmksize = .Bookmarks.Count

thisVal = Application.WorksheetFunction.Max(WordArray)
thismatch = Application.Match(thisVal, WordArray, 0)
If IsError(thismatch) Then
Exit For
Else
BookMarkName = .Bookmarks(thisVal).Name
If thisVal > 0 Then
Set BMRange = wrdDoc.Bookmarks(BookMarkName).Range
BMRange.Text = ""
.Bookmarks.Add BookMarkName, BMRange
End If
WordArray(thismatch) = ""
End If
Next

BookMarkName = .Bookmarks(2).Name
Set BMRange = wrdDoc.Bookmarks(BookMarkName).Range
BMRange.Text = ApplicantName
.Bookmarks.Add BookMarkName, BMRange

BookMarkName = .Bookmarks(17).Name
Set BMRange = wrdDoc.Bookmarks(BookMarkName).Range
BMRange.Text = PositionString
.Bookmarks.Add BookMarkName, BMRange

For Each toc In wrdDoc.TablesOfContents
toc.Update
Next

wrdDoc.SaveAs (CurrentPath & ApplicantName & ".doc")
.Close ' close the document
End With

wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub