Page 3 of 6 FirstFirst 12345 ... LastLast
Results 31 to 45 of 88
  1. #31
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts
    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 = "\\TSB\VOL1": NewServer = "\\TSLSERVER\Files"
    ' 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
    [MS MVP - Word]

  2. The Following User Says Thank You to macropod For This Useful Post:

    useful (2012-11-25)

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

  4. #33
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts
    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
    [MS MVP - Word]

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

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

  7. #36
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Hi George,

    Try:
    Code:
    Sub UpdateTemplateRefs(strDoc As String)
    Dim OldServer As String, NewServer As String, strPath As String
    OldServer = "\\TSB\VOL1": NewServer = "\\TSLSERVER\Files"
    ' 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
    [MS MVP - Word]

  8. The Following User Says Thank You to macropod For This Useful Post:

    useful (2012-11-27)

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

  10. #38
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Quote Originally Posted by useful View Post
    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 = "\\TSB\VOL1": NewServer = "\\TSLSERVER\Files"
    ' 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
    [MS MVP - Word]

  11. The Following User Says Thank You to macropod For This Useful Post:

    useful (2012-11-27)

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

  13. #40
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    242
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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 \\TSLSERVER\Files (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

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

  15. #42
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts
    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 = "\\TSB\VOL1": NewServer = "\\TSLSERVER\Files"
    ' 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
    [MS MVP - Word]

  16. The Following User Says Thank You to macropod For This Useful Post:

    useful (2012-11-28)

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

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

  19. #45
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts
    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
    [MS MVP - Word]

Page 3 of 6 FirstFirst 12345 ... 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
  •