Microsoft Office Forums - View Single Post

ptmuldoon · 12-08-2014, 09:27 AM

In Excel, I have a few hundred cells labeled with a NamedRange.

ie, Cell B10 on Worksheet2 may be called 'Company_Name', 'GL_Name', or something else. The information in these cells is all text.

Now in Word, I have similarly named BookMarks of 'Company_Name', etc.

Now, i'm trying to compare the BookMark Name to the NamedRange in Excel. And if a match is found, to update the BookMark with the value of the NamedRanged.

I'm boarding a plane here soon, but will report back later tonight as well as I continue to learn and experiment some more.

And thank you for your help as well. I'm (very) slowly beginning to understand and learn more on VBA. Its a long self learning !!

Edit. Made some more progress. And the below is now close. But it gives me the cell reference, ie. =Sheet1!$E$5 vs the actual value of the cell. So Unsure how to correct the NmdValue = Nm.Value in the below.

And/Or a better way vs looping everything.

Code:

Sub Test()
    Dim f, xlWorkBook As Object
    Dim Bm, Nm As Variant
    Dim Bmk, NmdRng, NmdValue As String
    Dim NewPath, MyText 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
        'NewPath = f.SelectedItems(1)
        'MsgBox "The New File Path is: " & NewPath
        Set xlWorkBook = GetObject(f.SelectedItems(1))
    Else 'user clicked cancel
        Exit Sub
    End If
    
    For Each Bm In ActiveDocument.Bookmarks
        Bmk = Bm.Name
        'MsgBox Bm
             
        ' Check Each BookMark for a Matching NamedRanged in the Excel File
        For Each Nm In xlWorkBook.Names
        NmdRng = Nm.Name
        NmdValue = Nm.Value 'This currently wrong, gives the cell reference, not the value?
        
             If Bmk = NmdRng Then
                'MsgBox "Match Found with " & Bmk
                'MsgBox NmdValue
                MyText = "String you want to insert"
                'ActiveDocument.Bookmarks(Bmk).Range.InsertAfter MyText
                ActiveDocument.Bookmarks(Bmk).Range.InsertAfter NmdValue
                'UpdateBM Bmk, MyText
                 Exit For
             End If
        Next Nm
        
    Next Bm
    
    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