Populate Bookmarkes in a Word Doc. with Excel

[ryguy7272]

I’m trying to push several values from an Excel sheet into a Word document,
using VBA in Excel and bookmarks in Word. Below is what I have so far:

Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bmk As Word.Bookmark
Set doc = objWord.Documents.Open("C:\Test\Disclosure.doc")
For Each bmk In doc.Bookmarks
If bmk.Name = "BrokerFirstName" Then bmk.Range.Text =
ActiveWorkbook.Sheet("LOOKUP").Range("B1").Value
If bmk.Name = "BrokerLastName" Then bmk.Range.Text =
ActiveWorkbook.Sheet("LOOKUP").Range("B2").Value
Next
'doc.Bookmarks("First_Name").Range.Text = Range("A1").Value
objWord.Visible = True

End Sub

The sheet with all the values is called LOOKUP and the ranges are pretty
obvious. What I can’t figure out, is the following:

The macro will run from an Excel file, which will essentially be the
ActiveWorkbook, but I’m not sure how to tell Word that the data is coming
first from the ActiveWorkbook, and then from the sheet named LOOKUP, and
finally from specific cells. Do I even need ActiveWorkbook in there?

As it is written now, I can open a Word template from a specific location,
but I want to be able to open any one of a couple dozen Word files (not a
loop; just open it and let Excel know that this is the active document, with
bookmarks, that need to be updated) from many locations. Thus, I am trying
to incorporate the following line of code into the macro:

file = Application.GetOpenFilename


Does anyone have any ideas about the best way to set this up? I am thinking
it is not difficult at all…just can’t get my mind around it right now…

Regards,
Ryan--

 

[Jon Peltier]

Keep the reference to the active workbook, the worksheet and the range. To
let the user select a Word document:

sWdFileName = Application.GetOpenFilename("Word Documents (*.doc), *.doc), ,
"Select a Word document", , False)
Set doc = objWord.Documents.Open(sWdFileName)

You could name the cells with the data using names that match the bookmark
names. Name a cell by selecting it and typing the name in the name box (just
above cell A1) and pressing Enter. When I do this I usually have a unique
prefix to the bookmark and cell names. The bookmark names might be like
"xlexportBrokerFirstName", "xlexportBrokerLastName", etc. Then I do
something like

For Each bkmk In doc.Bookmarks
If Left$(bkmk.Name, 8) = "xlexport" then
bkmk.Range.Text =
ActiveWorkbook.Worksheets("LOOKUP").Range(bkmk.Name).Value
End If
Next

- Jon

 

[ryguy7272]

Thanks for the code JP. I made a few modifications and I an trying to run
this:
Sub PushToWord()
Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
Fn = Application.GetOpenFilename(, , , , True)

For Each bkmk In doc.Bookmarks
If Left$(bkmk.Name, 8) = "BrokerFirstName" Then
bkmk.Range.Text =
ActiveWorkbook.Worksheets("LOOKUP").Range(bkmk.Name).Value
End If
Next bkmk
objWord.Visible = True

End Sub

I can choose the Word template, but as soon as I click on it, I get a
message that reads “Run-time error 91: Object variable or with block
variable not setâ€

Any idea what could cause this?

I think I'm almost there. I’ll continue to troubleshoot and try to resolve.
If you have any ideas please post back.

Regards,
Ryan---

 

[ryguy7272]

After a few more modifications, I came up with this:
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
Fn = Application.GetOpenFilename(, , , , True)
Set doc = objWord.Documents.Open(sWdFileName)

For Each bkmk In Fn 'doc.Bookmarks
If Left$(bkmk.Name, 8) = "xlExport" Then
bkmk.Range.Text =
ActiveWorkbook.Worksheets("LOOKUP").Range(bkmk.Name).Value
End If
Next

objWord.Visible = True

End Sub

In Excel, the named ranges are xlExportBrokerFirstName and
xlExportBrokerLastName.

The code fails on thsi line:
Set doc = objWord.Documents.Open(sWdFileName)
And when I mouse-over the syntax I get this mssg:
sWdFileName = empty

Does anyone have any thoughts on this? Why is the variable empty?

Thanks,
Ryan--

 

[ryguy7272]

I got it working, and I think I'll go with this:
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)

For Each bmk In doc.Bookmarks
If bmk.Name = "BrokerFirstName" Then bmk.Range.Text = Range("B1").Value
If bmk.Name = "BrokerLastName" Then bmk.Range.Text = Range("B2").Value
Next

objWord.Visible = True

End Sub

It's late, and although it is simple, it is effective, and this is what
matters to me now. Thanks for steering me in this direction Jon. One more
question, in your experience how stable are Word bookmarks? Mine seem to get
deleted wayyyy to easily. I've had to add them in several times now. just
wondering...

Regards,
Ryan---

 

[Jon Peltier]

In general I've found programming in Word to be more frustrating than in
Excel. Granted, I am not so familiar with the object model, but it seems
neither is the team of Word developers. I find that the same amount of work
requires somewhat more pounding of one's head on the table. The bookmarks
are reasonably stable. When you replace the contents of a bookmark, it tends
to vanish, so in my projects that populate a Word template by filling
bookmarks with Excel data, I have code that reinstates each bookmark as it
is processed. I also have a lot of checking, so that the code doesn't bomb
if a Word bookmark has no corresponding Excel name. I've also developed
routines that insert an Excel matrix of cells as a Word table, or inserts an
Excel graphic or image file, at specially encoded bookmarks.

- Jon

 

[ryguy7272]

Your summary of this issue is quite elegant. As you stated, contents of a
bookmark seem to vanish! That seems to be my dilemma now. I may try a few
alternatives this morning. Thanks for helping me to resolve this problem.

Regards,
Ryan--

 

[ryguy7272]

I received a little extra help in the Word Programming DG, and decided to go
with this routine:
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)

objWord.activedocument.variables("BrokerFirstName").Value =
Range("BrokerFirstName").Value
objWord.activedocument.variables("BrokerLastName").Value =
Range("BrokerLastName").Value

ActiveDocument.Fields.Update

objWord.Visible = True

End Sub

I foresee the document variables as being much more stable than the
bookmarks.

Hope this helps others...
Ryan---

 

[Jon Peltier]

Thanks for following up. I'll have to look into these variables...

- Jon