Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-09-10
6
Medium Priority
?
153 Views
Last Modified: 2012-09-17
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.
0
Comment
Question by:SFrongillo
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38383701
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
 

Author Comment

by:SFrongillo
ID: 38383798
Sorry - that didn't seem to work...
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38383818
Please elaborate in what way it did not work for you.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:SFrongillo
ID: 38383941
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
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 38385240
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
 

Author Closing Comment

by:SFrongillo
ID: 38405186
That worked!  Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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