Trouble removing/changing csv filenames via vba

[DRANDON]

Hello all. I have code that I've put together in an effort to remove a section of the filename in a series of csv files in a folder. Example "fhw_cpa_denial_report_20180802.csv" and "hmmc_cpa_denial_report_20180802.csv". They all vary in length but all I need to remove is anything after the underscore (essentially the underscore and the date).

Here is what I have:

Sub CleanFilenames()
Dim LastDot As Long, ThePath As String, OldFilename As String, NewFilename As String
ThePath = "C:\Users\ldc580\Desktop\New folder"
OldFilename = Dir(ThePath & "*.csv")

Do While Len(OldFilename)
NewFilename = Application.Remove(OldFilename, Len(OldFilename) - 13, 0, "")
Name Path & OldFilename As Path & NewFilename
OldFilename = Dir()
Loop
End Sub

It does not work. Any help would be greatly appreciated as it will automate a process that takes to long. Thank you in advance..

 

[Fluff]

How about

NewFilename = Left(OldFilename, InStrRev(OldFilename, "_") - 1)
Name ThePath & OldFilename As ThePath & NewFilename & ".csv"

This assumes that you want to keep the file extension

 

[DRANDON]

I had to change this "C:\Users\ldc580\Desktop\New folder" to this "C:\Users\ldc580\Desktop\New folder" and implemented your suggestion and it worked instantly! Fantastic! Many many thanks

 

[Fluff]

Glad to help & thanks for the feedback