Transfer Data from ListBox to MergeFields

R

ryguy7272

I’ve always found the help in this Excel Programming DG to be tremendously
valuable (and from time to time I try to offer valuable help too). Recently,
I posted a Word-based question to the Word Programming group, but only got
one response and I didn’t know what to do with the advice I got so I’m coming
here for help. I may, or may not, get the answer here. Let’s see. Anyway,
I am currently facing a challenge to get data from a ListBox in MS Word,
which originally comes from Excel, into about 32 MergeFields in a Word
template that I created.

Everything works, with the exception of the final transferring of data from
a ListBox into several MergeFields in my Word document. The code below works
for Bookmarks, but not for MergeFields. I don’t know how to create code that
takes data from a ListBox and transfers this data into the appropriate
MergeFields. The code for doing this for Bookmarks is displayed below:

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm1.Hide
End Sub


Doug gave me this code over the summer. It works great for Bookmarks in a
Word document, but it doesn’t do anything for me now that I am using
MergeFields. Some of my MergeFields are as follows:
{MERGEFIELD City \* MERGEFORMAT}
{MERGEFIELD State \* MERGEFORMAT}
{MERGEFIELD Number \* MERGEFORMAT}
…etc.

For a full explanation of the issue, and all related details, here is a link
to the post on the Word Programming DG:
http://www.microsoft.com/office/com...330af49adca&cat=&lang=en&cr=US&sloc=en-us&p=1

Any help would be greatly appreciated!!

Regards,
Ryan--
 
B

Bernie Deitrick

Ryan,

Mergefields are meant for merging an Excel (or other) database into a file template, creating a
multiple-page document, with one copy of the document per record. If you want to manually (or
through code) do that sort of merging, a simple way with a macro is to use placeholders in your
document. For example, if you document has

Hello ZZZZNameZZZZ,

My friend ZZZZFriendZZZZ told me about a great opportunity in the field of bridge sales...

Then you could do a Find/Replace of ZZZZNameZZZZ with the appropriate value, and so on.

Otherwise, instead of doing a field by field replacement, try recording the actual mail merge...

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top