Update Word bookmark with cell contents from Excel

[mpdsal]

Greetings,

I am attempting to program a macro that will open an existing Word 2010 document/template and paste a cell or cells from Excel 2010. In addition, I want to highlight the bookmark in yellow. Here is my code. For some reason it keeps giving me an error on the Range.HightlightColorIndex line.

Can anyone help?

Thanks

Mark


Sub test()
Dim objWord As Object
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("CRD")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
' objWord.Documents.Open "C:\test.docx " ' change as required"
objWord.Documents.Open "C:\test.docx"

With objWord.ActiveDocument
.Bookmarks("Rating").Range.Text = ws.Range("D57").Value
.Bookmarks("Rating").Range.HighlightColorIndex = wdYellow

' .Font.Name = "arial"
' .Font.Color = wdColorBlue
' .Font.Bold = True
' .Font.Italic = False
' .Font.Allcaps = False
' .Font.Size = 9
' .Bookmarks("Text2").Range.Text = ws.Range("A2").Value
' .Bookmarks("Text3").Range.Text = ws.Range("A3").Value


End With
Set objWord = Nothing
End Sub

[fumei]

What is the exact error message you are gettiing.

NOTE: .Bookmarks("Rating").Range.Text = ws.Range("D57").Value

places the value into the range of the bookmark...and then destroys the bookmark. Thus, any instruction that references the bookmark afterwards will fail.

To update a bookmark use something like this:
Sub UpdateBM(strBM As String, strText As String)
Dim r As Word.Range
Set r = objWord.ActiveDocument.Bookmarks(strBM).Range
r.Text = strText
objWord.ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End SubThis puts the value into the bookmark range, then recreates the bookmark. This allows true, multiple, changes to the bookmark value.

[mpdsal]

QUOTE]What is the exact error message you are gettiing[/QUOTE]

I am gettting an error that says "Compile Error: Can't find project or library"
on this line of code : Bookmarks("Rating").Range.HighlightColorIndex = wdYellow

Perhaps it is related to your comment about deleting the bookmark?

Thanks for replying.

Mark

[mpdsal]

[/quote]To update a bookmark use something like this:
Sub UpdateBM(strBM As String, strText As String)
Dim r As Word.Range
Set r = objWord.ActiveDocument.Bookmarks(strBM).Range
r.Text = strText
objWord.ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End SubThis puts the value into the bookmark range, then recreates the bookmark. This allows true, multiple, changes to the bookmark value.[/quote]

Now I am getting the error "Compile Error: Can't find project or library" on this line of code from above: Dim r As Word.Range

Should it matter if I am coding the VBA from within an Excel file instead of a Word doc?

Appreciate it.

Mark

[fumei]

Well it certainly DOES matter if you are executing from Excel, rather than Word. However, if the instructions are fully qualified (and using late-binding) it should execute.

It almost sounds like you do not have Word. That would be the project or library not being found. Note that it is erroring out on the first instruction to actually use Word objects. Ummm, you DO have Word properly installed, yes?

[mpdsal]

Yes. I have Word 2010 installed and have been using it. I must be missing a command that points to Word to open a file or execute at the top of my code.

Based on your response since I am running this from within Excel what changes do I need to make to my code:



Sub test() Dim objWord As Object Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("CRD") Set objWord = CreateObject("Word.Application") objWord.Visible = True ' objWord.Documents.Open "C:\test.docx " ' change as required" objWord.Documents.Open "C:\test.docx" With objWord.ActiveDocument .Bookmarks("Rating").Range.Text = ws.Range("D57").Value .Bookmarks("Rating").Range.HighlightColorIndex = wdYellow ' .Font.Name = "arial" ' .Font.Color = wdColorBlue ' .Font.Bold = True ' .Font.Italic = False ' .Font.Allcaps = False ' .Font.Size = 9 ' .Bookmarks("Text2").Range.Text = ws.Range("A2").Value ' .Bookmarks("Text3").Range.Text = ws.Range("A3").Value End With Set objWord = Nothing End Sub

[mpdsal]

I don't know what happened to the code. I used the VBA wrap tags. Here it is again.


Sub test()
Dim objWord As Object
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("CRD")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
' objWord.Documents.Open "C:\test.docx " ' change as required"
objWord.Documents.Open "C:\test.docx"

With objWord.ActiveDocument
.Bookmarks("Rating").Range.Text = ws.Range("D57").Value
.Bookmarks("Rating").Range.HighlightColorIndex = wdYellow

' .Font.Name = "arial"
' .Font.Color = wdColorBlue
' .Font.Bold = True
' .Font.Italic = False
' .Font.Allcaps = False
' .Font.Size = 9
' .Bookmarks("Text2").Range.Text = ws.Range("A2").Value
' .Bookmarks("Text3").Range.Text = ws.Range("A3").Value


End With
Set objWord = Nothing
End Sub