AutoUpdate VBA startup macro?

Question

I'm building some Word 2003 macro that have to be put in the %APPDATA%\Microsoft\Word\Startup folder.

I can't change the location of this folder (to a network share). How can I auto update this macros ?

I have tried to create a bootstrapper macro, with an AutoExec sub that copy newer version from a file share to this folder. But as Word is locking the file, I get a Denied Exception.

Any idea ?

FYI, I wrote this code. The code is working fine for update templates in templates directory, but not in startup directory :

' Bootstrapper module
Option Explicit

Sub AutoExec()
    Update
End Sub

Sub Update()

    MirrorDirectory MyPath.MyAppTemplatesPath, MyPath.WordTemplatesPath
    MirrorDirectory MyPath.MyAppStartupTemplatesPath, MyPath.WordTemplatesStartupPath

End Sub

' IOUtilities Module
Option Explicit

Dim fso As New Scripting.FileSystemObject

Public Sub MirrorDirectory(sourceDir As String, targetDir As String)
    Dim result As FoundFiles
    Dim s As Variant

    sourceDir = RemoveTrailingBackslash(sourceDir)
    targetDir = RemoveTrailingBackslash(targetDir)



    With Application.FileSearch
        .NewSearch
        .FileType = MsoFileType.msoFileTypeAllFiles
        .LookIn = sourceDir
        .SearchSubFolders = True
        .Execute
        Set result = .FoundFiles
    End With

    For Each s In result

        Dim relativePath As String
        relativePath = Mid(s, Len(sourceDir) + 1)

        Dim targetPath As String
        targetPath = targetDir + relativePath

        CopyIfNewer CStr(s), targetPath

    Next s

End Sub

Public Function RemoveTrailingBackslash(s As String)
    If Right(s, 1) = "\" Then
        RemoveTrailingBackslash = Left(s, Len(s) - 1)
    Else
        RemoveTrailingBackslash = s
    End If
End Function

Public Sub CopyIfNewer(source As String, target As String)
    Dim shouldCopy As Boolean
    shouldCopy = False
    If Not fso.FileExists(target) Then
        shouldCopy = True
    ElseIf FileDateTime(source) > FileDateTime(target) Then
        shouldCopy = True
    End If

    If (shouldCopy) Then
        If Not fso.FolderExists(fso.GetParentFolderName(target)) Then fso.CreateFolder (fso.GetParentFolderName(target))
        fso.CopyFile source, target, True
        Debug.Print "File copied : " + source + " to " + target
    Else
        Debug.Print "File not copied : " + source + " to " + target
    End If
End Sub

' MyPath module

Property Get WordTemplatesStartupPath()
    WordTemplatesStartupPath = "Path To Application Data\Microsoft\Word\STARTUP"
End Property

Property Get WordTemplatesPath()
    WordTemplatesPath = "Path To Application Data\Microsoft\Templates\Myapp\"
End Property


Property Get MyAppTemplatesPath()
    MyAppTemplatesPath = "p:\MyShare\templates"
End Property

Property Get XRefStartupTemplatesPath()
    MyAppStartupTemplatesPath = "p:\MyShare\startup"
End Property

[Edit] I explored another way

Another way I'm thinking about, is to pilot the organizer :

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/7/2011 by beauge
'
Application.OrganizerCopy source:="P:\MyShare\Startup\myapp_bootstrapper.dot", _
     Destination:= _
    "PathToApplication Data\Microsoft\Word\STARTUP\myapp_bootstrapper.dot" _
    , Name:="MyModule", Object:=wdOrganizerObjectProjectItems
End Sub

This is working, but has limitations :

• either I have to hard-code modules to organize
• or I have to change the option "Trust VBA project" to autodiscover items like this (which is not acceptable as it requires to lower the security of the station) :

the code of the project enumeration is this one :

Public Sub EnumProjectItem()
    Dim sourceProject As Document
    Dim targetProject As Document
    Set sourceProject = Application.Documents.Open("P:\MyShare\Startup\myapp_bootstrapper.dot", , , , , , , , , wdOpenFormatTemplate)
    Set targetProject = Application.Documents.Open("PathToApplication Data\Microsoft\Word\STARTUP\myapp_bootstrapper.dot", , , , , , , , , wdOpenFormatTemplate)
    Dim vbc As VBcomponent
    For Each vbc In sourceProject.VBProject.VBComponents 'crash here
        Application.ActiveDocument.Range.InsertAfter (vbc.Name + " / " + vbc.Type)
        Application.ActiveDocument.Paragraphs.Add

    Next vbc

End Sub

[Edit 2] Another unsuccessful try :

I put, in my network share, a .dot with all the logic.

In my STARTUP folder, I put a simple .Dot file, that references the former one, with a single "Call MyApp.MySub".

This is actually working, but as the target template is not in a trusted location, a security warning is popped up each time word is launched (even if not related to the current application macro)

Answer 1

My simple solution to this was to drop the updated file in the Windows Startup folder, along with a batch file that copies it to the Word\Startup folder and then self-destructs. Next time the user reboots their PC, the file is updated, and since it runs at startup, Word is very unlikely to be open in time to interfere. You can also just call a batch file / installer that then asks the user to close all open instances of Word before copying the file.

Answer 2

At least, I succeed partially using these steps :

1. Create a setup package. I use a NSIS script
   • the package detect any instance of Winword.exe and ask the user to retry when word is closed
   • extract from the registry the word's option path
   • deploy the files into the word's startup folder
   • add an uninstaller in the local user add/remove programs
2. I put the package in the remote share. I also added a .ini file containing the last version of the package (in the form "1.0")
3. In the macro itself, I have a version number ("0.9" for example).
4. At the startup (AutoExec macro), I compare the local version to the remote version
5. I use shell exec to fire the setup if a newer version is found.
6. The setup will wait for Word to close

A bit tricky, but it works on Word 2K3 and Word 2K10.