Automated replacement of Word template paths

Topic

New Reply

Hi All

We have just changed from an old Novell Server to a Windows Server. As a direct consequence, all of our thousands of Word documents have the path to the templates which resided on the Novell Server, accordingly they each take minutes to open.

I would be very grateful if someone could tell me if there is an automated routine that would replace the start of the string that comprises the embedded template in each Word document.

Specifically, it would need to replace \TSBVol1 with H:

I envisaged starting this routine in a directory and having it make these replacements in each Word document in each of the many sub-directories.

Can this be done?

I have seen suggestions here of running a routine when the file is opened, but this would be ‘mind numbing’ and take many weeks, as there are thousands of files involved.

I would be very grateful for your advice.

Regards
Useful :confused:

Reply | Quote

Replies

To start off, can you leave the old Novell Server up for a while so the files do open correctly?

Maybe you could create a virtual server with the same name and path as the old one to be your Template directory?

I kept the old server name to prevent this problem. An autostart macro may not prevent the time delay, but could fix each file as it is used.

Reply | Quote

Thanks for the suggestion BigMac56, I will investigate whether it is possible to set up a virtual server.

Regards
Useful

Reply | Quote

See: http://support.microsoft.com/?kbid=830561

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul, thank you for your response. I saw this support article and option 3 looks like the closest one to what I want to achieve, but it would need to be modified to loop through all of the Word files in all of the sub-directories of the main folder that it is started in, specifically replacing the path \TSBVol1 with H: in all of the attached templates, if found (there are Word files that have the Normal.dot template attached).

I would be happy to pay for such a modification, given that I don’t know how to do it myself.

Regards
Useful

Reply | Quote

Try the following:

Code:

Option Explicit
Public FSO As Object 'a FileSystemObject
Public oFolder As Object 'the folder object
Public oSubFolder As Object 'the subfolders collection
Public oFiles As Object 'the files object
Public i As Long, j As Long

Sub UpdateServer()
' Minimise screen flickering
Application.ScreenUpdating = False
Dim StrFolder As String
' Browse for the starting folder
StrFolder = GetTopFolder
If StrFolder = "" Then Exit Sub
' Search the top-level folder
Call GetFolder(StrFolder & "")
' Search the subfolders for more files
Call SearchSubFolders(StrFolder)
' Return control of status bar to Word
Application.StatusBar = ""
' Restore screen updating
Application.ScreenUpdating = True
MsgBox i & " of " & j & " files updated.", vbOKOnly
End Sub

Function GetTopFolder() As String
GetTopFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetTopFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Sub SearchSubFolders(strStartPath As String)
If FSO Is Nothing Then
  Set FSO = CreateObject("scripting.filesystemobject")
End If
Set oFolder = FSO.GetFolder(strStartPath)
Set oSubFolder = oFolder.subfolders
For Each oFolder In oSubFolder
  Set oFiles = oFolder.Files
  ' Search the current folder
  Call GetFolder(oFolder.Path & "")
  ' Call ourself to see if there are subfolders below
  SearchSubFolders oFolder.Path
Next
End Sub

Sub GetFolder(StrFolder As String)
Dim strFile As String
strFile = Dir(StrFolder & "*.doc")
' Process the files in the folder
While strFile  ""
  ' Update the status bar is just to let us know where we are
  Application.StatusBar = StrFolder & strFile
  Call UpdateTemplateRefs(StrFolder & strFile)
  strFile = Dir()
Wend
End Sub

Sub UpdateTemplateRefs(strDoc As String)
Dim OldServer As String, NewServer As String, Dlg As Dialog, Doc As Document, Tmplt As Template, strPath As String
OldServer = "": NewServer = "H:"
' Open the document
Set Doc = Documents.Open(strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto, Visible:=False)
With Doc
  If .ProtectionType = wdNoProtection Then
    ' Update the template path
    Set Tmplt = .AttachedTemplate
    Set Dlg = Dialogs(wdDialogToolsTemplates)
    strPath = Dlg.Template
    If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
      .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      ' Update the file counter for changed files
      i = i + 1
    End If
  End If
  ' Update the main file counter
  j = j + 1
  .Close SaveChanges:=True
End With
' Let Word do its housekeeping
DoEvents
Set Doc = Nothing: Set Tmplt = Nothing: Set Dlg = Nothing
End Sub

Note: Test it on a folder containing just a couple of files first – you may yet need to change the path variables. Note also that the code doesn’t process protected files and that mailmerge main documents and other documents containing macros may cause problems, due to their own prompts.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thank you for the code Paul. I will of course try it today as you suggested.

To those of us a lot less gifted, could you please tell me where to put this code and how to run it?

Regards
Useful

Reply | Quote

For installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

Thank you again for your generous help.

I created a Test folder on our new server with a single Word file in it, with the old path to the attached Template, as you suggested.

I then copied and pasted your code into a new macro and ran it, selecting the required Test folder. The code ran, displaying ‘Contacting \TSBVol1DocGenWord2002 press Esc to cancel’ on the status bar, then displayed ‘Contacting \TSB press Esc to cancel’ and then eventually it finished, displaying ‘0 of 1 files updated’.

I then created a sub-directory under Test and put the Word file into that, thinking that the code needed it, but it still failed.

Although the only difference in the Template path is that \TSBVol1 becomes H:, I then tried to modify the OldServer and NewServer values in the code, eventually entering the whole path \TSBVol1DocGenWord2002 where the Templates are and repeating it with H:DocGenWord2002 but it still failed.

I would be grateful if you could look at the code and hopefully find what needs to be changed.

Regards
Useful

Reply | Quote

Hi useful,

In the code, you’ll see the line ‘OldServer = “”: NewServer = “H:”‘. It’s critical that both server names be correct; otherwise, either the old one won’t be replaced or the new one will be invalid too.

Previously, you mentioned that the old server’s name was ‘\TSBVol1’, which I’ve encoded as ‘OldServer = “”‘. I based that on the MS example, but you may need to delete the chevrons.

Also, it seems that what you provided before wasn’t the full template path. If it’s going to work, the code needs the full paths for both the old and new templates. Conversely, if it’s only part of the path that has changed and there are various branches above that to various templates, all of which you’re trying to update references to simultaneously, the code would need further modification. Even so, you’re going to encounter a message like ‘Contacting \TSBVol1 press Esc to cancel’, simply because the old server can’t be found (unless you temporarily re-connect it).

As for the new server’s path to the templates, I suggest trying to update one document’s template path manually, then checking how that is depicted in Word.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

Clearly I have not understood. I thought that you could just write code that would replace one string with another, hence my request of replacing \TSBVol1 with H:

We have changed from a Novell Server to a Windows Server. The Novell Server was called \TSB and it had Volumes, as Novell does.

The Templates were located on Vol1 in the directory DocGenWord2002, the full UNC path being \TSBVol1DocGenWord2002, which I have noticed, is the path of the attached template currently in each Word file. This is what we need to change.

We have kept the file structures that same, but as Windows does not have Volumes, Vol1 has become the H: drive.

Accordingly, on the new Windows Server, the path to the templates is H:DocGenWord2002, whilst it was \TSBVol1DocGenWord2002.

If a full UNC path is required for the new server as well, then the templates are now located in \TSLSERVERFilesDocGenWord2002

We are not able to re-connect the old Novell Server and when I manually attached a template to a new document, the Properties just showed the name of the template, not its path.

