Trapping Errors Via Automation

[Neil]

Is there a way to trap an error generated in another app that is controlled
via automation? I have an Access 2000 app that opens Word 2000 and proceeds
to open a series of documents and, in each document, parse the contents and
write it to an Access table (the Access app, after opening Word, runs a
macro within Word which parses and writes to the Access app via DAO).

Occasionally we get an error from Word (such as "document is locked for
editing" or other), which hangs the process. The process is an overnight
one, so, obviously, if it hangs from a Word error message.

Is there a way to trap these error messages generated by Word and just move
onto the next document?

Thanks,

Neil

 

[Alex White MCDBA MCSE]

Hi Neil,

Yes is the simple answer, when automating something like word via vba code
the errors are passed back to the access database in most instances, to
bullet-proof automation code I do the following, every line of code should
numbers in front e.g. 10,20,30 etc, then in your error reporting something
like.


Err_Print_Word_Document:
msgbox err.description & vbcrlf & err.number & vbcrlf & erl()
resume next


will report the error number/description and the line that gives the
specific error, and if you have a specific line that produces errors that
are not show stoppers then just before the line of code something like

on error resume next
.... some code that is not so important
on error goto Err_Print_Word_Document

it is not good programming practice to have the 'on error resume next' at
the start of the code as you never see any errors and your code's logic may
fail without any feedback

also try not to cross post as you may have to check several places for
answers.

 

[Chuck Grimsby]

Why not move the Word Macro to Access VBA code?

 

[Neil]

Because it was hideously slow when I did it that way. When the Word document
was sent through the pipe to Access for Access to parse, it was very slow.
When I moved the code to a Word macro, allow Word to parse it itself without
sending to Access, and then have Word write to an Access table, it was much
faster (though it still takes a fair amount of time).

But I suppose I could open the Word doc from Access and then just use the
Word macro to parse it. That might work.

Neil

 

[Alex White MCDBA MCSE]

Ni Neil,

sounds strange, I wonder what is going on, could you post some code, because
I automate word via access code all the time and I find the time acceptable.

 

[Steve Jorgensen]

That's a pretty common way to do it. Write the Macro in Word, but call it via
Access automation, and trap errors returned from the call.

 

[Neil]

No, that's not what I'm saying. I'm saying that I *already* call the macro
via Automation from Access, but don't get error messages in Access to trap.

In one particular example, if the file's already open, a dialog box appears
in Word, "File is locked for editing" or something like that, which screws
up the whole process. As I note below, perhaps if I move the file open
portion of the code to Access, then I'll be able to trap an error (right now
it's in the macro).

But it seems that when one runs a macro via automation, that any errors that
happen through the macro execution are not trappable by the calling
application (in this case, Access). Or do you know of a way of doing that?

Thanks,

Neil

 

[Alex White MCDBA MCSE]

Hi Neil,

GetDocProperties = ""
Dim xWordobj As Object
Dim xWordDocument As Object
Dim strReturn As String
Set xWordobj = CreateObject("Word.Application")
Set xWordDocument = xWordobj.Documents.Open(Trim(strFileName))
strReturn = xWordobj.activedocument.builtindocumentproperties(2)
xWordDocument.Close
xWordobj.Quit (True)
xWordobj = Nothing
GetDocProperties = strReturn

in the above example I am opening a word document via automation and I can
trap different errors on different lines, personally I never use macros as I
believe vba to be more powerful and flexable.

 

[John Nurick]

Hi Neil,

It sounds as if you need to beef up the error trapping in the Word
macro.

For instance, before trying to open a document in Word, try to open it
as a binary file in a mode that will lock it. If that fails, you'll know
that Word won't be able to open it either, and you can either skip the
file or try making a copy of it and opening that.

If you're not familiar with this article, it's worth reading, as are
some of the articles it links to:
Considerations for Server-Side Automation of Office
http://support.microsoft.com/?id=257757

 

[Chuck Grimsby]

"hideously slow"?!? Then you're doing something wrong, more then just
error trapping.

Usually, it'll go faster.

 

[Steve Jorgensen]

"hideously slow"?!? Then you're doing something wrong, more then just
error trapping.

Usually, it'll go faster.

That's not my experience. Every COM call that passes across an application
barrier has a lot of overhead. If you're running a loop in Access, making
calls to libraries in Word, there's a real performance hit. The documentation
even says so, and recommends ways to mitigate the problem like calling
functions that take array parameters rather than making individual calls in a
loop.

 

[Neil]

That's a good idea about the Word macro and checking if the file's open. I
was hoping there was a way to have the calling app actually get any errors
that the automated app experiences. But I guess not.

N

 

[Neil]

What will go faster? You haven't seen my code, so how do you know how fast
it should go?

Basically the code goes through the Word document one character at a time
and writes strings of characters to a table record when there's a change in
formatting. When Access opened the Word document and then, via the object
variable, parsed the document, looking at each character, it was very slow.
When the process was moved to a Word macro that was called from Access, it
was much faster.

I mean, sure, opening a doc and maybe getting the text is pretty quick. But
this had to look at each character's formatting, etc. Each process of
getting the character's information took x amount of time. When added
together for an entire document, and then multiplied over a couple thousand
documents, it was very slow. Much performance gain when done on the Word
side.

Neil

 

[Neil]

I agree with what you wrote. The act of calling the Word doc, over and over,
once for each character, was hideously slow.

I would have preferred to keep everything Access-side, but I don't see how I
could accomplish the parsing of the document without a loop, having to look
at each character's formatting.

Neil

 

[John Nurick]

Basically the code goes through the Word document one character at a time
and writes strings of characters to a table record when there's a change in
formatting. When Access opened the Word document and then, via the object
variable, parsed the document, looking at each character, it was very slow.
When the process was moved to a Word macro that was called from Access, it
was much faster.

If that's what you're having to do, Neil, the thought of saving the
document as XML and then using an XML parser (or maybe just a regex) to
find characters between certain tags sounds pretty appealing.

 

[Neil]

Would I be able to read the document in Access directly via an add-in, or
how would I read the documents?

 

[Chuck Grimsby]

You're right, I haven't seen you code, nor have you seen mine!

I have a routine I use to convert a Word docuemnt to an HTML file,
which I prefer to use, since I *really* dislike what Word does when you
save a file as an HTML document. Nearly the world's ugliest HTML code,
as far as I'm concerned. But then again... it's trying to be all
things for all things, and the result is probably the best that could
be come up with given those circumstances.

Anyways, it's been a long time since I timed my code, so I just did.
Just under a minute to convert a Word document (with several tables
embedded, but mostly text and a few pictures) to an HTML page. The
word document says it was 36 pages if printed. I haven't had a printer
hooked up to my system from over a decade, so who knows how accurate
that count is.

The code "cheats" a bit by looking at each Word paragraph to determine
if there are any changes to the "standard" font. Just FYI, I guess,
but if you look at each font property, Word will tell you if the
property is the same all the way through the paragraph. The property
returns 0 or -1 if it's the same all the way through the paragraph, if
not, then it returns something that's not 0 or -1. (usually 999999.)
When that's detected, I switch to looking at each word in the
paragraph, doing the same thing with the font properies, and if it
doesn't come back 0 or -1, then it's time to look at each character.

Sure, I suppose I could of looked at each charater from the start, but
why? My goal was mass-processing of word documents to HTML pages, so
the fact that one character was the same as the last didn't really
matter to me, as long as it was the same though the whole paragraph
(which, with the documents I tend to deal with is more often the case
then not. Your milage, as always, may vary.).

Now, mind you, I'm doing this all from Access, using late binding to
Word, since I never know what version my client will be using. But
feel free to do whatever you feel you need to do to accomplish your
goal.

 

[Neil]

That's an interesting concept. I'm not sure I follow with how you determine
if a font is the same all the way through a paragraph. Perhaps you could
elaborate.

FWIW, I look at font name, font size, font color, bold, italic, and
underline (though, in reality, bold, italic, and underline are really all
that's needed).

Neil

 

[Alex White MCDBA MCSE]

Not just the ugliest, but biggest as well, something to do with styles, but
I think John's idea of converting the document either to XML or even text
(if you don't want to preseve formating) and the processing that document to
me is a winner.

just my thoughts.

 

[Chuck Grimsby]

As previously mentioned, "if you look at each font property, Word will
tell you if the property is the same all the way through the paragraph.
The property returns 0 or -1 if it's the same all the way through the
paragraph, if not, then it returns something that's not 0 or -1.
(usually 999999.) "

Additional hints:
ObjWord.ActiveDocument.Paragraphs(lngP).Range.FormattedText.Font.Bold
ObjWord.ActiveDocument.Paragraphs(lngP).Range.Words(lngW).FormattedText.Font.Italic
ObjWord.ActiveDocument.Paragraphs(lngP).Range.Words(lngW).Characters(lngC).FormattedText.Font.Color

Oh, and remember the 0 = False, -1 = True.

It occured to me, after my post, that this code was written for Word97,
and that since that version things might have changed, but I just
checked and it seems to work fine in Word 2003, although I didn't
bother doing any sort of a time test, I just confirmed it works.