macro for removing the date from a file

[pedenspeed]

Hello all,

I have seen the code posted to rename a file but for some reason no matter what I do, I get a run-time error '53': File not found. I have tried several ways, but this is what I currently have:

Sub removedate()

Dim oldFileName As String
Dim newFileName As String

oldFileName = Dir("C:\Users\k1rbr0\Desktop\afsm100 at risk\Avenger\Outputs\AFSM100Benchmark*.*")
newFileName = "C:\Users\k1rbr0\Desktop\afsm100 at risk\Avenger\Outputs\Data\AFSM100Benchmark.xlsx"

Name oldFileName As newFileName

End Sub

Any ideas on why its not working? I have tried without the Dir and with Dir and when Debugging, it shows me that oldFileName has the correct file string, and newFileName has the correct string (full path).

Is there another way to rename the file, or even create the new workbook without the date keeping the original?

Any help is appreciated!!

 

[Marc L]

Hi,​

​

if the source filename string is correct so check its attributes and if the destination path exists …​

 

[Alex Blakenburg]

I am sure there are much shorter ways of doing this but the below might work for you.
Ideally this needs to be modified for:-

• If you have a way of finding the date in the name eg it is preceded with a space or "_" you could find the date and do a replace
• This will fail if the new name already exists, so ideally you would test to see if it exits first or at least handle the error.

As far as I could tell your dir line was only returning the "name" of the file and you need the name & path for the rename line.

Sub RenameFileRemoveDate()

    Dim oldFileName As String
    Dim newFileName As String
   
    Dim oldFilePath As String
    Dim oldFileNameSearch As String
    Dim oldFileFullName As String
    Dim newFileFullName As String
    
    oldFilePath = "C:\Users\k1rbr0\Desktop\afsm100 at risk\Avenger\Outputs"
    oldFileNameSearch = "AFSM100Benchmark*.*"
    oldFileFullName = oldFilePath & "\" & oldFileNameSearch
    newFileName = "AFSM100Benchmark.xlsx"
    
    oldFileName = Dir(oldFileFullName)
    oldFileName = Replace(oldFileFullName, oldFileNameSearch, oldFileName)
    newFileFullName = oldFilePath & "\" & newFileName
    
    Name oldFileName As newFileFullName
End Sub

 

[pedenspeed]

Thank you both for the replies!! You were 100% correct Alex I was only returning the file name. Your code worked perfectly. Thank you so much.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.