Results 1 to 3 of 3

VBA: how to reference Word bookmark from Excel macro?

This is a discussion on VBA: how to reference Word bookmark from Excel macro? within the Excel Questions forums, part of the Question Forums category; I need to run a macro from Excel that can reference a bookmark defined in a Word document. The macro ...

  1. #1
    haw
    haw is offline
    Board Regular
    Join Date
    Jan 2005
    Location
    Fort Worth, Texas
    Posts
    113

    Default VBA: how to reference Word bookmark from Excel macro?

    I need to run a macro from Excel that can reference a bookmark defined in a Word document. The macro must insert a LINK FIELD into this bookmark. I'm trying to use the code below, which I wrote and which work if run from a Macro residing in a Word document.

    Unfortunately, when I try running the same code fragment in a Macro residing in an Excel spreadsheet, I get a variety of error messages, depending on exactly what portion of the code I try "tweaking." The error messages all say stuff like "application-defined or object-defined error" or "method 'range of object '_global' failed" or "method not supported for object" or other similar things.

    I HAVE turned on the reference to the MS Word object library in VBE/Tools/References. And my Excel macro IS able to successfully open the Word document. In fact, I can copy/paste other information from the Excel sheet into the Word document just fine ... I just can't get this LINK FIELD to work.

    Here's the code that works from a Word Macro:
    Set BMRange = ActiveDocument.Bookmarks("WordBM").Range
    Selection.Goto What:=wdGoToBookmark, Name:="WordBM"
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
    Text:= "LINK Excel.Sheet.8 " & docname & " Sheet1!ExcelRange" & _
    formatting, PreserveFormatting:=False
    ActiveDocument.Bookmarks.Add Range:=BMRange, Name:="WordBM"

    Wonder what I need to change to get it to work from within Excel?

    oh, BMRange is Dim'd "as range" in the word macro, and "as Word.Range" in the Excel macro. Docname and Formatting are just Strings of text

    With the exact code above, I'm getting "run time error #438 - object doesn't support this property or method" error pointing to the Selection.Goto line -- when trying it from within Excel.

  2. #2
    MrExcel MVP Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,435

    Default

    To handle other Office application from Excel, you need to create an object using CreateObject Function (or tick appropriate reference from Tools > References menu of VBE.)
    In the first case, s Constance like "wdGoToBookmark" is not recognized in Excel as it is.
    So you need to TELL excel what does wdGoToBookmark stand for. (You can get the value of "wdGoToBookmark" in Word application.)

    The following code works on Excel.

    Code:
        Const wdGoToBookmark As Long = -1
        Const wdFieldEmpty As Long = -1
        Dim appWord As Object
        Dim BMRange As Object
        Set appWord = CreateObject("Word.Application")
        With appWord
            .Visible = True
            .documents.Open ("C:\Doc1") 'Change Here
            Set BMRange = .ActiveDocument.Bookmarks("WordBM").Range
            .Selection.Goto What:=wdGoToBookmark, Name:="WordBM"
            .Selection.Fields.Add Range:=.Selection.Range, Type:=wdFieldEmpty, _
                                  Text:="LINK Excel.Sheet.8 " & docname & " Sheet1!ExcelRange" & _
                                        formatting, PreserveFormatting:=False
            .ActiveDocument.Bookmarks.Add Range:=BMRange, Name:="WordBM"
        End With
    Regards,

    Masaru Kaji aka Colo - Ex Microsoft MVP Since 2004 -2009

  3. #3
    haw
    haw is offline
    Board Regular
    Join Date
    Jan 2005
    Location
    Fort Worth, Texas
    Posts
    113

    Default Re: VBA: how to reference Word bookmark from Excel macro?

    Ahhhh. That was indeed the trouble - just needed to define the wd... constants. Well, it looks so easy now. But I surely was not making any headway on it by myself! Thank you SO much for the help!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com