Confusion on REN method

Question

I tried to rename all the files recursively in the folder through VBA.

    Sub FileNameCleaner()
'***************************************************************************
'Purpose: Update the filename to the latest valuation date.
'Author: Justin Sun
'Date: July 12th, 2018
'Inputs: nil
'Outputs: nil
'***************************************************************************
    Dim valdate, prvdate, myline As String
    valdate = "1811"
    prvdate = "1810"

    myline = "&& for /r %i in (*" & prvdate & "*) do ren ""%i"" ""*" & valdate & "*"" "
    mydate = "C:\Users\j1sunx\Desktop\test\"
    Call Shell("cmd /k cd " & mydate & myline, vbNormalNoFocus)


End Sub

The logic is quite straight-forward, use the wildcard to replace the previous month to the current month. The method works fine for other month, but it fails at month 11. The code shown above tried to update the date from 1810 to 1811. But in my testcase. It changes my filename 201810.txt to 20181811.txt.

Thanks in advance for your help.

You might have a better control if you where using vba commands and not a shell. — Vincent G, Dec 04 '18 at 09:14
Ever heard of the [FileSystemObject](https://stackoverflow.com/q/3233203)? — aschipfl, Dec 04 '18 at 09:28

Vincent G · Accepted Answer · 2018-12-04T10:24:51.517

Why it doesn't work:

Let's decompose your command:

Your command translate to ren "C:\Users\j1sunx\Desktop\test\201810.txt" "*1811*". This page explain how *1811* is evaluated:

*c Matches all source characters from current position through the last occurance of c (case sensitive greedy match) and appends the matched set of characters to the target name. If c is not found, then all remaining characters from source are appended, followed by c This is the only situation I am aware of where Windows file pattern matching is case sensitive

The first * with 1 will match anything up to the last 1 of the string: so C:\Users\j1sunx\Desktop\test\20181.

Then we add the 810 which are simple characters with no special meanings.

* At end of sourceMask - Appends all remaining characters from source to the target. If already at the end of source, then does nothing.

Then the end of the filename

Total: C:\Users\j1sunx\Desktop\test\20181810.txt

The key is that the first * is very greedy.

Replacement solution:

Since you are already using VBA, I suggest you use either vba statements/functions like Dir and Name, or the FileSystemObject

Example of solution (might need improvements)

ChDir mydate
myline = Dir("*" & prvdate & "*")
While myline <> vbNullString
    Name myline As Replace(myline, prvdate, valdate)
    myline = Dir()
Wend

Thank you. I am new to VBA and thanks for offering the FSO method and detailed explanation. — Justin Sun, Dec 06 '18 at 03:36

Confusion on REN method

1 Answers1