Page 1 of 6 123 ... LastLast
Results 1 to 15 of 88
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    243
    Thanks
    20
    Thanked 0 Times in 0 Posts

    Automated replacement of Word template paths

    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 \\TSB\Vol1 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

  2. #2
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    237
    Thanks
    3
    Thanked 24 Times in 24 Posts
    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.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,917
    Thanks
    0
    Thanked 194 Times in 177 Posts
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    243
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Thanks for the suggestion BigMac56, I will investigate whether it is possible to set up a virtual server.

    Regards
    Useful

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    243
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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 \\TSB\Vol1 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

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,917
    Thanks
    0
    Thanked 194 Times in 177 Posts
    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 = "<\\TSB\Vol1>": 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
    [MS MVP - Word]

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    243
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,917
    Thanks
    0
    Thanked 194 Times in 177 Posts
    For installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    243
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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 \\TSB\Vol1\Doc\Gen\Word2002 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 \\TSB\Vol1 becomes H:, I then tried to modify the OldServer and NewServer values in the code, eventually entering the whole path \\TSB\Vol1\Doc\Gen\Word2002 where the Templates are and repeating it with H:\Doc\Gen\Word2002 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

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,917
    Thanks
    0
    Thanked 194 Times in 177 Posts
    Hi useful,

    In the code, you'll see the line 'OldServer = "<\\TSB\Vol1>": 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 '\\TSB\Vol1', which I've encoded as 'OldServer = "<\\TSB\Vol1>"'. 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 \\TSB\Vol1 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
    [MS MVP - Word]

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    243
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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 \\TSB\Vol1 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 Doc\Gen\Word2002, the full UNC path being \\TSB\Vol1\Doc\Gen\Word2002, 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:\Doc\Gen\Word2002, whilst it was \\TSB\Vol1\Doc\Gen\Word2002.

    If a full UNC path is required for the new server as well, then the templates are now located in \\TSLSERVER\Files\Doc\Gen\Word2002

    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

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,917
    Thanks
    0
    Thanked 194 Times in 177 Posts
    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 = "\\TSLSERVER\Files"'

    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 = "\\TSB\Vol1": NewPath = "\\TSLSERVER\Files\Doc\Gen\Word2002"
    ' 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
    [MS MVP - Word]

  13. #13
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    243
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi Paul

    I ran your Test macro on the old test Word document and it returned the Template Path as \\TSB\Vol1\Doc\Gen\Word2002. 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:\Doc\Gen\Word2002.

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

    1. OldServer = "<\\TSB\Vol1>" and NewServer = "<H:>" - failed.
    2. OldServer = "<\\TSB\Vol1\Doc\Gen\Word2002>" and NewServer = "<H:\Doc\Gen\Word2002>" - failed.
    3. OldServer = "\\TSB\Vol1" and NewServer = "H:" - failed.
    4. OldServer = "\\TSB\Vol1\Doc\Gen\Word2002" and NewServer = "H:\Doc\Gen\Word2002" - failed.
    5. OldServer = "<\\TSB\Vol1>" and NewServer = "<\\TSLSERVER\Files>" - failed.
    6. OldServer = "<\\TSB\Vol1\Doc\Gen\Word2002>" and NewServer = "<\\TSLSERVER\Files\Doc\Gen\Word2002>" - failed.
    7. OldServer = "\\TSB\Vol1" and NewServer = "\\TSLSERVER\Files" - failed.
    8. OldServer = "\\TSB\Vol1\Doc\Gen\Word2002" and NewServer = "\\TSLSERVER\Files\Doc\Gen\Word2002" - failed.

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

    9. OldServer = "<\\TSB\Vol1>" and NewPath = "<H:>" - failed.
    10. OldServer = "<\\TSB\Vol1\Doc\Gen\Word2002>" and NewPath = "<H:\Doc\Gen\Word2002>" - failed.
    11. OldServer = "\\TSB\Vol1" and NewPath = "H:" - failed.
    12. OldServer = "\\TSB\Vol1\Doc\Gen\Word2002" and NewPath = "H:\Doc\Gen\Word2002" - failed.
    13. OldServer = "<\\TSB\Vol1>" and NewPath = "<\\TSLSERVER\Files>" - failed.
    14. OldServer = "<\\TSB\Vol1\Doc\Gen\Word2002>" and NewPath = "<\\TSLSERVER\Files\Doc\Gen\Word2002>" - failed.
    15. OldServer = "\\TSB\Vol1" and NNewPath "\\TSLSERVER\Files" - failed.
    16. OldServer = "\\TSB\Vol1\Doc\Gen\Word2002" and NewPath = "\\TSLSERVER\Files\Doc\Gen\Word2002" - failed.

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

    Thank you for your continuing generous assistance.

    Regards
    useful

  14. #14
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,524
    Thanks
    3
    Thanked 143 Times in 136 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  15. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,917
    Thanks
    0
    Thanked 194 Times in 177 Posts
    I'd suggest trying:
    Code:
    Sub UpdateTemplateRefs(strDoc As String)
    Dim OldPath As String, NewPath As String, Doc As Document
    OldPath = "\\TSB\Vol1\Doc\Gen\Word2002": NewPath = "H:\Doc\Gen\Word2002"
    ' 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
    [MS MVP - Word]

Page 1 of 6 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •