PDA

View Full Version : Error: Push Excel Named Range Values to Bookmarks in Word



SnrChris
08-18-2005, 03:19 PM
The code that follows is supposed to automatically push data to a Word template document, but I have been unable to get it to work after carefully following all the instructions. I keep getting an 5151 error. What does this mean? Has anyone successfully run this macro?

from http://www.vbaexpress.com/kb/getarticle.php?kb_id=381
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

Ken Puls
08-18-2005, 11:21 PM
Hi there!

Yes, I've been able to get it to work. ;) Also, just so you know, our approvers due a huge amount of testing before any KB submission is made live on the website.

At any rate, on to the troubleshooting!

I've never encountered the error, but let's check these:
-Are your bookmarks in Word spelled EXACTLY the same as the named ranges in Excel?
-Is your template that you are pushing the data into actually called "Pushmerge.dot" and is it saved in the same directory as your Excel file?

Failing those, when you run the macro and it errors, can you click "Debug" and tell me what line is highlighted?

HTH,

TonyJollans
08-19-2005, 05:30 AM
First, a minor point: You have 2 On Error GoTo ErrorHandler statements - the second one is redundant.

Now, Error 5151 means there is a problem opening a document. As Ken asks, do you have a Word Template called pushmerge.dot in the same directory as your workbook?

Finally, Ken, another minor point: as there is an error handler there is no Debug option.

Ken Puls
08-19-2005, 09:19 AM
Finally, Ken, another minor point: as there is an error handler there is no Debug option.

Well go figure that! I never actually knew that, Tony. You learn something new every day!

Thanks! :thumb