VBA Event Questions

[ASkolnick]

Here is the predicament -

I have a template which has very little code, we will call it Main Document.
I also have an add-in template called Code.dot.

In the Auto-Open of the Main Document, it does the following:

1) Finds the network path of Code.dot
2) If the local copy is older, we want to replace it.
3) Attach the local copy of Code.dot to Main
4) Run AutoOpen of Code.dot.

We have two types of functions:
1) Saves it back into the patients chart.
2) Puts it in a "special" folder which parses the name and automatically puts it in the patients chart.


It works fine on the first run, but here is the problem. If the file retains the Code.dot, it locks that Code.dot template so it cannot be replaced.
I need to know the event where I can jettison the code, save it, and then have it automatically quit the application.

Currently, the quit is part of Code.dot, but that could probably be moved.

[Frosty]

I think you may need to post a dummy of your code. There are too many questions raised by your post, not the least of which include the concept of Events vs. the concept of AutoMacros.

[ASkolnick]

CODE FROM MAIN DOCUMENT

Const USER_FOLDERS_PATH = "C:\SRS\UserFolders.txt"
Const DEFAULT_SRS_FOLDER = "\srsserver"
Const DEFAULT_XML_PATH = "\www\SRSFreedom\FormsXML\"
Const DEFAULT_XML_FILE = "SRSFormsParameters.xml"
Const DEFAULT_CODE = "Code.dot"

Public Sub AutoOpen()
Dim msxmlpath As String
DoEvents
MAIN DOCUMENT
On Error Resume Next
Application.ScreenUpdating = False
ActiveDocument.Unprotect
msxmlpath = ActiveDocument.CustomDocumentProperties("XMLPath")
If Trim(msxmlpath) = "" Then ' if not found, try to get server name
msxmlpath = zLclSRSTopFolder & DEFAULT_XML_PATH '
End If
msxmlpath = zLclCleanPath(msxmlpath)

If FileLastModified("C:\SRS\" & DEFAULT_CODE) < FileLastModified(msxmlpath & DEFAULT_CODE) Then
Application.DisplayAlerts = wdAlertsNone
FileCopy msxmlpath & DEFAULT_CODE, "C:\SRS\" & DEFAULT_CODE
End If
ActiveDocument.VBProject.references.AddFromFile "C:\SRS\Code.dot"
'Delay seems to be needed
errlab:
Application.OnTime Now + TimeSerial(0, 0, 0.5), "RunCodeAutoOpen"
Application.ScreenUpdating = True
End Sub

Sub RunCodeAutoOpen()
Code.AutoOpen
End Sub

CODE DOCUMENT

Sub RoutingActions()
' This normally does a save and exit without returning.
'
' If the filename and path would be too long, it does return.
'
' Before calling this, set the following globals:
'
' gFormsParameters set to a clsParameters and supplies the polling folders
'
' Set the following properties as required:
'
' SendForm True saves in the folder for polling with messaging
' False saves in the folder for polling without messaging
'
' if <> "", the following override the default in Custom properties
' RecipientID, TabID, DocumentName, MessageBody
'
' NB, AutoOpen sets TabID to the <TestTab> value if this is present in the XML
'

Dim sFilePath As String ' full filename including the path but excluding extension

sFilePath = zSaveFilePath ' ***** NB, all the logic is in here *************

' if the path is too long, don't try to save -- return instead
' caller can read the length from FilePathLength
If Len(sFilePath) <= MaxFilePathLength Then ' allow 245 chars before adding extension

' This allows cmdUpdateAndExit to display when the form is reopened
PropertyWrite CP_DOCWASSAVED, "Yes" ' create the property

' save Excel workbook or Word document
ApplicationSave sFilePath

' Exit
ApplicationQuit

End If
End Sub

Private Function zSaveFilePath()
'
' Returns the full path and filename (without extension) for saving the file.
'
' IMPORTANT: For entry conditions -- see RoutingActions
' ==========
'
' This has been split out of RoutingActions in order to test the file path length
'
' See more comment in FilePathLength
'
Dim sFilePath As String ' full filename including the path but excluding extension

If SendForm Then

' TFname = TPath & TSharedID & "_" & .TabID & "_" & .DocumentName & "_" _
' & sRecipientID & "_" & .MessageBody & "_" & .MessageSubject & "_" & .MessagePriority

' Form the filename including the path
sFilePath = Pollwmsg & SharedID & "_" & TabID & "_" & DocumentName & "_" & RecipientID

' Append the msg body and the msg subject in the required order
If gFormsParameters.MsgBodyBeforeSubject Then
sFilePath = sFilePath & "_" & msBodyOrSender & "_" & MessageSubject
Else
sFilePath = sFilePath & "_" & MessageSubject & "_" & msBodyOrSender
End If
sFilePath = sFilePath & "_" & MessagePriority

Else

' save without send
' TFname = TPath & TSharedID & "_" & .TabID & "_" & .DocumentName
sFilePath = Pollwomsg & SharedID & "_" & TabID & "_" & DocumentName

End If

zSaveFilePath = sFilePath

End Function

Sub ApplicationSave(Optional sFileName)
'
' Save Excel workbook or current Word document
'
' If sFileName is supplied, the document is saved with the new name
' Otherwise, it is saved with existing name.
'
' 20-May-2010 if no extension is present, it is added explicitly.
' This is to fix a problem if the sharedid contains a period (otherwise, the
' application adds the extension automatically).
'
' 15-Oct-2010 the previous fix is generalized for posterity
'
Dim sMsg
Dim sDocExt

mbApplicationSaveInProgess = True ' see ApplicationSaveInProgess ()

ApplicationInputShaded False

' future proof version
If Not IsMissing(sFileName) Then
sDocExt = "." & ApplicationDocumentExtension
If Right(sFileName, Len(sDocExt)) <> sDocExt Then ' Apr 6, 2011 <> "sDocExt" Then
sFileName = sFileName & sDocExt
End If
End If

' This is to suppress spurious loss of digital signature msgs eg at Whitaker Wellness
If NoAlerts Then Application.DisplayAlerts = False

On Error GoTo errlab
#If ExcelForm Then
If IsMissing(sFileName) Then
ActiveWorkbook.Save
Else
' if filename supplied, must be a routing form -- so minimize while saving
' (can't minimize the edit window in SRS)
If DocumentIsNew Then Application.WindowState = xlMinimized
ActiveWorkbook.SaveAs sFileName
End If
#Else
If IsMissing(sFileName) Then
ActiveDocument.Save
Else
If DocumentIsNew Then Application.WindowState = wdWindowStateMinimize
ActiveDocument.SaveAs sFileName
End If
#End If
mbApplicationSaveInProgess = False
Exit Sub
errlab:
On Error GoTo errlab1
#If ExcelForm Then
Application.WindowState = xlNormal
#Else
Application.WindowState = wdWindowStateNormal
#End If
sMsg = "Error when saving document" & vbCrLf & vbCrLf & sFileName
MsgBox sMsg
mbApplicationSaveInProgess = False

