Macro to update document name out on a server, located in various folders

I need a macro that will go out to a folder, hunt through all the folders contained within the folder, look for the documents that contain "as of 08-10-2012.xls" in the name and change it to "as of 09-10-2012.xls".  I have 800+ files that need to be updated due to an output typo.

Thanks for any help you can give.... I have Office 2010.
Sandra FrongilloSenior Business AnalystAsked:
Who is Participating?
 
krishnakrkcConnect With a Mentor Commented:
Are you sure the extension is .xls ?

If not sure then try this

Sub kTest()
    
    Dim f, fn As String
    
    Const MyFolder = "\\lm\central\Corporate\Legal-Field\AlmsReports\ALMS REPORTS\Legal Reports\Pacific Region\"        ' adjust to suit
    
    With CreateObject("scripting.filesystemobject").getfolder(MyFolder)
        For Each f In .subFolders
            fn = Dir(f.Path & "\Pending Defenses as 08-10-2012.xls*")
            If Len(fn) Then
                Name f.Path & "\" & fn As f.Path & "\" & Replace(fn, "Pending Defenses as 08-10-2012", "Pending Defenses as of 09-10-2012")
            End If
        Next
    End With
    
End Sub

Open in new window


Kris
0
 
krishnakrkcCommented:
Hi

try

Sub kTest()
    
    Dim f, fn As String
    
    Const MyFolder = "C:\temp\"        ' adjust to suit
    
    With CreateObject("scripting.filesystemobject").getfolder(MyFolder)
        For Each f In .subFolders
            fn = Dir(f.Path & "\as 08-10-2012.xls")
            If Len(fn) Then
                Name f.Path & "\as 08-10-2012.xls" As f.Path & "\as of 09-10-2012.xls"
            End If
        Next
    End With
    
End Sub

Open in new window


Kris
0
 
Sandra FrongilloSenior Business AnalystAuthor Commented:
Sorry - that didn't seem to work...
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
krishnakrkcCommented:
Please elaborate in what way it did not work for you.
0
 
Sandra FrongilloSenior Business AnalystAuthor Commented:
Here's the code I used -

Sub kTest()
   
    Dim f, fn As String
   
    Const MyFolder = "\\lm\central\Corporate\Legal-Field\AlmsReports\ALMS REPORTS\Legal Reports\Pacific Region\"        ' adjust to suit
   
    With CreateObject("scripting.filesystemobject").getfolder(MyFolder)
        For Each f In .subFolders
            fn = Dir(f.path & "\Pending Defenses as 08-10-2012.xls")
            If Len(fn) Then
                Name f.path & "\Pending Defenses as 08-10-2012.xls" As f.path & "\Pending Defenses as of 09-10-2012.xls"
            End If
        Next
    End With
   
End Sub

I updated with the actual file name since it wasn't finding anything - did look like it was thinking but didn't find any documents that contained the 08-10-2012.xls in the name nor any sub folder within Pacific Region...

I hope that is clearer than my first post. Sorry...
0
 
Sandra FrongilloSenior Business AnalystAuthor Commented:
That worked!  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.