Solved: Excel data to Word bookmarks

[Paul_Hossler]

Must have been asked before, but does anyone have the skelton code for an Excel 2007 macro to:

1. Start Word
2. Load an existing Word template
3. Put some Excel variables' data into the Word doc's bookmarks
4. Allow the user to save the Word doc?

I have a "fun" project at work to do some calculations in Excel and produce a Word based form doc with the summary of the data in Excel

Thanks

Paul

[lucas]

Paul, I don't honestly know if this will work in 2007 or not. You may have to change the template you want to use in the code to a 2007 template.


Checkmark in column a decides which files to build from the template. Change the save path to suit:
[VBA]Option Explicit
Sub MailList_Create_LateBound()
Dim _
oWord As Object, _
oDoc As Object, _
strFPath As String, _
bolWDCreated As Boolean, _
rngList As Range, _
iCol As Integer, _
rCell As Range
'// Initially set a range as a single cell, in this case, the cell in //
'// the last row in column B, that has a value in it. (B4, "Alicia Down//
'// in the example data) //
Set rngList = Sheet1.Range("B65536").End(xlUp)
'// Then reuse the variable, now setting the range to include from B2, //
'// to the last cell we already found. //
Set rngList = Sheet1.Range("B2", rngList)
'// Create a string variable with the path to this workbook (and hence, //
'// the Word Template). We include the last backslash "\" also, so it //
'// doesn't goober-up on us :-) //
strFPath = ThisWorkbook.Path & Application.PathSeparator
'// Now we temporarily set error handling to "in-line", meaning we'll //
'// handle errors while the code runs. We're doing this so that if an //
'// error is raised trying to 'GetObject' (see the vba Help topic), then//
'// we know that Word wasn't already running and we'll need to create it.//
On Error Resume Next
Set oWord = GetObject(Class:="Word.Application")
'// If an error raised, we'll clear it, and Create a new instance of Word//
If Err.Number > 0 Then
Err.Clear
Set oWord = CreateObject(Class:="Word.Application")
'// If we created Word, then we'll take note of this by setting a //
'// flag/boolean to True. We'll use this later. //
bolWDCreated = True
End If
'// Now we'll reset error handling //
On Error GoTo 0
With oWord
'// In case we Created Word, we'll make sure it's not hidden. //
.Application.Visible = True
'// Now since rngList was the range of cells that had names in them,//
'// we'll stop at each cell and get the values from each appropriate//
'// column. //
For Each rCell In rngList
'// Now for each record, we'll make a new temporary document, //
'// based on your template. //

If rCell.Offset(0, -1).Value = "a" Then 'if checked then print or save it
Set oDoc = .Documents.Add(Template:=strFPath & "tpl.dot")
'// Since you have six (6) different bits of data to retrieve, //
'// we'll use Offset to grab the values, assigning these to the //
'// Text of each range in the document, using the bookmarks to //
'// set this range. //
For iCol = 1 To 6
oDoc.Range(oDoc.Bookmarks("bm_" & iCol).Range.Start, _
oDoc.Bookmarks("bm_" & iCol).Range.End).Text _
= rCell.Offset(0, iCol - 1).Value
Next iCol

'// Now we're back on the outer loop, so we'll print the created//
'// document, and close it w/o saving (ie - throw it out.). //
'To print the docs uncomment the two lines of code below and comment the saveas code below
'No files will be saved to the hard drive
' oDoc.PrintOut
' oDoc.Close SaveChanges:=False
' oDoc.SaveAs ActiveWorkbook.Path & "\" & rCell.Offset(0, 0).Value & ".doc"
oDoc.SaveAs ActiveWorkbook.Path & "\Storage\" & rCell.Offset(0, 0).Value & ".doc"

'// Then move to the next (name) cell in the workboook, til done. //
End If
Next rCell
End With
'// If we Created the instance of Word, then we'll close it. //
If bolWDCreated Then oWord.Quit
'// Explicitly release our objects. //
Set oWord = Nothing
Set oDoc = Nothing
End Sub
[/VBA]
excel file and template attached. Put them in the same directory and run the excel file. Again, change the save path.....

hope this helps

[Paul_Hossler]

Thanks (and I'm glad you like a lot of comments )

I can work with this as a starting point for what I need to do

I did save as 2007 XLSM and DOTX and they seemed to work fine

I'll leave this thread open for awhile in case somebody else has any suggestions

Paul