Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Rename files in a single folder based on the modified date

0

Hello

I  have  problem  with  this  code !

the  code  will search  for  words sales reports   if theses words  are  existed , then will add  dd mmm yyyy  based on modified date , but  the  problem will adddd mmm yyyy continously when  run the  macro evry time  even  if  the  file  name contains sales reports dd mmm yyyy  shouldn't  do that . if  there is  already  file  contains sales reports dd mmm yyyy then  should  ignore  it .

Sub Test()
Dim p$, f$, pf$, d$, x$, n$

p = "E:\" 'change to your path

f = Dir(p & "*sales reports*")
Do While f <> vbNullString
    pf = p & f
    d = UCase(Format(FileDateTime(pf), "dd mmm yyyy"))
    x = Right(pf, Len(pf) - InStrRev(pf, ".") + 1)
    n = Left(f, Len(f) - Len(x))
    If Not Right(n, 11) Like "## ??? ####" Then
        n = n & " " & d & x
        Name pf As p & n
    End If
    f = Dir
Loop 
End Sub

thanks

excel
MS Office Version: 2019
posted by: Malkal (rep: 22) on Mon Mar 20, 2023 at 8:22 am
Answer
Discuss

Answers

0
Selected Answer

Hi Malkal

Given that you want to add the file date to the file name (unless it's already there), you just need to test for that. Here (and in the attached file) I've used Instr to check if string d (the file date) is already there in string f (file name) - see changes in bold:

Sub Test()
Dim p$, f$, pf$, d$, x$, n$

p = "E:\" 'change to your path

f = Dir(p & "*sales reports*")
Do While f <> vbNullString
    pf = p & f
    d = UCase(Format(FileDateTime(pf), "dd mmm yyyy"))
    ' check if date is NOT already in file name
    If InStr(f, d) = 0 Then
        x = Right(pf, Len(pf) - InStrRev(pf, ".") + 1)
        n = Left(f, Len(f) - Len(x))
        If Not Right(n, 11) Like "## ??? ####" Then
            n = n & " " & d & x
            Name pf As p & n
        End If
    End If
    f = Dir
Loop
End Sub

Now it will add the file modified date to each file just once (not add a second date when you run the macro again).

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Mon Mar 20, 2023 at 10:00 am
Discuss

Discussion

Awesome !
many  thanks  John .
Malkal (rep: 22) Mar 21, '23 at 4:22 am
Glad that helped. Thanks for selecting my Answer, Malkal (especially since it took me to the top of the Forum's "Top Users by Points" leaderboard).
John_Ru (rep: 6142) Mar 21, '23 at 4:40 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login