Consulting

Results 1 to 4 of 4

Thread: Sleeper: Error: Push Excel Named Range Values to Bookmarks in Word

  1. #1
    VBAX Newbie
    Joined
    Aug 2005
    Posts
    1
    Location

    Sleeper: Error: Push Excel Named Range Values to Bookmarks in Word

    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

  2. #2
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  3. #3
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  4. #4
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by TonyJollans
    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!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •