Perhaps:
Code:
Sub Test()
Dim f, xlWorkBook, NmdValue As Object
Dim Bm, Nm As Variant
Dim Bmk, NmdRng As String
'Choose the Excel File
Set f = Application.FileDialog(msoFileDialogFilePicker)
f.Title = "Please Select A New File"
f.AllowMultiSelect = False
f.Filters.Clear
f.Filters.Add "Microsoft Excel Files", "*.xls, *.xlsb, *.xlsm, *.xlsx" 'Limit to Excel Files Only
If f.Show = -1 Then
Set xlWorkBook = GetObject(f.SelectedItems(1))
Else 'user clicked cancel
Exit Sub
End If
' Check Each NamedRanged in the Excel File for a Matching BookMark
For Each Nm In xlWorkBook.Names
NmdRng = Nm.Name
If ActiveDocument.Bookmarks.Exists(NmdRng) = True Then
Set NmdValue = xlWorkBook.Names(NmdRng).RefersToRange
'Update the found bookmark
ActiveDocument.Bookmarks(Bmk).Range.InsertAfter NmdValue
End If
Next
ActiveDocument.Bookmarks.ShowHidden = True
ActiveWindow.View.ShowBookmarks = False
End Sub
Sub UpdateBM(BookmarkToUpdate As String, TextToUse As String)
Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange.Text = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Sub