Solved

VBA Macro: Traverse through subfolders

Posted on 2008-10-16
7
2,396 Views
Last Modified: 2010-04-21
Hello
The following script (thanks to Jeews) modifies the Word documents in the given path to change the attached template. It fits my needs perfectly, except that it modifies only the files in the given folder and it's subfolders. I need to go through the whole directory structure, not only one "folder layer". The code should modify all of the .doc's in all subfolders, even in it's subfolders and so on...
Thank you.
Word 2003
Public PcLogFileName As String
Public PcError As String
 
Sub CallChangeTemplates()
    Dim lcCurrentDir As String
 
 
    lcCurrentDir = "C:\Documents and Settings\URathnayake\My Documents\Expert Exchange\Skip write - Password protected"
 
    PcLogFileName = lcCurrentDir + "\" + "DocumentAccessSummery.txt"
 
 
    ChangeTemplates lcCurrentDir, "*.doc*"
  
End Sub
 
Sub ChangeTemplates(lcFilePath As String, strFilePattern As String)
    
    Dim loFileSystemObject, loDirectoryList As Object
    Dim lcLogString, lcChildDirectoryName, lcDirectoryPath, lcFileName, lcFileFullPath As String
    
 
     ' Create an object form the filesystem object
    Set loFileSystemObject = CreateObject("Scripting.FileSystemObject")
    
          
    ' Get the list of clients. Note: Folders should be named with the patner names
    Set loDirectoryList = loFileSystemObject.GetFolder(lcFilePath).SubFolders
    
    
    Set loFileList = loFileSystemObject.GetFolder(lcFilePath).Files
        
        
        ' Traverse through the files
        For Each loFile In loFileList
        
            ' Get the file name to a variable to be used below
            lcFileName = loFile.Name
            
            ' Check only for excel fiels
            If InStr(1, lcFileName, "doc") > 0 And Not InStr(1, lcFileName, "~$") = 1 Then
                
                ' Construct the full path of the document
                lcFileFullPath = lcFilePath & "\" & lcFileName
            
                 ' Check whether the file is open
                If Not IsFileOpen(lcFileFullPath) Then
                
                    ' Checks the file is password protected
                    On Error Resume Next
                   Set Doc = Application.Documents.Open(lcFileFullPath, , , , "**")
                        
                     ' File is password protected
                    If Err > 0 Then
                        Update_Log ("File: " + lcFileFullPath + " is Password protected.")
                    Else
                        Doc.AttachedTemplate = NormalTemplate
                        Doc.Close wdSaveChanges
                    End If
                    
                Else
                    
                    Update_Log ("File: " + lcFileFullPath + " is in use.")
                End If
            End If
 
        Next
    
    
 
    ' Traverse to each client folder to check get the available reports
    For Each loDirectory In loDirectoryList
    
        ' Get the Name of the processing client
        lcChildDirectoryName = loDirectory.Name
        
        ' Get the full path of the client directory
        lcDirectoryPath = loDirectory.Path
        
        ' Get the list of files under the client folder to be searched for MSR reports and procees with the updating
        Set loFileList = loFileSystemObject.GetFolder(lcDirectoryPath).Files
        
        
        ' Traverse through the files
        For Each loFile In loFileList
        
            ' Get the file name to a variable to be used below
            lcFileName = loFile.Name
            
            ' Check only for word fiels without the temporary files
            If InStr(1, lcFileName, "doc") > 0 And Not InStr(1, lcFileName, "~$") = 1 Then
                
                ' Construct the full path of the document
                lcFileFullPath = lcDirectoryPath & "\" & lcFileName
            
                ' Check whether the file is open
                If Not IsFileOpen(lcFileFullPath) Then
                    
                    ' Checks the file is password protected
                    On Error Resume Next
                    Set Doc = Application.Documents.Open(lcFileFullPath, , , , "**")
                    
                        
                    ' File is password protected
                    If Err > 0 Then
                        Update_Log ("File: " + lcFileFullPath + " is Password protected.")
                    Else
                        Doc.AttachedTemplate = NormalTemplate
                        Doc.Close wdSaveChanges
                    End If
                    
                Else
                    
                    Update_Log ("File: " + lcFileFullPath + " is in use.")
                End If
            End If
 
        Next
    Next
 
    loFileSystemObject.Delete
    loDirectoryList.Delete
 
End Sub
 
Function IsFileOpen(lcFileName As String)
 
    Dim lnFileNum As Integer, lnErrNum As Integer
 
    On Error Resume Next   ' Turn error checking off.
    lnFileNum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open lcFileName For Input Lock Read As #lnFileNum
    Close lnFileNum          ' Close the file.
    lnErrNum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.
 
    ' Check to see which error occurred.
    Select Case lnErrNum
 
        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False
 
        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True
 
        ' Another error occurred.
        Case Else
            Error lnErrNum
    End Select
 