I have tried removing the chevrons, but it still fails.

The key to this I think is the line ‘Old Server’ … ‘New Server’ but I’m not sure what this line should be.

Given the above information, I would be grateful if you could give me that line in the code, because I may not have tried a particular combination.

Thank you.

Regards
Useful

Reply | Quote

Sorry – a bit of mis-direction on my part. The adapted code does replace just part of the path. It seems that correctly identifying the path to be replaced is the issue. To that end, you can confirm the attached template path for a given document using:

Code:

Sub Test()
MsgBox ActiveDocument.AttachedTemplate.Path
End Sub

Based on your latest advice, it appears the new server should be specified as ‘NewServer = “\TSLSERVERFiles”‘

An alternative approach is to change the ‘UpdateTemplateRefs’ macro to:

Code:

Sub UpdateTemplateRefs(strDoc As String)
Dim OldServer As String, NewPath As String, Doc As Document
OldServer = "\TSBVol1": NewPath = "\TSLSERVERFilesDocGenWord2002"
' Open the document
Set Doc = Documents.Open(strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto, Visible:=False)
With Doc
  If .ProtectionType = wdNoProtection Then
    If LCase(Left(.AttachedTemplate.Path, Len(OldServer))) = LCase(OldServer) Then
      .AttachedTemplate = NewPath & "" & .AttachedTemplate.Name
      ' Update the file counter for changed files
      i = i + 1
    End If
  End If
  ' Update the main file counter
  j = j + 1
  .Close SaveChanges:=True
End With
' Let Word do its housekeeping
DoEvents
Set Doc = Nothing
End Sub

(using, of course, the template server name & volume returned by the ‘Test’ macro).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I ran your Test macro on the old test Word document and it returned the Template Path as \TSBVol1DocGenWord2002. I then confirmed this using File | Options | AddIns | Manage Templates and it was the same.

I repeated this on a new Word document and the path was H:DocGenWord2002.

I then performed a series of tests, running the original UpdateServer code:

1. OldServer = “” and NewServer = “” – failed.
2. OldServer = “” and NewServer = “” – failed.
3. OldServer = “\TSBVol1” and NewServer = “H:” – failed.
4. OldServer = “\TSBVol1DocGenWord2002″ and NewServer = “H:DocGenWord2002” – failed.
5. OldServer = “” and NewServer = “” – failed.
6. OldServer = “” and NewServer = “” – failed.
7. OldServer = “\TSBVol1” and NewServer = “\TSLSERVERFiles” – failed.
8. OldServer = “\TSBVol1DocGenWord2002″ and NewServer = “\TSLSERVERFilesDocGenWord2002” – failed.

I then tried the new ‘UpdateTemplateRefs’ macro and did the following tests:

9. OldServer = “” and NewPath = “” – failed.
10. OldServer = “” and NewPath = “” – failed.
11. OldServer = “\TSBVol1” and NewPath = “H:” – failed.
12. OldServer = “\TSBVol1DocGenWord2002″ and NewPath = “H:DocGenWord2002” – failed.
13. OldServer = “” and NewPath = “” – failed.
14. OldServer = “” and NewPath = “” – failed.
15. OldServer = “\TSBVol1” and NNewPath “\TSLSERVERFiles” – failed.
16. OldServer = “\TSBVol1DocGenWord2002″ and NewPath = “\TSLSERVERFilesDocGenWord2002” – failed.

What do you suggest? Is there a combination I have not tried?

Thank you for your continuing generous assistance.

Regards
useful

Reply | Quote

Step through the macro by pressing F8 for each line. Hover the mouse over pieces of the code to see what the values are. I would be particularly interested in the following line both before and after it executes

.AttachedTemplate = NewPath & “” & .AttachedTemplate.Name
Note that line that opens the word document is likely to take minutes to work for each document. You could drastically speed this up if you renamed one of your machines on the network so that the old address was valid for the duration of the macro running.

I assume you didn’t rename the old templates to the newer formats (which have a different suffix) because that would certainly cause the macro to fail.

Reply | Quote

I’d suggest trying:

Code:

Sub UpdateTemplateRefs(strDoc As String)
Dim OldPath As String, NewPath As String, Doc As Document
OldPath = "\TSBVol1DocGenWord2002": NewPath = "H:DocGenWord2002"
' Open the document
Set Doc = Documents.Open(strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto, Visible:=False)
With Doc
  If .ProtectionType = wdNoProtection Then
    If .AttachedTemplate.Path = OldPath Then
      .AttachedTemplate = NewPath & "" & .AttachedTemplate.Name
      ' Update the file counter for changed files
      i = i + 1
    End If
  End If
  ' Update the main file counter
  j = j + 1
  .Close SaveChanges:=True
End With
' Let Word do its housekeeping
DoEvents
Set Doc = Nothing
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul and Andrew

Thank you very much for your inputs. I really appreciate your continued generosity.

Paul, I substituted your suggested code change, but it still failed unfortunately, so I followed Andrew’s suggestion and stepped through the macro with F8 and was very surprised that the value for .AttachedTemplate in the code below:

If .AttachedTemplate.Path = OldPath Then
.AttachedTemplate = NewPath & “” & .AttachedTemplate.Name

was Normal.dotm, instead of the template that is actually attached to the Word 2003 document the macro found. As I am using Word 2010 to run this macro and all of the documents I hope to replace the template in are Word 2003, which does not use .dotm, I guess it is picking up the attached template to the blank Word 2010 document that I am running the UpdateServer macro from.

This I suppose is the reason it has never worked.

What am I doing wrong?

Regards
useful

Reply | Quote

Hi useful,

The code is specifically telling Word to find the template attached to the document it has just opened, not to template attached to the document running the code, so I don’t understand how Normal.dotm could possibly be referenced. What do you see if you insert:
MsgBox .AttachedTemplate.Fullname
before:
If .AttachedTemplate.Path = OldPath Then

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I inserted the code you sent and can confirm that it is definitely picking up the template attached to the document where it is running from – in other words the Normal.dotm (it displays the full path AppDataRoamingMicrosoftTemplatesNormal.dotm), rather than the document it has just opened.

It makes no sense to me either, but I don’t know how to modify the code to fix it. Clearly this is why it failed consistently during my previous testing.

Regards
useful

Reply | Quote

Hi useful,

Try deleting: ‘, Visible:=False’, while retaining ‘MsgBox .AttachedTemplate.Fullname’.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I have deleted ‘Visible = False’, but it made no difference. The Message Box still displays the local Normal.dotm, with the full path AppDataRoamingMicrosoftTemplatesNormal.dotm, rather than the document that has just been opened.

Regards
useful

Reply | Quote

Are you sure that the opened document’s template path isn’t (hasn’t been changed to) that path & template?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I am very confused.

I have opened the test document and get the ‘Contacting \TSBVol1DocGenWord2002′ message, then I check using File | Options | Add-ins | Manage Templates and get the template path of \TSBVol1DocGenWord2002, but when I run your code MsgBox ActiveDocument.AttachedTemplate.Path it shows the path to the local Normal.dotm template, AppDataRoamingMicrosoftTemplates.

I then tested a number of Word documents that have not been involved in the testing and I get the same results. All of these files display the ‘Contacting \TSBVol1DocGenWord2002′ message during the opening stage, then eventually time out when they can’t find the server. When I look at the attached template in Word (as I set out above) it displays the old Novell path, but when I run your Test code, I get the path to the local Normal.dotm template.

