|
|
|
|
|
|
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
Set pappWord = CreateObject("Word.Application")
On Error GoTo ErrorHandler
Set docWord = pappWord.Documents.Add(Path)
For Each xlName In wb.Names
If docWord.Bookmarks.Exists(xlName.Name) Then
docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
End If
Next xlName
With pappWord
.Visible = True
.ActiveWindow.WindowState = 0
.Activate
End With
ErrorExit:
Set pappWord = Nothing
Exit Sub
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 305 times.
|
|