Template, VBA and Mail Merge problem

[CJ]

I have a template. I have a user form. I have an Excel spreadsheet which
contains my data. Everything is working beautifully, but.....many users are
not Mail Merge proficient and the whole template idea may be trashed if I
can't make it more automatic. I'm trying to make it easier.
Is there a way to program Word's "Find Entry" to default to a specific field?
Or even better, I would like to declare a variable, and have the user enter
their initials, and have VB pick it up and search the spreadsheet for the row
containing that variable. So I can get the variable, but I can't plug that
into the Find Entry "find what" field, and I can't default the Field to a
specific field.
Research results appears like it can't be done, but before throwing in the
towel on the mail merge direction of my project, I thought I'd at least post
here. Thanks!

 

[Russ]

Your message is a little confusing.
Excel has the lookup function to bring back information.
Are you trying to do a lookup in Excel from Word?
Mail Merge is different from document formfields or activex controls.
Userforms are different from document formfields or activex controls.
The Find method in Word can find text patterns, styles, and formatting.
Other collections like table cells can be iterated through and tested.

From Shauna Kelly:
Control Excel from Word
http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

For information on inserting text into a bookmark, see
Inserting text at a bookmark without deleting the bookmark
http://www.word.mvps.org/FAQs/MacrosVBA/InsertingTextAtBookmark.htm

For a simple example of putting data from Excel into Word, see
http://tinyurl.com/39ga4g

 

[Russ]

CJ,
Just found:

From CyberTaz,
In Word's Tools menu you'll find a feature for Mail Merge for which Excel
files can be used as the record source for the merge. The differing versions
don't matter. If you're not familiar with the feature you might start by
looking it up in Word Help. Then take a look at the information you'll find
here:

http://word.mvps.org/faqs/mailmerge/CreateAMailMerge.htm

More is available from the links on this page4:

http://word.mvps.org/faqs/MailMerge/index.htm

 

[CJ]

I'm sorry I didn't make enough sense. I have the merge working, and the user
form simply collects the recipient information and places it in the template
at the correct places, and I have the Merge "Find Entry" window opening via
visual basic. What I want to accomplish is to either have the "Find in
Field" default to a particular field rather than having to choose a field
every time; or even better, pass a variable to the MailMergeFindRecord form
for full automation.

I have found code to find specific text within a specific field:
ActiveDocument.MailMerge.DataSource.FindRecord FindText:="Text", _
Field:="Field_Name"
But it does not to what I want to do.

 

[David Sisson]

Or even better, I would like to declare a variable, and have the user enter

their initials, and have VB pick it up and search the spreadsheet for the row
containing that variable. So I can get the variable, but I can't plug that

Could you use a listbox in the Userform instead of a single lookup?
http://word.mvps.org/faqs/interdev/filllistboxfromxldao.htm

 

[CJ]

Yes, I could. Then what would I do?

Could you use a listbox in the Userform instead of a single lookup?
http://word.mvps.org/faqs/interdev/filllistboxfromxldao.htm

 

[Russ]

I found this in Word VBA Help for the term code:
For Each aField In ActiveDocument.MailMerge.Fields
If InStr(1, aField.Code.Text, "Title", 1) Then
MsgBox "A Title merge field is in this document"
End If
Next aField

 

[CJ]

The information in the shortcut was very interesting, thank you.
But I still need to do the following:
1. get the user's initials - which I can do
2. go to the database - which I can do
3. go to the column containing the initials - which I can do
3. find the cell containing the initials - which I can do
4. get the entire row - which I can do
5. merge certain of those cells in that row into fields in the template.

I'm so sorry, but I'm missing the entire concept.

 

[CJ]

Oh, boy, can I sure be dense!!! Why try to go through all that when the
simple fix is to just rename my columns and make go with the Find Entry
default, which is "name" and I've been trying to force it to default to a
different column. Oh my goodness. The simplest things sometimes can be so
elusive.

If anyone does come up with code do change the MailMergeFindRecord default
field, I would still very much appreciate it!!!

So now basically I have one excel sheet from which several different
templates can pull and use data. And all I have to do is manage the
database. That is awesome.

 

[Russ]

Are you trying to reinvent the Query in mailmerge?
http://word.mvps.org/faqs/mailmerge/MMergeQueryOptions.htm

 

[CJ]

Oh, yes!! This is exactly what I needed. Thank you so much Russ!
(I did figure out I could just move the column, since the default is the
first column in the spreadsheet -- doh).