Page 4 of 6 FirstFirst ... 23456 LastLast
Results 46 to 60 of 88
  1. #46
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    242
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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

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

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

    useful (2012-11-28)

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

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

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

  7. #51
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,767
    Thanks
    0
    Thanked 162 Times in 150 Posts
    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
    [MS MVP - Word]

  8. #52
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,767
    Thanks
    0
    Thanked 162 Times in 150 Posts
    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") & "\Documents\SomeFile.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
    Last edited by macropod; 2012-12-02 at 23:19. Reason: updated code comments
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    useful (2012-11-29)

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

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

  12. #55
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,767
    Thanks
    0
    Thanked 162 Times in 150 Posts
    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 Settings\Useful\Application Data\Microsoft\Templates'), then delete the report.
    Last edited by macropod; 2012-11-29 at 22:55.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Clearly I'm missing something. I am running Windows 7 on this PC, so when I go to C:\Users\George\AppData\Roaming\Microsoft\Template s the only files I can see are Normal.dotm and NormalEmail.dotm there is no Error Report to delete.

    Regards
    George

  14. #57
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,767
    Thanks
    0
    Thanked 162 Times in 150 Posts
    Quote Originally Posted by useful View Post
    Clearly I'm missing something. I am running Windows 7 on this PC, so when I go to C:\Users\George\AppData\Roaming\Microsoft\Template s 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
    [MS MVP - Word]

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

    useful (2012-11-29)

  16. #58
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    242
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Ah yes, if I was only thinking ... the Error Report was clearly written to the Normal template.

    Thank you!

    Regards
    George

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

  18. #60
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    242
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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: \\TSLSERVER\Files\Doc\Gen\Word2002\B&W Letter_02.dot not found for H:\Home\Liz\Conveyancing\19533 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

Page 4 of 6 FirstFirst ... 23456 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
  •