Off the record - Craig Box

If you have a document take 5-15 seconds at "Requesting virus scan..." in Microsoft Word, you will think "Pesky virus scanner!". But while this fault is most commonly Norton AntiVirus, it can also manifest in another circumstance: where you have a document with an attached template on a share you can't access any more.

If you are a Windows tech, you have no excuse for not knowing about Sysinternal's Filemon - when you know exactly what an application is accessing, it lets you solve a multitude of problems, including this one. We have a customer with an application that generates Word files based on a template, in the same location as the program is being run from. Long before our involvement they appear to have had a server named "Server" (imaginitive naming) and ran the program from \\Server\Share. There is no Server any more, so the documents from that period all cause a timeout trying to find a share that doesn't exist.
Thankfully, when their current server was installed, a drive mapping was created, so all the documents from that point on are attached to a template on G:\. Therefore the task becomes to search-and-replace the template on all the old Word documents.

There is no easier way to do this than using Word's VBA, which is unfortunate, as you have to script a load of each document, which means each one takes 5-15 seconds to process. There are third party utilities I've seen that handle Word automation, but you may as well just run a macro, which appears in its full splendour below the fold.

I'd be interested to know if there was a library that let you edit Word documents, so we could do this by another method.

Net result however, documents now open speedy, and customer is happy.

Almost all the programming I do I call "programming-by-example" - in this case, take examples off the Internet, and bend them to do what I need.

The biggest problem was the sample code in the KB article that describes the problem doesn't recurse, and my application had a directory for each separate job. Never mind, VBA can do that too:

• Documents that have attached templates take a long time to open
• Working with Files, Folders and Drives in VBA

Here's what I eventually came up with. You need to add a reference to the Microsoft Scripting Runtime to make it work for you.

Function GetFiles(strPath As String, _
          dctDict As Scripting.Dictionary, _
          Optional blnRecursive As Boolean) As Boolean
                  
    ' This procedure returns all the files in a directory into
    ' a Dictionary object. If called recursively, it also returns
    ' all files in subfolders.
    
    Dim fsoSysObj        As Scripting.FileSystemObject
    Dim fdrFolder        As Scripting.Folder
    Dim fdrSubFolder     As Scripting.Folder
    Dim filFile          As Scripting.File
    
    ' Return new FileSystemObject.
    Set fsoSysObj = New Scripting.FileSystemObject
    
    On Error Resume Next
    ' Get folder.
    Set fdrFolder = fsoSysObj.GetFolder(strPath)
    If Err <> 0 Then
        ' Incorrect path.
        GetFiles = False
        GoTo GetFiles_End
    End If
    On Error GoTo 0
    
    ' Loop through Files collection, adding to dictionary.
    For Each filFile In fdrFolder.Files
        dctDict.Add filFile.Path, filFile.Path
    Next filFile

    ' If Recursive flag is true, call recursively.
    If blnRecursive Then
        For Each fdrSubFolder In fdrFolder.SubFolders
            GetFiles fdrSubFolder.Path, dctDict, True
        Next fdrSubFolder
    End If

    ' Return True if no error occurred.
    GetFiles = True
    
GetFiles_End:
    Exit Function
End Function


Sub ChangeTemplatesRecursively()

    Dim objDoc As Document
    Dim objTemplate As Template
    Dim dlgTemplate As Dialog
    Dim strPath As String

    Dim dctDict As Scripting.Dictionary
    Dim varItem As Variant
    Dim strDirPath As String
    
    Dim OldServer As String
    Dim NewServer As String
    Dim NewTemplate As String
    
    OldServer = "\\\\Server\\"
    NewServer = "G:\\"
    
    strDirPath = "H:\\Catering quotes\\Functions held\\"
    ' Create new dictionary.
    Set dctDict = New Scripting.Dictionary
    ' Call recursively, return files into Dictionary object.
    If GetFiles(strDirPath, dctDict, True) Then
        ' Print items in dictionary.
        For Each varItem In dctDict
        If (InStrRev(LCase(varItem), "function sht") Or InStrRev(LCase(varItem), "function sheet")) Then
                Debug.Print "Looking at " + varItem + vbCrLf
                Set objDoc = Documents.Open(varItem)
                  Set objTemplate = objDoc.AttachedTemplate
                  Set dlgTemplate = Dialogs(wdDialogToolsTemplates)
                  strPath = dlgTemplate.Template
                  If LCase(Left(strPath, 9)) = LCase(OldServer) Then
                     NewTemplate = NewServer & Mid(strPath, 10)
                     Debug.Print "Setting template path in " + varItem + " to " + NewTemplate + vbCrLf
                      objDoc.AttachedTemplate = NewServer & Mid(strPath, 10)
                    End If
                
            objDoc.Save
            objDoc.Close
        End If
        Next
    End If
End Sub

Tags: office, programming, windows

This entry was posted on Monday, March 19th, 2007 at 12:09 pm and is filed under Technical, Work. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.