Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Excel to Word (XL97 SR2)

    OK, I've searched all the expected forums of Word, VBA and Excel and I can't find a solution for this. I've also searched MSDN and come up with a partial solution but not one fitting my problem, so please forgive me if this has been asked before:-
    can anyone please help with copying data from excel specified cell addresses into predefined bookmarks within Word 97. The MSDN solution seemed to copy consecutive cells into consecutive bookmarks, but I want to copy specific addressed cells into specific named bookmarks.
    I got partway with the Word doc (i.e. opening up from specified filepathname) but the
    BMRange = ActiveDocument.Bookmarks("MyBookmark").Range bombed out every time with object error.
    I had thought of copying the data to a new workbook - closing the new workbook and then opening a predefined merge document, but this seems a little less 'slick' than I would have hoped for.
    Part of code is:

    Dim BMRange As Range

    MyItem = ActiveSheet.Range("D6").Value
    OpenFile = ActiveSheet.Range("D3").value

    ' Start Word and create an object
    Set WordApp = CreateObject("Word.Application")

    ' Send commands to Word
    With WordApp
    .Application.Visible = True
    .documents.Open FileName:=OpenFile 'open an existing file from worksheet cell value
    End With

    With WordApp
    BMRange = activedocument.Bookmarks("MyBookmark").Range 'runtime error 424
    BMRange.Text = MyItem
    .activedocument.bookmark.Add "MyBookmark", BMRange


    Obviously its only a section of the code and once sorted I would hope to use other cells values to populate further bookmarks in the document.

    Much appreciate any help

    Alan
    Cheshire
    UK

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel to Word (XL97 SR2)

    Hi,
    Try:
    Set BMRange = .activedocument.Bookmarks("MyBookmark").Range
    You have to use the Set statement to assign an object reference to a variable and you were missing the period in front of activedocument.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel to Word (XL97 SR2)

    Rory,
    Many thanks. Your correction worked first time.
    I also needed to change .ActiveDocument.Bookmark.Add "MyBookmark", BMRange
    to
    .ActiveDocument.Bookmarks.Add "MyBookmark", BMRange

    added the 's' to bookmark although my reference books show it as singular.
    Once again thanks.
    Alan

Posting Permissions

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