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:
[vba]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[/vba]
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.
Reply With Quote
