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.<\/p>\n
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.
\nThankfully, 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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Net result however, documents now open speedy, and customer is happy.<\/p>\n
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.<\/p>\n
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:<\/p>\n
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.<\/p>\n
\r\nFunction GetFiles(strPath As String, _\r\n dctDict As Scripting.Dictionary, _\r\n Optional blnRecursive As Boolean) As Boolean\r\n \r\n ' This procedure returns all the files in a directory into\r\n ' a Dictionary object. If called recursively, it also returns\r\n ' all files in subfolders.\r\n \r\n Dim fsoSysObj As Scripting.FileSystemObject\r\n Dim fdrFolder As Scripting.Folder\r\n Dim fdrSubFolder As Scripting.Folder\r\n Dim filFile As Scripting.File\r\n \r\n ' Return new FileSystemObject.\r\n Set fsoSysObj = New Scripting.FileSystemObject\r\n \r\n On Error Resume Next\r\n ' Get folder.\r\n Set fdrFolder = fsoSysObj.GetFolder(strPath)\r\n If Err <> 0 Then\r\n ' Incorrect path.\r\n GetFiles = False\r\n GoTo GetFiles_End\r\n End If\r\n On Error GoTo 0\r\n \r\n ' Loop through Files collection, adding to dictionary.\r\n For Each filFile In fdrFolder.Files\r\n dctDict.Add filFile.Path, filFile.Path\r\n Next filFile\r\n\r\n ' If Recursive flag is true, call recursively.\r\n If blnRecursive Then\r\n For Each fdrSubFolder In fdrFolder.SubFolders\r\n GetFiles fdrSubFolder.Path, dctDict, True\r\n Next fdrSubFolder\r\n End If\r\n\r\n ' Return True if no error occurred.\r\n GetFiles = True\r\n \r\nGetFiles_End:\r\n Exit Function\r\nEnd Function\r\n\r\n\r\nSub ChangeTemplatesRecursively()\r\n\r\n Dim objDoc As Document\r\n Dim objTemplate As Template\r\n Dim dlgTemplate As Dialog\r\n Dim strPath As String\r\n\r\n Dim dctDict As Scripting.Dictionary\r\n Dim varItem As Variant\r\n Dim strDirPath As String\r\n \r\n Dim OldServer As String\r\n Dim NewServer As String\r\n Dim NewTemplate As String\r\n \r\n OldServer = \"\\\\\\\\Server\\\\\"\r\n NewServer = \"G:\\\\\"\r\n \r\n strDirPath = \"H:\\\\Catering quotes\\\\Functions held\\\\\"\r\n ' Create new dictionary.\r\n Set dctDict = New Scripting.Dictionary\r\n ' Call recursively, return files into Dictionary object.\r\n If GetFiles(strDirPath, dctDict, True) Then\r\n ' Print items in dictionary.\r\n For Each varItem In dctDict\r\n If (InStrRev(LCase(varItem), \"function sht\") Or InStrRev(LCase(varItem), \"function sheet\")) Then\r\n Debug.Print \"Looking at \" + varItem + vbCrLf\r\n Set objDoc = Documents.Open(varItem)\r\n Set objTemplate = objDoc.AttachedTemplate\r\n Set dlgTemplate = Dialogs(wdDialogToolsTemplates)\r\n strPath = dlgTemplate.Template\r\n If LCase(Left(strPath, 9)) = LCase(OldServer) Then\r\n NewTemplate = NewServer & Mid(strPath, 10)\r\n Debug.Print \"Setting template path in \" + varItem + \" to \" + NewTemplate + vbCrLf\r\n objDoc.AttachedTemplate = NewServer & Mid(strPath, 10)\r\n End If\r\n \r\n objDoc.Save\r\n objDoc.Close\r\n End If\r\n Next\r\n End If\r\nEnd Sub\r\n<\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[66,68],"tags":[5,3,4],"_links":{"self":[{"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/posts\/97"}],"collection":[{"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/comments?post=97"}],"version-history":[{"count":1,"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/posts\/97\/revisions"}],"predecessor-version":[{"id":393,"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/posts\/97\/revisions\/393"}],"wp:attachment":[{"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/media?parent=97"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/categories?post=97"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/craig.dubculture.co.nz\/blog\/wp-json\/wp\/v2\/tags?post=97"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}