word 2003 mailmerge event

[mcchung52]

Hi all,
I have looked everywhere on the web to no avail. So here I am and I'll try to explain the problem as clearly as possible.
I have a doc file with merge fields and when the doc file opens it links to an excel file to get the data (excel file is not opened in any event). I also disabled the sqlquery thing in the registry so that it doesn't ask me about allowing the sql command. After the doc file is shown, it shows the word file and in the toolbar there is a mail merge toolbar with all sorts of buttons and icons. As the arrow keys in the mailmerge toolbar to move back or forward through the record is clicked, the number in the textbox inbetween the arrow keys changes accordingly and also the merge fields that have a link to the excel file updates as that number changes.

Now my question: is there a way to trap that change event (of clicking on the arrow keys or directly changing that number) and possibly insert some text in the doc file according to what's in an excel file (not merge fields)? or do I maybe have to open the excel file in the background? In any case, trapping that event is more of a primary concern for me.
Please help me.

 

[PHV]

You may consider the MailMergeBeforeRecordMerge and MailMergeAfterRecordMerge events.

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

 

[SkipVought]

is there a way to trap that change event (of clicking on the arrow keys or directly changing that number) and possibly insert some text in the doc file according to what's in an excel file (not merge fields)?

Why NOT merge EVERYTHING?

You will need to perform a query in Excel using the current mail merge source data AND the other data 'according to what's in an excel file (not merge fields)' and then use THAT resultset as the merge source.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcchung52]

Ok, I tried using MailMergeAfterRecordMerge but it doesn't seem to trap the event.

This is what I did:
in "ThisDocument", I have the following code
Dim X As New EventClassModule

Private Sub Document_Open()
'Call Register_Event_Handler
Set X.App = Word.Application
end sub

and in a class module named, EventClassModule,

Public WithEvents App As Word.Application
'I chose this event from drop-down list
Private Sub App_MailMergeAfterRecordMerge(ByVal Doc As Document)

MsgBox "hey"
End Sub

I clicked on those buttons in the toolbar that let me navigate through the records and nothing happened.
I also clicked on "Merge to Printer" icon in the toolbar. Nothing happened here as well.
Is this all I have to do? or Am I missing something?
Any input is appreciated~!

 

[mcchung52]

or is it because the "merge" event doesn't actually happen then?

 

[SkipVought]

As you are clicking thru the merge list, the merge has already taken place. You are just looping thru the resultset.

And THAT is why I suggested merging the stuff you want to "add" in Excel, BEFORE the merge in Word.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JudyL]

if you want to change the wording in the resulting document based on what is in the Excel data file (that is the data in the merge fields), why don't you use the "If" merge statement to check the value of a field to determine what wording should be in the final document?

 

[mcchung52]

Thanks Skip.
That first sentence clears a lot of smoke. The word resultset really hit home. I guess I was wondering when that event "merge" actually took place.
Although that Excel file itself is really not updated when using the word file and user at a time only works on a small subset of data, I still need to provide user with all sets of data (which is only about 200 at most) and so selecitve merging is not an option.
Instead, as clicking through those buttons, I want to fill a part of a doc with data from Excel, which otherwise user has to fill. (Instead of my programming the whole interface to enable this, if I can I want to take advantage of what's already available to the user from Office.)
That's why I looked into "merging" as an event to pull data.
Anyway I figured out a way to do just that by looking into me.app.mailmerge.datasource (found out while debugging and watching local variables). All merged data was already there so I extracted from there. Thank you all for the lead.

 

[mcchung52]

which kinda brings me to the next question...
When I open the Word file first then open the excel file, it locks the excel file so that I get a msg like "you can only open in read-only mode". Well... to add capability for user to update excel file anytime he/she wants to and also to not have the word file lock out excel file the whole time, should I instead look into ADO connection to the data without doing the merge so that data is probed only when needed and file unlocked? In this specific example, I'm working with a small set of data, so is this a costly way to go? If so, how much bigger do they need to get in order to be efficient?
Do I need to start another thread for this? (This will open up a whole new horizon in terms of the value the system can provide.)

 

[SkipVought]

You cold use ADO to return the results for a specific criteria and then populate your document. You may have better control. Alternatively, I believe that you could open the Excel file first, then the Word document, then execute the merge, allowing the user to make changes in Excel. I'm not so sure that the Excel changes would reflect in your current merge. Might have to play with it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcchung52]

Ok thank you. will play with it.