Err.Clear

errlab1:
End Sub

[ASkolnick]

Pieces of the code have been loaded in the thread above.
The issue is all of the saving code exists in the add-in called Code.dot.
This has the code to save the document.

The problem boils down to the following:

If Code.dot remains attached to a document, we are not able to update code.dot from a master copy from the server because it is being held.

Therefore, we need to jettison code.dot attachment.

But if we jettison it before we save, it saves the document, but Word doesn't close and I need it to close automatically.

[Frosty]

For the sake of accurate nomenclature... you're loading a Reference to your Code.dot, not "attaching it" -- you could also "get access" to your code project if you set the .AttachedTemplate property of your ActiveDocument (not that I'm advocating that).

I assume you are launching Word by double-clicking on the document? Because triggering an Application.Quit from an AutoOpen can cause issues otherwise (i.e., if I already have Word open when I open this document, it's going to try and quit regardless).

It looks like you might be able to get away with just some basic garbage collection. Something along the lines of:
(requires a .dot called Tester.dot at your C:\ with a subroutine called "HelloWorld" in Module1, obviously)

Public Sub AddRefRunCodeRemoveRef()
Dim sProjectPath As String

sProjectPath = "C:\tester.dot"
'if it's already here, ignore the error when trying to re-add
On Error Resume Next
ActiveDocument.VBProject.references.AddFromFile sProjectPath
On Error GoTo 0
'you have a timer function here, that should be fine-- this was enough for my testing
DoEvents
'using application.run to avoid compile issues
Application.Run "Tester.Module1.HelloWorld"

'remove the reference
ActiveDocument.VBProject.references.Remove fVBProjectReference(ActiveDocument, sProjectPath)
End Sub
'pass in the path of the template, return the reference object
Public Function fVBProjectReference(oDoc As Document, sFullPath As String) As Object
Dim i As Integer
With oDoc.VBProject
For i = 1 To .references.Count
If .references(i).FullPath = sFullPath Then
'we have a match
Set fVBProjectReference = .references(i)
Exit For
End If
Next
End With
End Function


So just remove the reference when you're done whatever the reason is that you decided to implement it this way.

In general, the methodology you're using is a bad one (i.e., calling code, and then having the calling code exit all execution). You also see this problem, conceptually, with the use of the End command.

You always want to exit routines gracefully... so...

If your entry point is:
1. Word not running
2. user double-clicks document which contains Document.AutoOpen routine (which launches Word, and then subequently calls AutoOpen be default)
3. Document.AutoOpen routine executes code which loads a code template (after first checking for correct version)
4. Code template runs some code

Then you need to perform the reverse order of operations to do good "garbage collection" or you end up with dangling participles (of the code variety).

So the next steps would be...
5. Document.AutoOpen routine unloads the Code template
6. Document.AutoOpen quits Word

However, I disagree with this entire approach, as I can think of many ways to cause issues for an end-user... however, I don't know your end-users, and if they are always launching word, then you should close it.

I think you would get a more stable system by approaching this differently, and utilizing Global Addins in conjunction with a separate deployment strategy, but if this has worked for you... it should continue to work for you.

As you get to newer versions of Office, you're going to run into security problems with this approach, as what you're doing (automatically deploying code by virtue of opening a Word document) is exactly the methodology that a virus could employ. And you will explicitly need to add programmatic access to VBProject, the source to your trusted locations, etc. And at the point at which you're distributing this, it would be much easier (and safer) to close those security holes and simply distribute the most recent version of your template via login script/group policy or some other industry-standard enterprise delivery system.

So in concrete terms, you need the second function above to be added to your Main Document project, and then put something along the lines at the end of your AutoOpen routine in your Main Document.

errlab:
Application.OnTime Now + TimeSerial(0, 0, 0.5), "RunCodeAutoOpen"
Application.ScreenUpdating = True
ActiveDocument.VBProject.References.Remove fVBProjectReference(ActiveDocument, "C:\SRS\Code.dot")
Application.Quit


Hope this helps.

[ASkolnick]

Thanks for the information, but I may not have been clear of something.

Even though I am only calling the Reference from AutoOpen once, I cannot just remove the reference immediately. All of the functionality of the form is located in that Code.dot reference.

The entry point is not from Word, but a third party software which uses word.

[Frosty]

So the process is:

1. Third party software causes a Word or Excel file to be opened
2. Appropriate application is launched
3. A macro in that word/excel file runs, loading Code.dot into memory
4. The user interacts with the document for some unspecified length of time, some of which is in an uncontrolled manner (i.e., they aren't interacting with the document through forms you control)
5. At some point, the user runs code in Code.dot which has the desired effect of closing Word "cleanly"


Is that accurate?

I think you may need to talk to your 3rd party vendor, or, if that is you... you need to address the clean closing of the application from the same routine which launches the application, conceptually. Otherwise, you will often leave yourself exposed to an incomplete close/quit etc.

Other than that, it is fairly impossible to give you more than conceptual help without something more than concepts from you. You've posted a lot of code, but it's not like I can just plop that code onto my own machine and test it. I can read it and get a sense of what's happening, but without being able to demonstrate why the file is left in-use, it is difficult to say exactly what is going on.

If you can't use Application.Quit in the AutoOpen routine (after you've done everything else), I still think you're going to need to have it somewhere in the document which loaded the project reference. Maybe using applicationl.run from Code.dot to a publically exposed routine in Document.doc which serves the sole purpose of Application.Quit will work for you.

It's tough to tell. But you're describing a scenario in which you're relying on Word to do "good" garbage collection, and it is failing to do so. So, the trouble-shooting begins. In general, I begin that kind of trouble-shooting by attempting to take out my own trash.

[Frosty]

Alternatively, have you looked at using the Addins collection and loading an addin (instead of adding a reference). For example:

1. Your 3rd party app could do the test for the most recent version Code.dot *before* launching Word

2. It could place the most recent version of the Code.dot in the Word Startup directory and *then* launch Word.

I have a feeling turning your Code.dot into a global addin for the duration of the Word session (instead of temporarily loading it as a project reference to a specific document) would allow you to let Word do the garbage collection (it has an easier time unloading addins than it does dealing with references)

[ASkolnick]

I would like this approach but:

1) I don't have the ability to modify the third party software.
2) If I were to add it after the fact, is there anyway to "restart" Word?
3) We are doing this for multiple vendors, so we are unsure of where that startup path would be. But I am assuming some VBA code could look it up.

