|
|
|
|
|
|
|
|
Multiple Apps
|
Push Excel Named Range Values to Bookmarks in Word
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2003
|
|
Submitted by:
|
Ken Puls
|
|
Description:
|
This macro takes data from named Excel ranges, and pushes their values into a Word document using bookmarks with the same name.
|
|
Discussion:
|
If you use Excel to create calculated data, one record at a time, but like the features of mail merge for a form letter, then you can use this macro to "push" the values from Excel to Word. Just name your ranges in Excel. Then, much like you would a mail merge, place bookmarks (instead of merge fields) into your Word document. You can't see bookmarks by default, and even when you turn viewing bookmarks on, they're still difficult to see. Be careful you don't delete them. Adapted and commented using code originally developed by Suat Ozgur (smozgur).
|
|
Code:
|
instructions for use
|
Option Explicit
Sub BCMerge()
Dim pappWord As Object
Dim docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.Name
Dim TodayDate As String
Dim Path As String
Set wb = ActiveWorkbook
TodayDate = Format(Date, "mmmm d, yyyy")
Path = wb.Path & "\pushmerge.dot"
On Error Goto ErrorHandler
'Create a new Word Session
Set pappWord = CreateObject("Word.Application")
On Error Goto ErrorHandler
'Open document in word
Set docWord = pappWord.Documents.Add(Path)
'Loop through names in the activeworkbook
For Each xlName In wb.Names
'if xlName's name is existing in document then put the value in place of the bookmark
If docWord.Bookmarks.Exists(xlName.Name) Then
docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
End If
Next xlName
'Activate word and display document
With pappWord
.Visible = True
.ActiveWindow.WindowState = 0
.Activate
End With
'Release the Word object to save memory and exit macro
ErrorExit:
Set pappWord = Nothing
Exit Sub
'Error Handling routine
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & "; There is a problem"
If Not pappWord Is Nothing Then
pappWord.Quit False
End If
Resume ErrorExit
End If
End Sub
|
|
How to use:
|
- Name your Excel ranges and Word bookmarks with the same names for each value.
- Save the Word document as a template in the same directory as your Excel file.
- Copy the code above.
- From the Excel workbook, press Alt + F11 to enter the Visual Basic Editor (VBE).
- Right-click desired workbook on left (in bold).
- Choose Insert -> Module and paste code into the right pane.
- Change "pushmerge.dot" in the code to match the name of your template.
- Save the workbook with the SAVE diskette, and exit the VBE.
|
|
Test the code:
|
- Hit Tools-->Macro-->Macros and double-click BCMerge.
|
|
Sample File:
|
PushNamedToBookmark.zip 11.38KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 219 times.
|
|
|