Im 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 cant figure out, is the following:

The macro will run from an Excel file, which will essentially be the
ActiveWorkbook, but Im 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 cant get my mind around it right now€¦

Regards,
Ryan--



--
RyGuy

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.Nam e).Value
End If
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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--



--
RyGuy

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.Nam e).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. Ill continue to troubleshoot and try to resolve.
If you have any ideas please post back.

Regards,
Ryan---



--
RyGuy


"Jon Peltier" wrote:

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.Nam e).Value
End If
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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--



--
RyGuy

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.Nam e).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--

--
RyGuy


"Jon Peltier" wrote:

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.Nam e).Value
End If
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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--



--
RyGuy

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---

--
RyGuy


"ryguy7272" wrote:

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.Nam e).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--

--
RyGuy


"Jon Peltier" wrote:

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.Nam e).Value
End If
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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--



--
RyGuy

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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---

--
RyGuy


"ryguy7272" wrote:

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.Nam e).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--

--
RyGuy


"Jon Peltier" wrote:

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.Nam e).Value
End If
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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--



--
RyGuy

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--


--
RyGuy


"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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---

--
RyGuy


"ryguy7272" wrote:

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.Nam e).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--

--
RyGuy


"Jon Peltier" wrote:

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.Nam e).Value
End If
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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--



--
RyGuy

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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---


--
RyGuy


"ryguy7272" wrote:

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--


--
RyGuy


"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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---

--
RyGuy


"ryguy7272" wrote:

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.Nam e).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--

--
RyGuy


"Jon Peltier" wrote:

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.Nam e).Value
End If
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ryguy7272" wrote in message
...
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--



--
RyGuy