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:

  1. Name your Excel ranges and Word bookmarks with the same names for each value.
  2. Save the Word document as a template in the same directory as your Excel file.
  3. Copy the code above.
  4. From the Excel workbook, press Alt + F11 to enter the Visual Basic Editor (VBE).
  5. Right-click desired workbook on left (in bold).
  6. Choose Insert -> Module and paste code into the right pane.
  7. Change "pushmerge.dot" in the code to match the name of your template.
  8. Save the workbook with the SAVE diskette, and exit the VBE.
 

Test the code:

  1. Hit Tools-->Macro-->Macros and double-click BCMerge.
 

Sample File:

PushNamedToBookmark.zip 11.38KB 

Approved by mdmackillop


This entry has been viewed 305 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express