Regards
useful (aka George)

Reply | Quote

Hi George,

In post #13, you said:

I ran your Test macro on the old test Word document and it returned the Template Path as \TSBVol1DocGenWord2002. I then confirmed this using File | Options | AddIns | Manage Templates and it was the same.

Running the larger macro I provided does nothing to change that (before updating to ne new path). Your ‘AppDataRoamingMicrosoftTemplates’ path and ‘Normal.dotm’ are never referenced, so I can’t see how it’s possible to get such results.

Do you have a couple of (not sensitive – you can delete all the content if you wish) documents referencing the old path that you can attach to a post for me to do some testing with?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I’m grateful that you will look at the files … it really makes no sense to me, but perhaps I have done too much testing and I’m just confused.

There are two documents, as you requested:

Test1.doc is the test document I have been running the code on;
Test2.doc is one of the documents from the Novell server that has not been involved in the process.

Thank you.

Regards
George

Reply | Quote

Except for a very few rare cases, there is absolutely nothing contained in the original template that ever needs to be accessed. I’ve got a short piece of code here that will run on Open, if inserted into your “Normal” document template, and just deletes the path to the template. This will automatically be replaced with the path to “Normal”, no need to check what’s already there. Maybe someone can work it into a looping macro, that’s a bit beyond my skillset.

Code:

Private Sub Document_Open()

‘ ClearTemplatePath Macro
‘
‘
    With ActiveDocument
        .UpdateStylesOnOpen = False
        .AttachedTemplate = “”
        .XMLSchemaReferences.AutomaticValidation = True
        .XMLSchemaReferences.AllowSaveAsXMLWithoutValidation = False
    End With
End Sub

Which version of Word are you using? With Word 2010, if you open a template from Windows Explorer, you can’t “hurry” the opening process along, but if you open it from within Word, you can press Escape to skip the document search.

Reply | Quote

Except for a very few rare cases, there is absolutely nothing contained in the original template that ever needs to be accessed.

Those “very few rare cases” are a lot more common than you might think. For example, they apply to any document whose template runs a Document_Open macro or an event-driven macro and to any document that has the ‘Automatically update document styles’ attribute set.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi George,

What your attachments show is that, aside from the volume names, the paths to the template folders differ, so we’ll need to use code that replaces just the changed part of the path.
Test1 = \TSBVOL1DocGenWord2002Coloured Letter_02.dot
Test2 = \TSBVOL1DocGenWord97Coloured Letter.dot

The question then becomes one of correctly encoding the old & new server names. In my testing, I’ve confirmed that you can indeed use:
OldServer = “\TSBVOL1”
Although I can’t test it (because Word won’t allow me to change to a non-existant attached template path) you should also be able to use:
NewServer = “\TSLSERVERFiles”
or, perhaps:
NewServer = “H:Files”

I’ve also confirmed that, notwithstanding the dialogue’s contents, if an attached template can’t be found, code like ‘MsgBox ActiveDocument.AttachedTemplate.Fullname’ returns only the Normal template – one needs to query the dialogue explicitly for it’s interpretation of the attached template.

What I suggest you do is to open one of the test files on a PC attached to your network and run the following code:

Code:

Sub Test()
Dim OldServer As String, NewServer As String, Dlg As Dialog, Doc As Document, strPath As String
OldServer = "\TSBVOL1": NewServer = "\TSLSERVERFiles"
With ActiveDocument
  ' Update the template path
  Set Dlg = Dialogs(wdDialogToolsTemplates)
  strPath = Dlg.Template
  MsgBox .AttachedTemplate.FullName & vbCr & strPath
  If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
    MsgBox strPath & vbCr & NewServer & Mid(strPath, Len(OldServer) + 1)
    .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
  End If
End with
End Sub

Word will either update the template path or report an error, in which case, try changing the ‘NewServer’ details. Once you’ve got it working re-run the full-blown macro with the updated ‘NewServer’ details in:

Code:

Sub UpdateTemplateRefs(strDoc As String)
Dim OldServer As String, NewServer As String, Dlg As Dialog, Doc As Document, strPath As String
OldServer = "\TSBVOL1": NewServer = "\TSLSERVERFiles"
' Open the document
Set Doc = Documents.Open(strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto, Visible:=False)
With Doc
  If .ProtectionType = wdNoProtection Then
    ' Update the template path
    Set Dlg = Dialogs(wdDialogToolsTemplates)
    strPath = Dlg.Template
    If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
      .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      ' Update the file counter for changed files
      i = i + 1
    End If
  End If
  ' Update the main file counter
  j = j + 1
  .Close SaveChanges:=True
End With
' Let Word do its housekeeping
DoEvents
Set Doc = Nothing: Set Dlg = Nothing
End Sub

(the above is a slightly simplified version of the original)

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

Thank you very much for the new codes. You are really great for doing this.

I opened a document with the Novell path to the template and ran the ‘Test’ code above, as you suggested, and after I put in the required ‘End With’ into the code it worked fine! I then replaced the ‘UpdateTemplateRefs’ section of the original code with the one above and ran it, but it failed.

It is quite possible that I misunderstood something, otherwise there must be some vital difference between the two code sections.

I would be very grateful if you could have a look.

Thank you.

Regards
George

Reply | Quote

Hi George,

Did you do a manual check or a re-run the test code against your test document to confirm that the template path had in fact been changed?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I firstly opened the document to confirm that it displayed the ‘Contacting \TSBVol1DocGenWord2002′ and took over 30 seconds to open.

I then ran the ‘Test’ code you so kindly sent and after it ran, I checked the path to the template and it had indeed been correctly modified to the current location, in this case \TSLSERVERFilesDocGenWord2002Coloured Letter_02.dot

Then as a further test, I opened the document and it opened straight-away, without the ‘Contacting \TSBVol1DocGenWord2002′ being displayed for about 30 seconds.

I am wondering though that as we know that the ‘OldServer’ path no longer exists, if there is any way of eliminating the time for Word to time-out searching for this non-existent path and just replace the path embedded in the file from ‘OldServer’ to ‘NewServer’?

Thank you very much again.

Regards
George

Reply | Quote

Hi George,

Sadly, unless you can temporarily reconnect the old server, I doubt you’ll be able to reduce the file opening delay while the macro runs.

Since the template path seems to update correctly when referencing ‘ActiveDocument’, try the code with:

Code:

Sub UpdateTemplateRefs(strDoc As String)
Dim OldServer As String, NewServer As String, strPath As String
OldServer = "\TSBVOL1": NewServer = "\TSLSERVERFiles"
' Open the document
Documents.Open strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto
With ActiveDocument
  If .ProtectionType = wdNoProtection Then
    ' Update the template path
    strPath = Dialogs(wdDialogToolsTemplates).Template
    If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
      .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      ' Update the file counter for changed files
      i = i + 1
    End If
  End If
  ' Update the main file counter
  j = j + 1
  .Close SaveChanges:=True
End With
' Let Word do its housekeeping
DoEvents
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I am very grateful for your untiring assistance with the resolution of this problem (it’s only a shame that you could not achieve a ‘find and replace’ without the lengthy delay on each file).

If ever I could reciprocate somehow, please let me know.

Your eternally grateful fan,
George

Reply | Quote

Hi George,

Am I take it from your last post that the code’s working now?

