Handling errors in merge VBA

[Walter Briscoe]

I run Word and Excel 2003. I have a VBA macro which mailmerges an Excel
file into a Word file. Usually, a new file window is created and gets
focus. My macro then continues and does some post processing.
Yesterday, I found that an error window containing "A field calculation
error occurred in record 1." was also created and got the focus.
This got me thinking. I have a couple of questions for the experts here:

1) Do mailmerge error messages all get tagged "Error!"?
Mine is "Error! Bookmark not defined" and should be easy to fix. If the
tag is mere coincidence, then going from the error window to the
appropriate part of the output document would be hard.

2) How do I get my VBA to recognise that an error file was output and
took focus and force focus to proceed to the output data window rather
than the error window?

 

[Peter Jamieson]

The following message I wrote a couple of years ago doesn't answer all
your questions but I'm posting it in full anyway because I suspect most
of it will be relevant.

Sorry, I cannot remember (and probably never knew) whether all the error
messages are prefixed by "Error!"

<<
As far as I know because I don't do bulk stuff like this,
a. you cannot trap all errors using the Word object model. For example, you
cannot trap the "error" where the Mail Merge Data Source does not exist.
b. You probably already know that you can reduce the possible number of
dialog messages by setting

objWordApplication.DisplayAlerts = wdAlertsNone

(where objWordApplication is your Word Applicaiton object

c. You can suppress some types of error by executing the merge using

objWordApplication.objMMMD.MailMerge.Execute Pause:=False

(rather than objWordApplication.objMMMD.MailMerge.Execute Pause:=True)

where objMMMD is your Mail Merge Main Document object

If you do that, some syntax errors will be logged in a separate Errors
document. But how do you know that such a document has been created? Well,
you can compare the document count and document names pre- and post- merge.
For example, if pre-merge your document count is always 1, then
1. if you are merging to a new document and there are no errors, post-merge
you should see 2 documents (MMMD, destination document), and the
ActiveDocument should be the Destination Document
2. if you are merging to a new document and there are errors, post-merge
you should see 3 documents (MMMD, destination document, error document), the
ActiveDocument should be the error document, and the ActiveDocument.Name
should be something like "Mail Merge ErrorsNNN" where NNN is a sequence
number (at least, in Word 2003, English Language version it's like that).
3. if you are merging to any other destination type and there are no
errors, post-merge you should see 1 document (MMMD), and the ActiveDocument
should be the MMMD
4. if you are merging to a any other destination type and there are errors,
post-merge you should see 2 documents (MMMD, error document), the
ActiveDocument should be the error document, and the ActiveDocument.Name
should be as above.

I know that some simple errors, e.g. you have a formula field { ={
MERGEFIELD myfield } } and myfield is not numeric) will be dealt with by
setting .DisplayAlerts = wdAlertsNone anyway (in other words, if those are
the only types of error you get, you don't need to contemplate produing an
Error Document, but if you do, an Error Document will be produced when this
error occurs, regardless of the state of .DisplayAlerts). However, I don't
have enough experience of doing that to tell you exactly what types of error
are trapped by what technique.
Peter Jamieson

http://tips.pjmsn.me.uk

 

[Walter Briscoe]

In message <#[email protected]> of Thu, 28 May 2009
10:08:22 in microsoft.public.word.mailmerge.fields, Peter Jamieson

The following message I wrote a couple of years ago doesn't answer all
your questions but I'm posting it in full anyway because I suspect most
of it will be relevant.

Sorry, I cannot remember (and probably never knew) whether all the
error messages are prefixed by "Error!"

[snip]

It is good stuff and led me to write the following naive code:
' Put focus on output
If Documents.Count > 2 Then
MsgBox "something went wrong with merge, will focus on output"
ActiveDocument.Close
End If

I probably ought to put the focus on documents(2), but Rome wasn't built
in a day. Windows(Documents(2).name)).activate might do it - but did not
immediately occur to me.

The cause of the failure entertains me:
I have some script:
{if 1 = 1 {INCLUDETEXT "{DOCVARIABLE Home}enc.doc" "{MERGEFIELD foo}_a_b"}}}
(I apologise if I made any syntax errors, I transcribed by hand. I never
noted the code to copy the text of a field rather than its result.)
This pulls in 2 alternative bookmarks no_a_b or yes_a_b according to foo
being no or yes. You've guessed it; it was neither.
I thing I would have to write something brutal to fix the heads, tails,
on the side problem. Meanwhile, I will live with it and check the
specification of the data.