VBA: how to reference Word bookmark from Excel macro?

[haw]

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.

 

[Colo]

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.

    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

 

[haw]

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!