Solved: Pushing Excel data to a Word template

[Mustang3]

This is my first experience with this forum (or any forum for that matter). If my question has been answered already, maybe you could refer me to that thread.

I found an article by Ken Puls at vbaexpress kb, "..../kb/getarticle.php?kb_id=381" (I can't post links yet). It was called "Push Excel Named Range Values to Bookmarks in Word." This is very close to what I am hoping to do.

I would like to treat the excel spreadsheet as a database with each row being a separate record. For example, a single worksheet with many rows of name, address, phone number, etc. cells as fields. Then I would like to be able to "select" a particular row and have those cell values for that row pushed into a word template such as a form letter. - I do not want to push the information from the non-selected rows.

Can the code in the above article be tweaked to do that? Or, has someone already written a macro that I might be able to use? Or, is this not really doable?

Thanks in advance....

[Demosthine]

Good Afternoon.

First, let me welcome you to the Forum. I'm afraid, though, you may find this quite an addictive sight. It has an endless supply of wonderful information on it. And some of Ken's information is no exception.


You are on the right track using that KB Article. There are only a few slight changes you'll need to make to the code.

First, since you are only accessing the data on one row, you won't want to use named ranges. Instead, use Row 1 for your Column Headers. Each Header will contain the name of the FormField's Bookmark in Word.

Next, you're going to replace part of the For Each xlName in wb.Names loop to match a horizontal data source. For the example below, we'll assume you have your data in Columns 1 through 9.


For intCol = 1 to 9
' Since the Bookmark Name is located in the Column's Row 1, we'll use
' that to check the Bookmarks.Exist statement.
If docWord.Bookmarks.Exists(Cells(1, intCol).Value) Then
' The Bookmark was found, so now we'll push the data.
docWord.Bookmarks(Cells(1, intcol).Value).Range.Text = _
Cells(ActiveCell.Row, intCol).Value
End If
Next intCol


If you need more of an explanation, let me know.
Scott

[Mustang3]

Thank you, so much.

I have entered your code. However, the compiler tells me the variable intCol is not defined.

Just a little more help please. Sorry for being such a newb.

[Demosthine]

Hi again.

Don't worry about being a newbie. We all had to start somewhere.

You'll find that I don't just provide the code, I give explanations, too, so...

Visual Basic (and the Visual Basic for Applications subset) have an line that can be placed at the top of each Module or Class called Option Explicit. This tells Visual Basic that every variable must be defined using a Public, Private, Global, Const, or Dim statement prior to being used. You'll find this line at the top of your module.

So to fix the problem, go up to Line 9 of the code, which should read:
Dim Path as String

Directly below that, add another line reading:
Dim intCol as Integer

Run your program and you should be ready to roll.

Scott

[Mustang3]

Thank you, Scott.

It works like a charm!!!:clap2: