VBA: how to reference Word bookmark from Excel macro?

haw

Board Regular
Joined
Jan 18, 2005
Messages
113
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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
 

haw

Board Regular
Joined
Jan 18, 2005
Messages
113
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!
 

Forum statistics

Threads
1,147,694
Messages
5,742,671
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top