Autofill from link

[Worzel Gummidge]

I have an MS Excel spreadsheet. In each column, there is info. Column A =
name, B = address, C = postcode and D = reference number. In column E, I
have a little image which, when clicked I want to do the following.......

When I click the image in column E, for example cell E1, I want it to open
an MS Word letter template. This will be a template I have designed myself.
Now, in this template, I want MS Word to automatically complete certain info
in the letter, for example, I would like MS Word to automatically complete
the name, address and reference number in the Word document, by extracting
this info from the Excel spreadsheet.

Any suggestions?

 

[Brad Detchevery]

You can do a simple mail merge from excel data to word. More info here
http://wordprocessing.about.com/od/usingmailmerg1/l/blexcelmerge2.htm

Individual merging I'm not sure about the latest Office products, in older
versions I would probably have coding some VBA. There is probably an easier
way, but if you are comfortable with VBA I would just write a macro that
opens the Microsoft Word template, and calls multiple replacments of fields
in the Word document with the data from the Excel

Pseudocode (untested):

Set ActiveDocument = GetObject(PathToTemplate)
CurrentRow = 1 'Or whatever row the image is on when clicked

For Each F In ActiveDocument.Fields
If IsField(F, "Full_Name") Then

F.Result.Text = ActiveSheet.Cells(CurrentRow, 1) 'Cell A1

ElseIf IsField(F, "Street_Address") Then

F.Result.Text = ActiveSheet.Cells(CurrentRow, 2) 'Cell B1

ElseIf IsField(F, "Postal_Address") Then
F.Result.Text = ActiveSheet.Cells(CurrentRow, 3) 'Cell C1

ElseIf IsField(F, "Reference_Num") Then
F.Result.Text = ActiveSheet.Cells(CurrentRow, 4) 'Cell D1

End If

Etc...

I have done this in the past with Outlook 97 and Word 97, but as I said MS
may have come up with an easier way by now.


========================
B. Detchevery
http://www.BWebCentral.com