VBA Merge from Excel to Word by variable instead of DataSource. 1

[sqleptical]

Does anyone have a VBA code snipet that will assign a Word Merge field a value from the Excel sheet that the macro is ran from? I'm trying to skip the step of having to write the variables to a .csv file, as the values to be written are already known inside the macro (Cells(r, 6) should be written into the merge field "ClientName" of the merge doc merge.doc).

The code below does not work, but might give some of you gurus an idea of what I mean:

Set appWd = CreateObject("Word.Application")

appWd.documents.open Filename:="c:\merge.doc"
destination.fields."ClientName" = Cells(r, 6)

Thanks for your help!

 

[SkipVought]

Hi,

Maybe...

Set appWd = CreateObject("Word.Application")

set doc = appWd.documents.open Filename:="c:\merge.doc"

'assuming that r has been assigned a proper value...

doc.MailMerge.Fields("ClientName") = Cells(r, 6)

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[sqleptical]

Thank you for your help. Unfortunately, that throws an "invalid use of property" error and VB highlights the following:

.Fields("ClientName") =

r has been assigned a proper value. I also tried inserting something static to be sure:

doc.MailMerge.Fields("ClientName") = "David

 

[SkipVought]

Run it again and DEBUG. Open the Watch Window and see what the messages are for...
[tt]
doc.MailMerge.Fields("ClientName")

and

Cells(r, 6)
[tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[sqleptical]

The error does not give me the option to debug. The only buttons on the error message are 'OK' & 'Help'.

Nothing appears in the Watch Window.

Thanks again!

 

[SkipVought]

"Unfortunately, that throws an "invalid use of property" error and VB highlights the following:

.Fields("ClientName") = "

RIGHT THERE, hit the DEBUG button.


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[sqleptical]

Sorry, it's a "Compiler Error", Debug is not an option.

 

[SkipVought]

Of course...

\Fields Property
See AlsoApplies ToExampleSpecificsFields property as it applies to the Document, Range, and Selection objects.

Returns a read-only Fields collection that represents all the fields in the document, range, or selection.

expression.Fields
expression Required. An expression that returns one of the above objects.

Note When applied to the Document object, the Fields property returns a Fields collection that contains only the fields in the main text story.

Fields property as it applies to the MailMerge object.

Returns a read-only MailMergeFields collection that represents all the mail merge related fields in the specified document.

Try a FormFields object instead. Means redoing the controls in your document.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[PHV]

You may consider the MailMergeField.Delete and MailMergeFields.AddFillIn methods.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sqleptical]

I was able to get Skip's solution to work. Thanks for the help guys!