I have appreciated all your help to this point so far.

[Frosty]

Oh, multiple vendors and you don't have total control of the environment? Well, I think you're in trouble then. Because you're starting to look more and more like a virus (not that you are a virus, but you're behaving like one: self-deploying code is one of the hallmarks of a virus, right?).

This approach could have worked 10 years ago, but self-deploying code (especially code which adds references to other code projects) is generally forced to jump through a series of "trust" hoops these days. If you don't already have those hoops in place (trust certificates, etc) and are trying to deploy in a mom-and-pop fashion, it's only going to get harder and harder as your clients upgrade their OS, Office version and virus-protection software.

You can definitely look up startup path from vba code, but I really don't think that's your main problem. You're trying to do a number of different things, and this problem will probably only get more complex the more you look at it.

My recommendation is two-fold:
1. separate out your deployment strategy from the rest of your functionality (since that seems to be the main issue you're currently having), and either do something simple (create a .bat file which updates it, and then hand that .bat file to your clients and tell them to run it every time the end-user reboots the computer) or look up something like KixScript for a more robust login-script type thing

2. Hire a programmer to look through your code and start cleaning up your process. If you're running into garbage collection issues, I suspect you've never sat down with anything but a self-taught programmer, and you might be paying the price now. Not that self-taugh programmers are bad (I'm one too!), but you can only get so far without understanding some of the underlying concepts, and I believe you've reached that point.

I'm not soliciting the work for myself (I'm plenty busy), but there are several very knowledgable people on this site who could probably help and may also be interested. But if you're a business, I think it will benefit you to invest some in your process.