As for reciprocation, you never know when your chance may come. Who knows what ‘problem’ I might encounter (computing or otherwise), that you’ll be able to help me with some day. And even if that day never comes, you can reciprocate by helping others, on the principle that ‘what goes around comes around’.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

Yes, the code now works, thanks to your tireless efforts. The trick was to insert ‘ActiveDocument’, as the template path updated correctly when referencing ‘ActiveDocument’.

You are a very generous person with your time and I just wanted to acknowledge that and to let you know that I’m very grateful and I’m not taking it for granted!

I cannot imagine how I could reciprocate, but I am willing to try whenever you have a need.

Thanks again.

Regards
George

Reply | Quote

Hi Paul

During further testing of the macro, I discovered an issue that I would be very grateful for your help with.

When a template file is not found (as may be the case with very old templates), the process stops and the message that it can’t find the file is displayed. As I will be running this macro over-night and over the weekend, I would be very grateful if you could modify the code so that if a template is not found, the Word file is just skipped, but the processing continues, without displaying anything.

Thank you very much.

Regards
George

Reply | Quote

Hi George,

Try:

Code:

Sub UpdateTemplateRefs(strDoc As String)
Dim OldServer As String, NewServer As String, strPath As String
OldServer = "\TSBVOL1": NewServer = "\TSLSERVERFiles"
' Open the document
Documents.Open strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto
With ActiveDocument
  If .ProtectionType = wdNoProtection Then
    ' Update the template path
    strPath = Dialogs(wdDialogToolsTemplates).Template
    If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
      On Error GoTo ErrRpt
      .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      ' Update the file counter for changed files
      i = i + 1
      GoTo OK
ErrRpt:
    ThisDocument.Range.InsertAfter vbCr & "Template: " & NewServer & Mid(strPath, Len(OldServer) + 1) & _
      " not found for " & strDoc
      .AttachedTemplate = ""
    On Error GoTo 0
OK:
    End If
  End If
  ' Update the main file counter
  j = j + 1
  .Close SaveChanges:=True
End With
' Let Word do its housekeeping
DoEvents
End Sub

With the above mods, you should get an error report in the document you run the macro from and the errant document’s attached template should be deleted – resetting it to Normat.dot(m).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

You are amazing and so prompt! Thank you very much.

I have made the changes to the code and tested it. It now does not stop when it can’t find an old template, which is great!! At the end, it reports the file count, which is useful, it replaces the missing old template with the Normal template, which is fine, but there is no error report (not that it matters).

You are wonderful!!!!

Regards
George

Reply | Quote

