PDA

View Full Version : Solved: Push Values to Bookmarks in Word



Anne Troy
03-24-2005, 06:12 AM
Okay, I'm sick of trying to work it out myself.

I want to take some cells and push their values to Word.

Step 1: Select each value (or range) and give it a name.

Step 2: For each named range in Excel, insert a bookmark with the same name into the Excel file.

Step 3: Go to Dick's Daily Dose of Excel (well...when you find it after searching for an hour) and find this entry, which ALMOST does what you want:
http://www.dicks-blog.com/archives/2004/08/13/

Step 4: Yoink this code from Dick:

Sub CreateWordDoc()
Dim wdApp As Object
Dim wdDoc As Object
Dim rCell As Range
Dim rRng As Range
Dim lScore As Long
Dim sName As String

Set rRng = Sheet1.Range("A2:A6")
lScore = 100 ?set the max possible score

?loop through the range
For Each rCell In rRng.Cells
?if the score is less than previous
If rCell.Offset(0, 1).Value < lScore Then
?store the data as variables
lScore = rCell.Offset(0, 1).Value
sName = rCell.Value
End If
Next rCell

?open the word documents
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open("C:AutoWord.doc")

?replace the bookmarks with the variables
FillBookmark wdDoc, sName, "bmWinner"
FillBookmark wdDoc, lScore, "bmScore", "0"

?show the word document
wdApp.Visible = True

End Sub

Sub FillBookmark(ByRef wdDoc As Object, _
ByVal vValue As Variant, _
ByVal sBmName As String, _
Optional sFormat As String)
Dim wdRng As Object

?store the bookmarks range
Set wdRng = wdDoc.Bookmarks(sBmName).Range

?if the optional format wasn?t supplied
If Len(sFormat) = 0 Then
?replace the bookmark text
wdRng.Text = vValue
Else
?replace the bookmark text with formatted text
wdRng.Text = Format(vValue, sFormat)
End If

?re-add the bookmark because the above destroyed it
wdRng.Bookmarks.Add sBmName, wdRng

End Sub

Step 5: Try to figure out how to edit the code to suit cells B1:B4 in your sample, and using my named ranges and bookmark names. Delete all the junk about lower/higher scores and such 'cause you figure it's not needed. Change some stuff to make it work on YOUR named ranges. And have it continually give you errors anyway, and just have no clue as to how to even figure out why it doesn't work.

I do want Dick's method because I have heard time and time again that replacing the bookmarks is best (if you don't, the code works once and that's it--I actually understand that part!), so I like that I can call his function (did I say that right? hee hee) to do that part of the code.

When I couldn't get Dick's to work, I went to MrExcel's VBA book, and yep! There's bookmarks in Word, but they're populating them with hard-coded text and not values from a range or named ranges. Hmph!

Now, there's another method I found in my archives (I'll bet I have some terrific code in them, 'cause most of it's smozgur's), but the one I found finds the bookmark and REPLACES it with the value of a cell. I don't think that is efficient if you're doing tons of replacements.

So, my question is this:

Can someone help me edit the code above to suit the sample files I've provided? I would like the most efficient method, if possible. Final goal is to create a macro in Excel that will open the Word doc, populate the bookmarks and leave the Word doc in front of the user in front of the Excel file.

Dave
03-24-2005, 12:34 PM
Not sure if this helps but see Jon's post here: http://www.mrexcel.com/board2/viewtopic.php?t=98105&postdays=0&postorder=asc&highlight=word+bookmark&start=10
Also, your "FillBookmark" sub looks more like a function? Not sure if I understand that. HTH. Dave

Anne Troy
03-24-2005, 02:16 PM
Dave: I am a jerk. I was peeved because I wanted the code. I didn't want to have to work it out. But I came back an hour later, looked again, and (doh) noticed another page, and so on, and so forth. I ended up searching for bookmarks at MrE and was able to find something that DID work.

I thank you soooo much for posting. If you hadn't, I probably would:

a) have waited for someone else to code it

b) given up

Here's the code I ended up using. It's beautiful:

Option Explicit
Public pappWord As Word.Application
Private Sub Merge()
On Error GoTo ErrorHandler
Dim TodayDate As String
Dim Path As String

Set pappWord = GetObject(, "Word.Application")
TodayDate = Format(Date, "mmmm d, yyyy")
Path = Application.ThisWorkbook.Path & "\pushmerge.dot"
pappWord.Documents.Add Path
On Error Resume Next
With pappWord.Selection
.GoTo What:=wdGoToBookmark, Name:="Ddate"
.TypeText Text:=Range("Ddate")
.Bookmarks.Add Range:=Selection.Range, Name:="Ddate"


.GoTo What:=wdGoToBookmark, Name:="NamePark"
.TypeText Text:=Range("B2")
.Bookmarks.Add Range:=Selection.Range, Name:="NamePark"

.GoTo What:=wdGoToBookmark, Name:="StAdd"
.TypeText Text:=Range("B3")
.Bookmarks.Add Range:=Selection.Range, Name:="StAdd"

.GoTo What:=wdGoToBookmark, Name:="CitySt"
.TypeText Text:=Range("B4")
.Bookmarks.Add Range:=Selection.Range, Name:="CitySt"

End With
On Error GoTo 0
With pappWord
.Selection.WholeStory
.Selection.Fields.Update
.Selection.HomeKey Unit:=wdStory
.ActiveWindow.WindowState = 0
.Visible = True
.Activate
End With
ErrorExit:
Set pappWord = Nothing
Exit Sub

ErrorHandler:

If Err.Number = 429 Then
Set pappWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Can?t open Word "
Resume ErrorExit
End If
0

End Sub

Ken Puls
03-24-2005, 02:40 PM
And, of course, now you're going to submit this to the KB to save anyone else the hassle, right? ;)

:whip

Dave
03-24-2005, 03:40 PM
Like that code! I can't trial it now. I'm on holidays and the rele's puter has Excel on it but can't seem to access it? Anyways, I might take a trial @ making your code doable without the word reference. Late binding and Jon's approach works real well. Maybe make an array for good measure. It seems like a very useful routine. Maybe it would be appropriate to start a new thread if I have any success? This one is solved? Dave

Anne Troy
03-24-2005, 03:51 PM
Yes, but it can be marked unsolved when you're ready, too. :)
I *would* like to see something that doesn't require the bookmark/range names be added...that's what an array would do, no?

chrismc
01-15-2006, 03:50 PM
Firstly, thanks for a great piece of code. Not only did it provide 95% of a solution for a problem I was looking into but it generated a real eureka moment. I could suddenly see how to run applications from other applications.

I saw that you were using bookmarks. Here's another approach (which I picked up from a forum, quite possibly this one). You can use form fields instead of bookmarks.


With pappWord.ActiveDocument
.FormFields("Ddate").Result = Range("Ddate").Value
.FormFields("NamePark").Result = Range("B2").Value
.FormFields("StAdd").Result = Range("B3").Value
.FormFields("CitySt").Result = Range("B4").Value
End With