开发者

AutoUpdate VBA startup macro?

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.WordTemplatesSt开发者_运维技巧artupPath

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)


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