Running template code after a 3rd party app runs

[C]

We have a 3rd party app that uses Word as its report writer. I would like to
run additional code from within the template after the app is done and has
filled-in all bookmarks. If I put my code in an AutoNew procedure in the
template, it executes before the other app is done. I can force it to run
later by putting a delay in the AutoNew and calling the code from another sub
(Application.OnTime Now + TimeValue("00:00:05"), "NewProcedure", but I can't
always be sure how long the 3rd party app will require. Is there a way other
than a time value to control starting the code when the other app is
complete? Thanks very much!

 

[Jay Freedman]

We have a 3rd party app that uses Word as its report writer. I would
like to run additional code from within the template after the app is
done and has filled-in all bookmarks. If I put my code in an AutoNew
procedure in the template, it executes before the other app is done.
I can force it to run later by putting a delay in the AutoNew and
calling the code from another sub (Application.OnTime Now +
TimeValue("00:00:05"), "NewProcedure", but I can't always be sure how
long the 3rd party app will require. Is there a way other than a
time value to control starting the code when the other app is
complete? Thanks very much!

If you can arrange to have your other app (or a batch file that runs it)
create a file or a registry entry when it's done, you can make your Word
code "poll" until that marker appears.

The idea is to put code at the beginning of NewProcedure that looks for the
marker. If it finds the marker, it deletes the marker and continues with its
processing. If it doesn't find the marker, it sets Application.OnTime to
wait for a bit and then run NewProcedure again.

Instead of OnTime (which has some known flakiness because of the way timers
are implemented in Windows), you can use the Sleep API call. You'll need the
declaration at the top of the module:

Declare Sub Sleep Lib "kernel32" Alias "Sleep" _
(ByVal dwMilliseconds As Long)

To sleep for 5 seconds, call Sleep(5000) and then check for the marker
again. It's also a good idea to keep a running total of the time you've been
waiting, and exit the macro with an error message after some longish period.

 

[C]

The 3rd party app is a commercial executable to which I have no access. The
vendor has only allowed customization in the form of our being able to load
our own tempates. Your comments have, however, made me think that maybe I
could look for some piece of information that is consistently filled-in by
the app and poll for its existence. I really appreciate the help and the
good Sleep API information. Thanks!

 

[Jay Freedman]

Just to be sure you recognize what might have been a cryptic bit of
shorthand in my first reply: When I said "(or a batch file that runs
it)" I had exactly this sort of situation in mind. You can't influence
what the 3rd party app does, but you might be able to make a batch
file that launches the 3rd party app and then creates the marker.
Since control doesn't return to the batch file until the 3rd party app
exits, that might be sufficient to let Word know when to run your
procedure. What I'm not sure about is whether the 3rd party app
actually does exit at the proper time.

 

[C]

I should know to give better information up front! Your suggestion is
excellent, but still beyond my control: the main application, a case
management system, is served from a vendor location via terminal services.
The user typically executes it in the morning and leaves it running all day.
The vendor uses Word as the report writer for various letters and reports
relating to different cases. The only local control is that the users are
allowed to provide their own Word templates. That's where I come in -- I
have control to write code within the actual .dot’s, but that's all.

So far, the only code I have been able to execute via their system is within
an AutoNew() procedure. However, since it executes before the vendor's code,
I am unable, for example, to search and react to information they have
filled-in using bookmarks. I'm still working on the Sleep suggestion, but so
far haven't gotten it quite right. The one thing I have gotten to work is to
use the AutoNew() procedure to display a custom toolbar and button that the
user can click to execute my code (and remove the toolbar) after the document
appears on screen with the vendor code complete. It's absolutely not
eloquent and, therefore temporary, but it works.

Thanks so much for your help!

 

[Jay Freedman]

You have my sympathies. Sometimes it seems these setups are designed
maliciously. :-(

Another thought... Is there some field or bookmark in your template that is
always filled last, or near the end of the system's processing? Possibly
your code could use that as the marker, instead of anything external. Once
it appears, sleep another few seconds and then launch your post-processing.

 

[C]

Aargh. I ran a simple test to execute a Find on a value the main app
fills-in in a particular template's new document. The logic was if the
find.execute was true, meaning the main app was done, then display a message
box with "True". If false, display "False", Sleep (5000) and then try again.
It dutifully looped the specified number of tries, each time reporting
"false" and sleeping. The disappointing factor is that the filling in of the
bookmarks by the main app dutifully "slept" as well. Alas, the Sleep did not
cause/force/allow the main app to complete.

I'm still sure the problem stems from my having to execute the code from an
AutoNew procedure which, by definition, runs first. I just wish there was a
DoEvents like with VB or something that would relinquish control to the main
app to fill in the bookmarks first.

Thanks again for all of your help. I know this one is like doing a jigsaw
puzzle with a piece missing.

 

[Howard Kaikow]

You could have your template test whether the app is still running in, say,
the Document_BeforeClose event or in an AutoClose macro or ...

 

[Howard Kaikow]

You can use API calls to verify whether the app is running.
At some point the app has to stop working on a particular document, so you
can use code in, say, the document_beforeclose event to test things.

 

[C]

I've been thinking about this. Since the main case management system is
always running and the Word document creation is just a reporting function
for it, I don't know how I could test for application completion. I did try
putting my code in an AutoClose procedure, but that requires the user to
initiate a close since again, if I initiate the close, it happens before the
main application code completes. If the user has to "click" something, I
would rather it be a custom continue button than a close. Feel free to tell
me if I'm missing something here.

Thank you!

 

[C]

I think I will try calling support for the company that wrote the case
management app and see if they will help on this. I think we need more to
work with than I have. Thank you both for your gracious help -- I'll post an
update when available.

Thanks again!

 

[Howard Kaikow]

Does not the 3rd party app have to open a Word doc to make changes?
If so, then they have to Close the doc before you can use it.

So, you can test whether a doc is already open.
Option Explicit

Private Sub TestIsFileOpen()
Dim strFile As String

strFile = "This file does not exist"
Debug.Print Chr$(34) & strFile & Chr$(34); IIf(IsFileOpen(strFile), " is
open", " is not open")
strFile = "C:\boot.ini"
Debug.Print Chr$(34) & strFile & Chr$(34); IIf(IsFileOpen(strFile), " is
open", " is not open")
strFile = "J:\Documents and Settings\Howard Kaikow\ntuser.dat.LOG"
Debug.Print Chr$(34) & strFile & Chr$(34); IIf(IsFileOpen(strFile), " is
open", " is not open")
End Sub

Private Function IsFileOpen(strFullyQualifiedFileName As String) As Boolean
Dim intFreeFile As Integer

intFreeFile = FreeFile

On Error Resume Next
Open strFullyQualifiedFileName For Binary Access Read Lock Read As
#intFreeFile
Close #intFreeFile

If Err.Number = 0 Then
IsFileOpen = False
Else
IsFileOpen = True
Err.Clear
End If
End Function

 

[C]

This really put me on the right track. I finally got a response from the
developer that said their process is to run the template, filling in the
bookmarks in the new document, then (believe it or not) cut the text out of
the document, close it, and paste it into a new document. They admit this
was done out of ignorance thinking that it would somehow prevent overwriting
the template. What it did to me was leave me with no template code available
to the new document.

What I have done after thinking about your open/close test is to use an
AutoClose procedure to attach the template to the unidentified document
before the developer can close the template. That gets all of my code into
the new document. I also copy over a custom toolbar with a button for the
user to press to activate the code in the new document. I would rather that
be automatic, but so far I haven’t figured out how to do that since the
original template is closed by the time the paste is done in the new document.

Anyway, we’re in business and I thank you for your help!

 

[Jane Duddy]

C, just in case you are still around, I need to do exactly the same thing as
you - is there any chance of you providing the AutoClose() code please?
Thanks