simple? Word Bookmarks link to Excel

[JFamilo]

I have a fairly complex problem that requires a simple solution. I hav
a worksheet with many records in approximately 100 columns. I also hav
a Word template with many bookmarks. I would like to link the bookmar
entries to cells in the excel file so that when the file is updated s
are the entries in the Word file. I can figure out how to do thi
manually but no with VBA.

Also, since there are several records I need to create a document fro
the template for each of the records. All of these need to put on
different page in a continuous Word document.

Any and all help would be greatly appreciated!!

 

[Henry]

JFamilo,

You're nearly there!

Use Excel VBA to open the Word Doc., put the data in the bookmarks and print
the doc.
Put this all in a loop to go through each of your templates in turn.


Here's some code I wrote to do just that.
You'll need to change the names of the bookmarks, file locations and
variables to suit your needs.
You'll also need to change it from looping through worksheets and rows to
looping through templates.


Dim appWD As Word.Application
'Set-up appWD
Set appWD = CreateObject("Word.Application.9") 'Open M.S.
Word (the 9 refers to Word 2000, use 10 for Word XP)
appWD.Visible = False
'Hide word window if you don't want to see the word doc.
appWD.ChangeFileOpenDirectory Sheets("System").Range("MyPath").Value
'Word looks here for file path
appWD.Documents.Open Filename:="ACM23", ConfirmConversions:=False,
ReadOnly:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="",
Format:=wdOpenFormatAuto 'Open doc
For gCounter = 2 To 3
'Look at each Worksheet
Worksheets(gCounter).Activate
For gMyRow = 3 To Worksheets(gCounter).UsedRange.Rows.Count
'Look at each row
If Worksheets(gCounter).Cells(gMyRow, 14).Value = "" _
And Worksheets(gCounter).Cells(gMyRow, 1).Value <> "" Then
'Exclude blank lines
If DateValue(Worksheets(gCounter).Cells(gMyRow, 10).Value) <
DateAdd("d", Date, -14) _
And Worksheets(gCounter).Cells(gMyRow, 11).Value = "" Then
'Found an outstanding first reminder
Worksheets(gCounter).Cells(gMyRow, 11).Value = Date
'Note reminder sent
'********************************************************
'print first reminders
'*******************************************************
appWD.ActiveDocument.FormFields("Title").Select
'Title
appWD.ActiveDocument.FormFields("Title").Result =
Worksheets(gCounter).Cells(gMyRow, 2).Value
appWD.ActiveDocument.FormFields("Title2").Select
'Title
appWD.ActiveDocument.FormFields("Title2").Result =
Worksheets(gCounter).Cells(gMyRow, 2).Value
appWD.ActiveDocument.FormFields("Initial").Select
'Initial
appWD.ActiveDocument.FormFields("Initial").Result =
Worksheets(gCounter).Cells(gMyRow, 3).Value

'Initial
appWD.ActiveDocument.FormFields("Surname").Select
'Surname
appWD.ActiveDocument.FormFields("Surname").Result =
Worksheets(gCounter).Cells(gMyRow, 4).Value

'Surname
appWD.ActiveDocument.FormFields("Surname2").Select
'Surname
appWD.ActiveDocument.FormFields("Surname2").Result =
Worksheets(gCounter).Cells(gMyRow, 4).Value

'Surname
appWD.ActiveDocument.FormFields("number").Select
'House/flat number
appWD.ActiveDocument.FormFields("number").Result =
Worksheets(gCounter).Cells(gMyRow, 5).Value

'Number
appWD.ActiveDocument.FormFields("Address1").Select
'First line of address
appWD.ActiveDocument.FormFields("Address1").Result =
Worksheets(gCounter).Cells(gMyRow, 6).Value

'Address1
If Worksheets(gCounter).Cells(gMyRow, 7).Value <> "" Then
Worksheets(gCounter).Cells(gMyRow, 7).Value = _
Worksheets(gCounter).Cells(gMyRow, 7).Value & ","
End If
appWD.ActiveDocument.FormFields("Address2").Select
'Second line of address
appWD.ActiveDocument.FormFields("Address2").Result =
Worksheets(gCounter).Cells(gMyRow, 7).Value

'Address2
appWD.ActiveDocument.FormFields("Postcode").Select
'Postcode
appWD.ActiveDocument.FormFields("Postcode").Result =
Worksheets(gCounter).Cells(gMyRow, 8).Value

'Postcode
appWD.ActiveDocument.FormFields("No").Select
'No
appWD.ActiveDocument.FormFields("No").Result =
Worksheets(gCounter).Cells(gMyRow, 1).Value

'No
appWD.ActiveDocument.FormFields("SentDate").Select
'Date review sent
appWD.ActiveDocument.FormFields("SentDate").Result =
Worksheets(gCounter).Cells(gMyRow, 10).Value
appWD.ActiveDocument.FormFields("Date").Select
'Date of letter
appWD.ActiveDocument.FormFields("Date").Result = Date
appWD.PrintOut
'Print letter
Application.Wait Time + TimeValue("00:00:05")
'Delay to let printing finish

End If
Next gMyRow
Next gCounter
Application.Wait Time + TimeValue("00:00:05")
'Delay to let printing finish
appWD.DisplayAlerts = wdAlertsNone
'Turn off alerts
appWD.Quit SaveChanges:=False
'Quit word
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Henry

 

[JFamilo]

Thank you! This is so much help. I can't wait to try it.

Just one question though... will this link my bookmarks to my excel
file so that if the excel file is updated then the bookmarks in the
word file are updated too?

I think I have to change the statement:

Code:

 

[NickHK]

JFamilo,
Never used it, but Word has a Link option for a Field.
Check out "Field codes: Link field" in Word Help. It appears to do what you
want.

NickHK

 

[Henry]

JFamilo,
The bookmarks aren't linked to XL.
All my code does is to fill in the bookmarks for you from XL
If you add, remove or rename any bookmarks, you'll have to add code to fill
them.

You could set up all the possible bookmarks on the Word Doc. and then use If
statements to either put in whatever you want or, if you don't want to use
them in some versions, put in "" or If condition Then ignore those "Result
=" lines

The other way around the problem would be to have several versions of your
Word Doc and open the appropriate one.
You would then need different code for each version.

Henry