End Function
 
Function Update_Log(lcString As String)
    
    
    If Not IsEmpty(lcString) Then
        lcString = FormatDateTime(Date, vbLongDate) + " : " + FormatDateTime(Time, vbLongTime) + " : " + lcString
        
        cfilename = FreeFile()
      
        
        On Error Resume Next
        Open PcLogFileName For Append As cfilename
        
        
        Print #cfilename, lcString
        Close #cfilename
        
        Err
    End If
 
End Function

Open in new window

0
Comment
Question by:chassouna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 6

Expert Comment

by:Jeews
ID: 22738638
Hay hope this is what you are looking for

Please let me know if you need further assistance

Public PcLogFileName As String
Public PcError As String
Public loFileSystemObject As Object
 
Sub CallChangeTemplates()
    Dim lcCurrentDir As String
 
 
    lcCurrentDir = "c:\temp\docs"
 
    PcLogFileName = lcCurrentDir + "\" + "DocumentAccessSummery.txt"
 
 
    ChangeTemplates lcCurrentDir, "*.doc*"
  
End Sub
 
Sub ChangeTemplates(lcFilePath As String, strFilePattern As String)
    
    Dim loDirectoryList As Object
    Dim lcLogString, lcChildDirectoryName, lcDirectoryPath, lcFileName, lcFileFullPath As String
    
 
     ' Create an object form the filesystem object
    Set loFileSystemObject = CreateObject("Scripting.FileSystemObject")
    
          
    ' Get the list of clients. Note: Folders should be named with the patner names
    Set loDirectoryList = loFileSystemObject.GetFolder(lcFilePath).SubFolders
    
    For Each loDirectory In loDirectoryList
      lcReturn = ListSubFolders(loDirectory.Path)
    Next
    
End Sub
 
Function IsFileOpen(lcFileName As String)
 
    Dim lnFileNum As Integer, lnErrNum As Integer
 
    On Error Resume Next   ' Turn error checking off.
    lnFileNum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open lcFileName For Input Lock Read As #lnFileNum
    Close lnFileNum          ' Close the file.
    lnErrNum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.
 
    ' Check to see which error occurred.
    Select Case lnErrNum
 
        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False
 
        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True
 
        ' Another error occurred.
        Case Else
            Error lnErrNum
    End Select
 
End Function
 
Function Update_Log(lcString As String)
    
    
    If Not IsEmpty(lcString) Then
        lcString = FormatDateTime(Date, vbLongDate) + " : " + FormatDateTime(Time, vbLongTime) + " : " + lcString
        
        cfilename = FreeFile()
      
        
        On Error Resume Next
        Open PcLogFileName For Append As cfilename
        
        
        Print #cfilename, lcString
        Close #cfilename
        
        Err
    End If
 
End Function
 
Function ListSubFolders(lcFilePath As String)
 
    UpdateFiles (lcFilePath)
 
    On Error Resume Next
    ' Get the list of clients. Note: Folders should be named with the patner names
    Set loDirectoryList = loFileSystemObject.GetFolder(lcFilePath).SubFolders
    
    For Each loDirectory In loDirectoryList
    
        
        ListSubFolders (loDirectory.Path)
 
    Next
End Function
 
Function UpdateFiles(lcFilePath As String)
 
    Dim lcFileFullPath As String
 
    Set loFileList = loFileSystemObject.GetFolder(lcFilePath).Files
        
        
    ' Traverse through the files
    For Each loFile In loFileList
    
        ' Get the file name to a variable to be used below
        lcFileName = loFile.Name
        
        ' Check only for excel fiels
        If InStr(1, lcFileName, "doc") > 0 And Not InStr(1, lcFileName, "~$") = 1 Then
            
            ' Construct the full path of the document
            lcFileFullPath = lcFilePath & "\" & lcFileName
        
             ' Check whether the file is open
            If Not IsFileOpen(lcFileFullPath) Then
            
                ' Checks the file is password protected
                On Error Resume Next
               Set Doc = Application.Documents.Open(lcFileFullPath, , , , "**")
                    
                 ' File is password protected
                If Err > 0 Then
                    Update_Log ("File: " + lcFileFullPath + " is Password protected.")
                Else
                    Doc.AttachedTemplate = NormalTemplate
                    Doc.Close wdSaveChanges
                End If
                
            Else
                
                Update_Log ("File: " + lcFileFullPath + " is in use.")
            End If
        End If
 
    Next
 
End Function

Open in new window

0
 
LVL 6

Accepted Solution

by:
Jeews earned 500 total points
ID: 22739081
Please insert the below line in the Line 30 of the above code

UpdateFiles (lcFilePath)

I'll anyway upload the updated code for you


Public PcLogFileName As String
Public PcError As String
Public loFileSystemObject As Object
 
