VBA Change FileName with Numbers sequentially

[malleshg24]

Hi Team,


Need help in modifying file name. In a folder I have input files which contain dates in filename.

Ex1 Daily_report_NY - 10-07-2019.xlsx
Ex2 Daily_report_NY - 10-08-2019.xlsx
Ex2 Daily_report_NY - 10-09-2019.xlsx

Expected result after Modifying filename should be numberwise
Daily_report_NY - 10-07-2019.xlsx after Modify it should be 1.xlsx
Daily_report_NY - 10-08-2019.xlsx after Modify it should be 2.xlsx
Daily_report_NY - 10-0*-2019.xlsx after Modify it should be 3.xlsx


Hardly there will be 5-6 files from Monday to Monday.
Please assist how to achieve this Task. Thanks for your help in advance. !!!


Regards,
mg

[mana]

Option Explicit


Sub test()
Dim srtl As Object
Dim p As String
Dim fn As String
Dim d As Date
Dim k As Long


Set srtl = CreateObject("system.collections.sortedlist")

p = CreateObject("wscript.shell").SpecialFolders("desktop") & "\test\"

fn = Dir(p & "Daily_report_NY - *.xlsx")

Do While fn <> ""
d = DateValue(Mid(fn, 19, 10))
srtl(d) = p & fn
fn = Dir()
Loop

For k = 1 To srtl.Count
Name srtl.getbyindex(k - 1) As p & k & ".xlsx"
Next

End Sub





マナ