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