Referenceing Excel Cells in Word Document - MSOffice 2010

[pendle666]

Hi

I've got a standard letter in Word which I use on a regular basis to let someone know their contract has been extended.

My main data is in Excel and what I'd like to do is to run a macro which takes the line in the worksheet that is active and enters the relevant data into the Word doc. It would be A135 (first name) B135 (second name) L135(end date) M135 (rate of pay) for example.

I've started with just copy/paste a particular cell to see what the reference looks like - in this case the first name - to see how the link works but it references the actual cell but this will differ each time.

I already have a macro sitting in the ribbon to open this letter:


Sub extension_letter()

Set appWD = CreateObject("Word.Application")
appWD.Visible = True

appWD.Documents.Open Filename:="J:\Master Documents\contract extensions.doc"

End Sub

Can anyone point me in the right direction?

 

[SkipVought]

Hi,

Why not use MailMerge with the Excel as the data source?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pendle666]

Hi

I guess I could, but I'm not sure what you've got in mind - do you mean to record a macro going through the mail merge process?

Pendle

 

[SkipVought]

You apparently don't know what MailMerge is. Check HELP.

Another question: why row 135?

Let me share how I used Word MailMerge and Excel to customize personalized children's books, back in 1995. Each book (Word doc) had the presonalizations as MailMerge fields. When a customer ordered a book, I'd enter the appropriate presonalizations in an Excel sheet. Then I stated my MailMerge, filtered on the customer order and ran the MailMerge, and printed the book sheets.

No VBA requiered!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pendle666]

Row 135 is just an example. There are 300 workers and any one of them could need this particular letter doing.

I know how the mailmerge works, what I'm trying to achieve is to click the button in Excel and dump what I need from whatever the active row is in Excel into the Word letter.