Sub CallChangeTemplates()
    Dim lcCurrentDir As String
 
 
    lcCurrentDir = "c:\temp\docs"
 
    PcLogFileName = lcCurrentDir + "\" + "DocumentAccessSummery.txt"
 
 
    ChangeTemplates lcCurrentDir, "*.doc*"
  
End Sub
 
Sub ChangeTemplates(lcFilePath As String, strFilePattern As String)
    
    Dim loDirectoryList As Object
    Dim lcLogString, lcChildDirectoryName, lcDirectoryPath, lcFileName, lcFileFullPath As String
    
 
     ' Create an object form the filesystem object
    Set loFileSystemObject = CreateObject("Scripting.FileSystemObject")
    
          
    ' Get the list of clients. Note: Folders should be named with the patner names
    Set loDirectoryList = loFileSystemObject.GetFolder(lcFilePath).SubFolders
    
    UpdateFiles (lcFilePath)
    
    For Each loDirectory In loDirectoryList
      lcReturn = ListSubFolders(loDirectory.Path)
    Next
    
End Sub
 
Function IsFileOpen(lcFileName As String)
 
    Dim lnFileNum As Integer, lnErrNum As Integer
 
    On Error Resume Next   ' Turn error checking off.
    lnFileNum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open lcFileName For Input Lock Read As #lnFileNum
    Close lnFileNum          ' Close the file.
    lnErrNum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.
 
    ' Check to see which error occurred.
    Select Case lnErrNum
 
        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False
 
        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True
 
        ' Another error occurred.
        Case Else
            Error lnErrNum
    End Select
 
End Function
 
Function Update_Log(lcString As String)
    
    
    If Not IsEmpty(lcString) Then
        lcString = FormatDateTime(Date, vbLongDate) + " : " + FormatDateTime(Time, vbLongTime) + " : " + lcString
        
        cfilename = FreeFile()
      
        
        On Error Resume Next
        Open PcLogFileName For Append As cfilename
        
        
        Print #cfilename, lcString
        Close #cfilename
        
        Err
    End If
 
End Function
 
Function ListSubFolders(lcFilePath As String)
 
    UpdateFiles (lcFilePath)
 
    On Error Resume Next
    ' Get the list of clients. Note: Folders should be named with the patner names
    Set loDirectoryList = loFileSystemObject.GetFolder(lcFilePath).SubFolders
    
    For Each loDirectory In loDirectoryList
    
        
        ListSubFolders (loDirectory.Path)
 
    Next
End Function
 
Function UpdateFiles(lcFilePath As String)
 
    Dim lcFileFullPath As String
 
    Set loFileList = loFileSystemObject.GetFolder(lcFilePath).Files
        
        
    ' Traverse through the files
    For Each loFile In loFileList
    
        ' Get the file name to a variable to be used below
        lcFileName = loFile.Name
        
        ' Check only for excel fiels
        If InStr(1, lcFileName, "doc") > 0 And Not InStr(1, lcFileName, "~$") = 1 Then
            
            ' Construct the full path of the document
            lcFileFullPath = lcFilePath & "\" & lcFileName
        
             ' Check whether the file is open
            If Not IsFileOpen(lcFileFullPath) Then
            
                ' Checks the file is password protected
                On Error Resume Next
               Set Doc = Application.Documents.Open(lcFileFullPath, , , , "**")
                    
                 ' File is password protected
                If Err > 0 Then
                    Update_Log ("File: " + lcFileFullPath + " is Password protected.")
                Else
                    Doc.AttachedTemplate = NormalTemplate
                    Doc.Close wdSaveChanges
                End If
                
            Else
                
                Update_Log ("File: " + lcFileFullPath + " is in use.")
            End If
        End If
 
    Next
 
End Function

Open in new window

0
 

Author Comment

by:chassouna
ID: 22739387
Very good, now all documents in every subfolder are processed. But now the documents in the root folder stay unchanged (that files that resides just in the folder we define in line 9). I think this is a small code change (as it worked in the previous code).
Thank you!
0
Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

 
LVL 6

Expert Comment

by:Jeews
ID: 22739407
I have done that change also in the previous post please check
0
 

Author Closing Comment

by:chassouna
ID: 31507042
I'm very thankful for your assistance, Jeews. You saved me a lot of time. Thank you!
0
 

Author Comment

by:chassouna
ID: 22739465
Ups, I copied the code without the supplemented line...
I'm very thankful for your assistance, Jeews. You saved me a lot of time. Thank you!
0
 
LVL 6

Expert Comment

by:Jeews
ID: 22739501
You are always welcome chassouna :-)
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When creating Microsoft Word-based forms there may be a need to have a form field repeated throughout the whole document. For instance, with a company name, you may want this information repeated automatically throughout the document rather than man…
I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question