there is no error report (not that it matters

In that case, I wonder if the error report is going to the file being updated – in which case it probably does matter. In that case, you should either delete:

Code:

ThisDocument.Range.InsertAfter vbCr & "Template: " & NewServer & Mid(strPath, Len(OldServer) + 1) & _
      " not found for " & strDoc

or, if you want the error report, use:

Code:

Option Explicit
Public FSO As Object 'a FileSystemObject
Public oFolder As Object 'the folder object
Public oSubFolder As Object 'the subfolders collection
Public oFiles As Object 'the files object
Public i As Long, j As Long, Doc As Document

Sub UpdateServer()
' Minimise screen flickering
Application.ScreenUpdating = False
Set Doc = ThisDocument
Dim StrFolder As String
' Browse for the starting folder
StrFolder = GetTopFolder
If StrFolder = "" Then Exit Sub
' Search the top-level folder
Call GetFolder(StrFolder & "")
' Search the subfolders for more files
Call SearchSubFolders(StrFolder)
' Return control of status bar to Word
Application.StatusBar = ""
' Restore screen updating
Application.ScreenUpdating = True
MsgBox i & " of " & j & " files updated.", vbOKOnly
Set Doc = Nothing
End Sub

Function GetTopFolder() As String
GetTopFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetTopFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Sub SearchSubFolders(strStartPath As String)
If FSO Is Nothing Then
  Set FSO = CreateObject("scripting.filesystemobject")
End If
Set oFolder = FSO.GetFolder(strStartPath)
Set oSubFolder = oFolder.subfolders
For Each oFolder In oSubFolder
  Set oFiles = oFolder.Files
  ' Search the current folder
  Call GetFolder(oFolder.Path & "")
  ' Call ourself to see if there are subfolders below
  SearchSubFolders oFolder.Path
Next
End Sub

Sub GetFolder(StrFolder As String)
Dim strFile As String
strFile = Dir(StrFolder & "*.doc")
' Process the files in the folder
While strFile  ""
  ' Update the status bar is just to let us know where we are
  Application.StatusBar = StrFolder & strFile
  Call UpdateTemplateRefs(StrFolder & strFile)
  strFile = Dir()
Wend
End Sub

Sub UpdateTemplateRefs(strDoc As String)
Dim OldServer As String, NewServer As String, strPath As String
OldServer = "\TSBVOL1": NewServer = "\TSLSERVERFiles"
' Open the document
Documents.Open strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto
With ActiveDocument
  If .ProtectionType = wdNoProtection Then
    ' Update the template path
    strPath = Dialogs(wdDialogToolsTemplates).Template
    If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
      On Error GoTo ErrRpt
      .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      ' Update the file counter for changed files
      i = i + 1
      GoTo OK
ErrRpt:
    Doc.Range.InsertAfter vbCr & "Template: " & NewServer & Mid(strPath, Len(OldServer) + 1) & _
      " not found for " & strDoc
      .AttachedTemplate = ""
    On Error GoTo 0
OK:
    End If
  Else
    Doc.Range.InsertAfter vbCr & strDoc & " protected. Not updated."
  End If
  ' Update the main file counter
  j = j + 1
  .Close SaveChanges:=True
End With
' Let Word do its housekeeping
DoEvents
End Sub

Note the bonus additional reporting for files not updated.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

Thank you for the quick response.

I am running the first over-night job now, so I can’t check to see where the error report is going, but I will check in the morning and make the suggested changes.

Thank you very much again. You are great!!!

Regards
George

Reply | Quote

Hi Paul

Firstly, an error report is generated, but I just didn’t see it at first, because you have to open a ‘New’ document to see it. If you just exit Word (as I did early on) you don’t see the error report. It’s perfectly fine how it is, now that I know.

Now the major and unforseen (by me) problem!

All files that had the template path replaced, now have a modified date of either last night (27 Nov) or today (28 Nov) depending on when they had the template path replaced. All of the original file dates are gone. Is there any way of getting them back?

I had a look at the file properties and inexplicably, the file creation date has no bearing on the real date that a file was created. For instance, a letter that was written on 20 May 2010, has a Date Created of 16 October 2012.

However, the Date Printed has the correct date and time.

Is it possible to look at all the files with a template path starting with \TSLSERVERFiles (the letters that were modified) and have their modified date and time replaced with the Date Printed date and time?

It would be wonderful if this were possible.

Regards
George

Reply | Quote

Hi Paul

I had had a further look and the Date Printed is unreliable. The field Content Created seems to match much better.

Regards
George

Reply | Quote

Hi George,

For the files yet to be processed, try:

Code:

Option Explicit
Dim FSO As Object 'a FileSystemObject
Dim oFolder As Object 'the folder object
Dim oSubFolder As Object 'the subfolders collection
Dim oFiles As Object 'the files object
Dim i As Long, j As Long
Dim oShell As Object, oFldr As Object, oItem As Object, StrDtTm As String
Dim StrFolder As String, strFile As String

Sub Main()
' Minimise screen flickering
Application.ScreenUpdating = False
' Browse for the starting folder
StrFolder = GetTopFolder
If StrFolder = "" Then Exit Sub
' Create a Shell object
Set oShell = CreateObject("Shell.Application")
i = 0: j = 0
' Search the top-level folder
Call GetFolder(StrFolder & "")
' Search the subfolders for more files
Call SearchSubFolders(StrFolder)
' Return control of status bar to Word
Application.StatusBar = ""
' Erase the Shell
Set oShell = Nothing
' Restore screen updating
Application.ScreenUpdating = True
MsgBox i & " of " & j & " files updated.", vbOKOnly
End Sub

Function GetTopFolder() As String
GetTopFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetTopFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Sub SearchSubFolders(strStartPath As String)
If FSO Is Nothing Then
  Set FSO = CreateObject("scripting.filesystemobject")
End If
Set oFolder = FSO.GetFolder(strStartPath)
Set oSubFolder = oFolder.subfolders
For Each oFolder In oSubFolder
  Set oFiles = oFolder.Files
  ' Search the current folder
  Call GetFolder(oFolder.Path & "")
  ' Call ourself to see if there are subfolders below
  SearchSubFolders oFolder.Path
Next
End Sub

Sub GetFolder(StrFolder As String)
strFile = Dir(StrFolder & "*.doc")
' Process the files in the folder
While strFile  ""
  ' Update the status bar is just to let us know where we are
  Application.StatusBar = StrFolder & strFile
  Call UpdateTemplateRefs(StrFolder & strFile)
  strFile = Dir()
Wend
End Sub

Sub UpdateTemplateRefs(strDoc As String)
' This sub updates the template paths for files after a server
' change. Simply insert however much of the lower end of the
' server paths differ as the OldServer and NewServer variables.
Dim OldServer As String, NewServer As String, strPath As String
OldServer = "\TSBVOL1": NewServer = "\TSLSERVERFiles"
' Store the file's current Date/Time stamp.
Set oFldr = oShell.NameSpace(StrFolder & "")
Set oItem = oFldr.ParseName(strFile)
StrDtTm = oItem.ModifyDate
' Open the document
Documents.Open strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto
With ActiveDocument
  If .ProtectionType = wdNoProtection Then
    ' Update the template path
    strPath = Dialogs(wdDialogToolsTemplates).Template
    If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
      ' Update the file counter for changed files
      i = i + 1
      On Error GoTo ErrRpt
      .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      GoTo OK
ErrRpt:
    ' Output an error report in the document from which the macro is run.
    ThisDocument.Range.InsertAfter vbCr & "Template: " & NewServer & Mid(strPath, Len(OldServer) + 1) & _
      " not found for " & strDoc
      ' Reset the template to 'Normal'
      .AttachedTemplate = ""
    On Error GoTo 0
OK:
    End If
  Else
    ' Output a 'protected' file report in the document from which the macro is run.
    ThisDocument.Range.InsertAfter vbCr & strDoc & " protected. Not updated."
  End If
  .Close SaveChanges:=True
End With
' Update the main file counter
j = j + 1
' Let Word do its housekeeping
DoEvents
' Reset the file's Date/Time stamp.
Set oItem = oFldr.ParseName(strFile)
If oItem.ModifyDate  StrDtTm Then oItem.ModifyDate = StrDtTm
' Erase the shell-related items
Set oItem = Nothing: Set oFldr = Nothing
End Sub

For the files processed so far, I’ll have to investigate – I don’t ordinarily work in this area. FWIW, I’d have thought the printed date, if there is one, would be a better indicator of when the file was last updated than is the content date.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

Thank you very much for your prompt response and thank you for thoughtfully modifying the code for the rest of my task. I am very pleased that I won’t have the date/time problem with the remaining files.

I should have realised, if I had thought about it, that changing the files without resetting the file’s date/time would change it to whenever the macro was run. I guess I will always learn.

Regarding the Date Printed and Content Created dates, although initially I thought that the printed date would work, I found many files did not have a print date at all and some had a different print date to that in the letters. Overall I found the Content Created date a much more reliable indicator of when the letters were written.

Good luck with the investigation!

Regards
George

Reply | Quote

Hi Paul

I tested the new code on previously unchanged Word documents and it worked fine, leaving the original modified dates on each file, but changing the path of the attached Templates.

When I ran it on a real folder, however, the macro ‘crashed’ with the following error:

Runtime error ’91’
Object variable or With block variable not set

and running Debug, the following line was highlighted:

StrDtTm = oItem.ModifyDate

This folder has a number of non-Word files in various sub-folders and Word documents in other sub-folders. I checked and the macro definitely had its problem with the folder that did not contain Word files, but other file types. I tested it on the sub-folder that contained purely Word files and it ran fine with no issues.

It would be really time-consuming to find and run it only on sub-folders that had Word files in it, so I hope you can fix this error.

Sorry to be a nuisance.

Regards
George

Reply | Quote

The line ‘StrDtTm = oItem.ModifyDate’ is trying to get the modified dates from .doc files only. The implication of the error is that the folder concerned has one or more such files without a ‘modified’ date! Can you check this?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I have conducted a very detailed test, running the macro on each sub-folder separately without error. I also moved all of the Word files contained in the main folder to a separate sub-folder and ran the macro there, without error.

However, when I run the macro on the main folder with all files/sub-folders where they should be, it fails, showing the first Word file in first sub-folder that contains Word files in the status line of Word.

I then moved this file out of that sub-folder and ran the macro again on the main folder. This time it failed showing the second Word file in that sub-folder on the status line of Word.

I moved the entire sub-folder and this time after running the macro on the main folder, it failed on the now first sub-folder with Word files, which was the second sub-folder before.

So, when running on each sub-folder separately, the macro runs without error, but when it runs on the main folder, it fails and does so seemingly on a different file each time..

Each Word file has a Modified Date.

It makes no sense to me, I hope you can make some sense of it.

Regards
George

Reply | Quote

Hi George,

A slightly different approach, re-using the FileSystemObject instead of the Shell:

Code:

Option Explicit
Dim FSO As Object 'a FileSystemObject
Dim oFolder As Object 'the folder object
Dim oSubFolder As Object 'the subfolders collection
Dim oFiles As Object 'the files object
Dim i As Long, j As Long

Sub Main()
' Minimise screen flickering
Application.ScreenUpdating = False
Dim StrFolder As String
' Browse for the starting folder
StrFolder = GetTopFolder
If StrFolder = "" Then Exit Sub
i = 0: j = 0
' Search the top-level folder
Call GetFolder(StrFolder & "")
' Search the subfolders for more files
Call SearchSubFolders(StrFolder)
' Return control of status bar to Word
Application.StatusBar = ""
' Restore screen updating
Application.ScreenUpdating = True
MsgBox i & " of " & j & " files updated.", vbOKOnly
End Sub

Function GetTopFolder() As String
GetTopFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetTopFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Sub SearchSubFolders(strStartPath As String)
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(strStartPath)
Set oSubFolder = oFolder.subfolders
For Each oFolder In oSubFolder
  Set oFiles = oFolder.Files
  ' Search the current folder
  Call GetFolder(oFolder.Path & "")
  ' Call ourself to see if there are subfolders below
  Call SearchSubFolders(oFolder.Path)
Next
Set FSO = Nothing
End Sub

Sub GetFolder(StrFolder As String)
Dim strFile As String
strFile = Dir(StrFolder & "*.doc")
' Process the files in the folder
While strFile  ""
  ' Update the status bar is just to let us know where we are
  Application.StatusBar = StrFolder & strFile
  Call UpdateTemplateRefs(StrFolder & strFile)
  strFile = Dir()
Wend
End Sub

Sub UpdateTemplateRefs(strDoc As String)
' This sub updates the template paths for files after a server
' change. Simply insert however much of the lower end of the
' server paths differ as the OldServer and NewServer variables.
Dim OldServer As String, NewServer As String, strPath As String
Dim oItem As Object, StrDtTm As String
OldServer = "\TSBVOL1": NewServer = "\TSLSERVERFiles"
' Store the file's current Date/Time stamp.
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set oItem = FSO.GetFile(strDoc)
StrDtTm = oItem.DateLastModified
' Open the document
Documents.Open strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto
With ActiveDocument
  If .ProtectionType = wdNoProtection Then
    ' Update the template path
    strPath = Dialogs(wdDialogToolsTemplates).Template
    If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
      ' Update the file counter for changed files
      i = i + 1
      On Error GoTo ErrRpt
      .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      GoTo OK
ErrRpt:
    ' Output an error report in the document from which the macro is run.
    ThisDocument.Range.InsertAfter vbCr & "Template: " & NewServer & Mid(strPath, Len(OldServer) + 1) & _
      " not found for " & strDoc
      ' Reset the template to 'Normal'
      .AttachedTemplate = ""
    On Error GoTo 0
OK:
    End If
  Else
    ' Output a 'protected' file report in the document from which the macro is run.
    ThisDocument.Range.InsertAfter vbCr & strDoc & " protected. Not updated."
  End If
  .Close SaveChanges:=True
End With
' Update the main file counter
j = j + 1
' Let Word do its housekeeping
DoEvents
' Reset the file's Date/Time stamp.
Set oItem = FSO.GetFile(strDoc)
If oItem.DateLastModified  StrDtTm Then oItem.DateLastModified = StrDtTm
Set oItem = Nothing
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

Well done … whatever you did. It works fine!!

Thank you yet again.

I just hope that your investigation of that other date problem is as fruitful.

Regards
George

Reply | Quote

Hi Paul

Sorry to bother you, but something very strange is happening as I continue to run that macro for other files that need the new template paths.

When an error report is created, I am unable to ‘get rid of it’. Whether I close the file, or save the file, or exit Word, the error report just re-appears as soon as I open a new file, or re-run Word (when a new document is started automatically).

It must be a simple code change, I hope.

Regards
George

Reply | Quote

Hi Paul

One other thing. I’m probably not doing it right, but I am unable to add your macro to existing macros, because I am unable to ‘separate’ them and make yours unique. The only way I can run it is to remove all the others, so yours is the only one.

Clearly there must be a way for the macro to pick up those statements (declarations?) above Sub Main () but unless it’s the only macro it doesn’t.

Thank you.

Regards
George

Reply | Quote

Hi George,

Are you trying to add the macro to a template, or to a new document? I had envisaged it being added to a document (in which case it would be the only code in the relevant module), not to a template.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi George,

For the files with the ‘wrong’ dates, substitute the following for the ‘UpdateTemplateRefs’ sub:

Code:

Sub UpdateTemplateRefs(sInputFile As String)
' This sub re-sets the modified dates on the referenced files.
Dim objShell, objFolder, objFile As Object, sFolder, sFile As String, StrTmp As String
sFolder = Left$(sInputFile, InStrRev(sInputFile, ""))
sFile = Mid$(sInputFile, InStrRev(sInputFile, "") + 1)
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.NameSpace(sFolder)
Set objFile = objFolder.ParseName(sFile)
StrTmp = objFolder.GetDetailsOf(objFile, 143)
StrTmp = Replace(Replace(StrTmp, ChrW(8206), ""), ChrW(8207), "")
objFile.ModifyDate = StrTmp
Set objFile = Nothing: Set objFolder = Nothing: Set objShell = Nothing
i = i + 1: j = i
End Sub

Before running the above, though, you should verify that the ‘143’ is the correct offset for the ‘Content Created’ date, which apparently varies by OS. Here’s some code for the testing:

Code:

Sub Test()
Dim Str As String, strInfo As String
Str = "C:Users" & Environ("UserName") & "DocumentsSomeFile.doc"
strInfo = FileDetails(Str)
strInfo = Replace(Replace(strInfo, ChrW(8206), ""), ChrW(8207), "")
MsgBox strInfo
End Sub

Function FileDetails(ByVal sInputFile As String) As String
Dim objShell, objFolder, objFile As Object, sFolder, sFile As String, i As Long, StrTmp As String
sFolder = Left$(sInputFile, InStrRev(sInputFile, ""))
sFile = Mid$(sInputFile, InStrRev(sInputFile, "") + 1)
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.NameSpace(sFolder)
Set objFile = objFolder.ParseName(sFile)
For i = 0 To 150
  StrTmp = StrTmp & i & ":" & objFolder.GetDetailsOf(objFile, i) & vbTab
Next
FileDetails = StrTmp
Set objFile = Nothing: Set objFolder = Nothing: Set objShell = Nothing
End Function

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

1. That’s an easy solution to add the macro to a document instead of the Normal template. Thank you, that’s resolved.
2. I ran the test code, but even though your code says “For i = 0 To 150” the display box only display 65 entries and Content Created is not one of them.
3. That3.
3. That weird display of the error report keeps coming up no matter what I do, each time I open Word.

Regards
George

Reply | Quote

Hi Paul

I have just tested the code for the date fix and although the test code only displayed 65 entries, the 143 is correct and the code does work!

Thank you very much! It is really appreciated!

All that remains now is to fix that strange problem of the error code report being displayed every time I run Word and there is nothing I can do to remove it.

Regards
George

Reply | Quote

To fix the errant error report, open the Normal template for editing (File|Open [File type = “All Word templates”] & navigate to the Word templates folder – probably something like ‘C:Documents and SettingsUsefulApplication DataMicrosoftTemplates’), then delete the report.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

Clearly I’m missing something. I am running Windows 7 on this PC, so when I go to C:UsersGeorgeAppDataRoamingMicrosoftTemplates the only files I can see are Normal.dotm and NormalEmail.dotm there is no Error Report to delete.

Regards
George

Reply | Quote

Clearly I’m missing something. I am running Windows 7 on this PC, so when I go to C:UsersGeorgeAppDataRoamingMicrosoftTemplates the only files I can see are Normal.dotm and NormalEmail.dotm there is no Error Report to delete.

Did you open Normal.dotm for editing?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Ah yes, if I was only thinking … the Error Report was clearly written to the Normal template.

Thank you!

Regards
George

Reply | Quote

Hi Paul

Just when I thought everything was going to be OK and I could just fix those file dates … I am running the code and after doing a significant number of files fine, it ‘crashes’ with:

Runtime error ’13’
Type mismatch

Debug shows:

ObjFile.ModifyDate = StrTmp

Initially I moved the offending file out of that tree and ran it again (I can’t run the code from where it has stopped, which is a problem in itself if it strikes an error way down the tree). This time it ‘crashed’ on a different file, so I thought I would ask for your help.

Sorry to trouble you again.

Regards
George

Reply | Quote

Hi Paul

I’m not having much luck it seems … whilst waiting, I thought I would continue with the template path changes on all of those other files in the Home directory. Armed with your latest code, I ran it on a couple of people’s folders and it ran fine. On the third person’s folder, however, it ‘crashed’ on one of the Word files with:

Run-time error ’91’
Object variable or Withblock variable not set

Debug highlighted:

.AttachedTemplate = “”

and the error report displayed:

Template: \TSLSERVERFilesDocGenWord2002B&W Letter_02.dot not found for H:HomeLizConveyancing19533 ltr to natoli.doc

I have checked and B&W Letter_02.dot is definitely there, so I don’t understand what it is complaining about.

Sorry to keep bothering you.

Regards
George

Reply | Quote

Hi George,

From what you’ve posted, I can’t see what the cause might be either. That said, you might try changing:

Code:

      On Error GoTo ErrRpt
      .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      GoTo OK
ErrRpt:
    ' Output an error report in the document from which the macro is run.
    ThisDocument.Range.InsertAfter vbCr & "Template: " & NewServer & Mid(strPath, Len(OldServer) + 1) & _
      " not found for " & strDoc
      ' Reset the template to 'Normal'
      .AttachedTemplate = ""
    On Error GoTo 0
OK:

to:

Code:

      strPath = NewServer & Mid(strPath, Len(OldServer) + 1)
      If Dir(strPath)  "" Then
        .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      Else
        ' Output an error report in the document from which the macro is run.
        ThisDocument.Range.InsertAfter vbCr & "Template: " & strPath & " not found for " & strDoc
        ' Reset the template to 'Normal'
        .AttachedTemplate = ""
      End If

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I substituted the code you so kindly sent and ran the macro again and this time it ‘crashed’ with:

Run-time error ‘5180’
Word cannot open this document template
(\TSLSERVER…Bill of Costs_02.dot)

Debug highlighted the line:

.AttachedTemplate = NewServer & Mid(strPath, Len(oldServer) + 1)

The template file it says it cannot open is, of course, there so I’m none the wiser as to why it would display that error.

Regards
George

Reply | Quote

Hi George,

Time to check that the template isn’t corrupt or protected. Try opening it and/or creating a new file from it.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I have just opened the template without problems and just created a new file from it, which also worked fine. I have Administrator privileges, so it could not be a rights issue.

Regards
George

Reply | Quote

Hi George,

I just noticed that the file throwing the latest error is a different one than before. Did the previous one end up being processed correctly with the new code? Something else to check is whether the template now throwing the error has any macros or is intended to run a mailmerge.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

The previous file the appeared on the error report and reported that it could not find the template, did process correctly with the new code. All of the templates involved with this process have several macros, but none of the templates are intended to run a mailmerge.

The very next Word file in the same folder must have tripped up the code when I ran it again, as the template that is attached to that document is the Bill of Costs_02.dot and the code has not updated to the current template path, which still points to the old Novell server path.

I hope this gives you a suitable clue.

Regards
George

Reply | Quote

Hi George,

In the ‘Main’ sub, try inserting:
‘ Disable auto macros
WordBasic.DisableAutoMacros 1
before:
Dim StrFolder As String
and:
‘ Enable auto macros
WordBasic.DisableAutoMacros 0
before:
‘ Restore screen updating

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I inserted the new code in the two places you indicated and ran the macro again on the same person’s folder, but it ‘crashed’ with the same Run-time error ‘5180’ as it did before, with the same message and same Debug highlight.

Regards
George

Reply | Quote

Hi George,

Try it with this version of the ‘UpdateTemplateRefs’ sub:

Code:

Sub UpdateTemplateRefs(strDoc As String)
' This sub updates the template paths for files after a server
' change. Simply insert however much of the lower end of the
' server paths differ as the OldServer and NewServer variables.
Dim OldServer As String, NewServer As String, strPath As String
Dim oItem As Object, StrDtTm As String
OldServer = "\TSBVOL1": NewServer = "\TSLSERVERFiles"
' Store the file's current Date/Time stamp.
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set oItem = FSO.GetFile(strDoc)
StrDtTm = oItem.DateLastModified
' Open the document
Documents.Open strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto
With ActiveDocument
  If .ProtectionType = wdNoProtection Then
    ' Update the template path
    strPath = Dialogs(wdDialogToolsTemplates).Template
    If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
      ' Update the file counter for changed files
      i = i + 1
      ' Get the new template path & name
      strPath = NewServer & Mid(strPath, Len(OldServer) + 1)
      ' Check whether the template exists
      If Dir(strPath)  "" Then
        On Error GoTo ErrRpt
        ' If found, update the path
        .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      Else
        ' If not found, reset the template to 'Normal'
        .AttachedTemplate = ""
        ' Output an error report in the document from which the macro is run.
        ThisDocument.Range.InsertAfter vbCr & "Template: " & strPath & " not found for " & strDoc
ErrRpt:
        If Err.Number = 5180 Then
          ' Output an error report in the document from which the macro is run.
          ThisDocument.Range.InsertAfter vbCr & "Err 5180 - Template: " & strPath & " could not be updated for " _
            & strDoc & ". Please update manually."
        ElseIf Err.Number > 0 Then
          ' Output an error report in the document from which the macro is run.
          ThisDocument.Range.InsertAfter vbCr & "Unspecified error - Template: " & strPath & " could not be updated for " _
            & strDoc & ". Please update manually."
        End If
      End If
    End If
  Else
    ' Output a 'protected' file report in the document from which the macro is run.
    ThisDocument.Range.InsertAfter vbCr & strDoc & " protected. Not updated."
  End If
  .Close SaveChanges:=True
End With
' Update the main file counter
j = j + 1
' Let Word do its housekeeping
DoEvents
' Reset the file's Date/Time stamp.
Set oItem = FSO.GetFile(strDoc)
If oItem.DateLastModified  StrDtTm Then oItem.DateLastModified = StrDtTm
Set oItem = Nothing
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I replaced the code with the one you kindly sent and once again ran the macro for the same person’s folder.

This time the code ran much more slowly and displayed, for example:

Contacting ||TSLSERVERFilesRFilesDocGenWord2002Bill of Costs_02.dot

I am unsure of where the extra folder ‘FilesR’ has come from. It is not in the code, nor on the File Server.

Eventually the macro ‘crashed’ with the following error:

Run-time error ‘450’
Wrong number of arguments or invalid property assignment

Debug highlighted the following:

oItem.DateLastModified = StrDtTm

The error report listed 29 files that it was unable to update and that should be updated manually. Most were Error ‘5180’ files, but some were protected documents. I have been able to open all of these protected documents and I can see that they contain forms with editable fields that have been downloaded from say the Titles Office and I am unable to see which templates are attached, as the document is just re-displayed when I got to Files | Options | Add-Ins | Templates.

Regards
George

Reply | Quote

Hi George,

Like you, I can’t see where “the extra folder ‘FilesR’ has come from”. Perhaps it’s because the offending template’s path was formerly specified as “\TSBVOL1R”.

As for the “Run-time error 450”, you could replace the lines:
Set oItem = FSO.GetFile(strDoc)
StrDtTm = oItem.DateLastModified
with:
StrDtTm = FileDateTime(strDoc)

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I keep forgetting to ask if you know what the problem is with the date fix macro?

Regards
George

Reply | Quote

Hi Paul

As far as I know, the path has never been \TSBVol1R … so that extra folder FilesR remains a mystery.

I have replaced the code as you suggested and ran the macro yet again on the same folder. It ran for a while then it ‘crashed’, this time with the following error:

Run-time error ‘6295’
Office has detected a problem with this file. To help protect your computer this file cannot be opened.

Debug highlighted:

Documents.Open StrDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto

When I then opened this file, it was opened in Protected Mode, with a similar error in Word.

Regards
George

Reply | Quote

Hi George,

Re the file with problem contents, that suggests a corrupt document. There’s really nothing I can do to fix that. I can, however, add extra error-checking:

Code:

Sub UpdateTemplateRefs(strDoc As String)
' This sub updates the template paths for files after a server
' change. Simply insert however much of the lower end of the
' server paths differ as the OldServer and NewServer variables.
Dim OldServer As String, NewServer As String, strPath As String
Dim oItem As Object, StrDtTm As String
OldServer = "\TSBVOL1": NewServer = "\TSLSERVERFiles"
' Store the file's current Date/Time stamp.
StrDtTm = FileDateTime(strDoc)
' Disable auto macros
WordBasic.DisableAutoMacros = True
On Error GoTo ErrRpt
' Open the document
Documents.Open strDoc, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto
With ActiveDocument
  If .ProtectionType = wdNoProtection Then
    ' Update the template path
    strPath = Dialogs(wdDialogToolsTemplates).Template
    If LCase(Left(strPath, Len(OldServer))) = LCase(OldServer) Then
      ' Update the file counter for changed files
      i = i + 1
      ' Get the new template path & name
      strPath = NewServer & Mid(strPath, Len(OldServer) + 1)
      ' Check whether the template exists
      If Dir(strPath)  "" Then
        ' If found, update the path
        .AttachedTemplate = NewServer & Mid(strPath, Len(OldServer) + 1)
      Else
        ' If not found, reset the template to 'Normal'
        .AttachedTemplate = ""
        ' Output an error report in the document from which the macro is run.
        ThisDocument.Range.InsertAfter vbCr & "Template: " & strPath & " not found for " & strDoc
ErrRpt:
        ' Output error reports in the document from which the macro is run.
        If Err.Number = 5180 Then
          ThisDocument.Range.InsertAfter vbCr & "Err 5180 - Template: " & strPath & " could not be updated for " _
            & strDoc & ". Please update manually."
        ElseIf Err.Number = 6295 Then
          ThisDocument.Range.InsertAfter vbCr & "Err 6295 - Unable to open " & strDoc & ". Office detected a problem with this file."
        ElseIf Err.Number > 0 Then
          ThisDocument.Range.InsertAfter vbCr & "Unspecified error - Template: " & strPath & " could not be updated for " _
            & strDoc & ". Please update manually."
        End If
      End If
    End If
  Else
    ' Output a 'protected' file report in the document from which the macro is run.
    ThisDocument.Range.InsertAfter vbCr & strDoc & " protected. Not updated."
  End If
  .Close SaveChanges:=True
End With
' Update the main file counter
j = j + 1
' Let Word do its housekeeping
DoEvents
' Reset the file's Date/Time stamp.
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set oItem = FSO.GetFile(strDoc)
If oItem.DateLastModified  StrDtTm Then oItem.DateLastModified = StrDtTm
Set oItem = Nothing
' Enable auto macros
WordBasic.DisableAutoMacros = False
End Sub

Re the date-fix macro, I wasn’t aware there was a problem with it. Post #54 suggested the code in post #52 worked fine.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I thought the date-fix issue might get lost in all our correspondence. You are right, I tested it and it initially worked (Post #54), but when I ran it on the real files, it ran for a while and eventually ‘crashed’. If you look at my Post #59 you will see the problem.

Thank you for the new code. I will implement it today and let you know the result.

I am very grateful for your continued help!

Regards
George

Reply | Quote

Hi George,

For that, you could try the same fix as in post #71, perhaps with the new line preceded by ‘On Error Resume Next’ (so that errors won’t prevent other files being updated).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I have looked at the date-fix code and I am unable to find the relevant area, as per Post #71, you suggest that I replace.

Also, at the moment, each time either of the two macros strike an error, the only option I seem to have is to start running them all over again from the beginning. Ideally, it would be great if the macros did not to stop on an error, but continue to the end.

If this can’t be done, is there a way of continuing from a specific point onwards that I choose manually? Although not ideal, because I would have to do this manually, I could at least set the starting point after the file with the error instead of always having to run the macros from the beginning.

Regards
George

Reply | Quote

Hi George,

The ‘UpdateTemplateRefs’ sub in post #74 should be fairly immune to any further crashes, as errors are directed to the error-processing part of the sub.

As for the date-fixing version of the sub, try inserting:
On Error Resume Next
anywhere before:
objFile.ModifyDate = StrTmp

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I will put that ‘On Error Resume Next’ line in the date-fix code and let you know how it goes.

Regarding the change-template-path macro, I made the code change as per Post #74, but unfortunately it still ‘crashed’ after running for a while, with:

Run-time error ’91’
Object variable or Withblock variable not set

Debug highlighted:

.Close SaveChanges:=True

Regards
George

Reply | Quote

Hi George,

I really am perplexed by the range of errors you seem to be getting.

Try changing:
.Close SaveChanges:=True
to:

Code:

  On Error GoTo NotSaved
  .Close SaveChanges:=True
NotSaved:
  If .Saved = False Then
    ' Output a 'protected' file report in the document from which the macro is run.
    ThisDocument.Range.InsertAfter vbCr & strDoc & " save error. Not updated."
  End If
  On Error GoTo 0

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I am really embarrassed to have to keep coming back to you with new issues.

I have substituted the new code you kindly sent in the macro and ran it again on the same folder and this time it ‘crashed’ immediately with:

Run-time error ‘5825’
Object has been deleted

Debug highlighted:

If .Saved = False Then

Regards
George

Reply | Quote

My apologies. Change:
.Close SaveChanges:=True
to:
.Save
and insert:
.Close SaveChanges:=False
after:
On Error GoTo 0

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

You must be sick of me …

I made the changes, but would you believe, the macro ‘crashed’ immediately with:

Run-time error ‘450’
Wrong number of arguments or invalid property assignment

Debug highlighted:

oItem.DateLastModified = StrDtTm

As in Post #70 but I made the change that you requested then.

Regards
George

Reply | Quote

Hi George,

Try inserting:
On Error Resume Next
before that line, and:
On Error GoTo 0
after it.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I put the code in as you requested and the macro ran for a short while on that same folder, then it ‘crashed’ with the same error (Post #83).

Regards
George

Reply | Quote

OK, try replacing everything between:
Set oItem = FSO.GetFile(strDoc)
and:
Set oItem = Nothing
with:

Code:

If IsDate(StrDtTm) Then
  If oItem.DateLastModified  StrDtTm Then oItem.DateLastModified = StrDtTm
End If

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul

I made the change to the template-path macro as you suggested and ran it on the same folder as before. It immediately ‘crashed’ with the error:

Run-time error ’91’
Object variable or Withblock variable not set

Debug highlighted:

If oItem.DateLastModified StrDtTm Then

I’m clearly having no luck with this one!

Regards
George

Reply | Quote

Hi Paul

In the meantime I ran the date-fix macro and it ran fine AND finished without error. I did some spot checks and it replaced the Modified dates with Content Created dates, just as we wanted.

Thank you very much!

I will make the suggested code changes to the change-template-path macro and report back.

Regards
George

Reply | Quote