Renaming files with VBA

Question

I'm having issue renaming files in vba.

I only know a certain part of the files name, for instance that it starts with happy. I want to be able to rename this file to whatever I want. There will only be one file called happy*. I've got the below code but it's giving me a "File not found" error on the Name ffile As NewName

Sub ReNaming()

Dim ffile As String

ffile = Dir("h:\folder1\happy*")
NewName = "yellow.xlsx"
Name ffile As NewName


End Sub

I'm aware this is probably not the correct way to be doing it but the wildcard part of the problem is causing all the issues!

Any help would be great.

Thanks

Answer 1

It's probably best to be as specific and thorough as you can be given the available information.

ffile = Dir("h:\folder1\happy*.xlsx")
NewName = "yellow.xlsx"
Name "h:\folder1\" & ffile As "h:\folder1\" & NewName

Although the .xlsx is not necessary, it makes little sense to be renaming anything that wasn't an .xlsx workbook to an .xlsx workbook.

Answer 2

The problem is that Dir() returns the file name but not the full path. You can put Debug.Print ffile after the Dir to see what it returns. If that file doesn't exist in the directory that you are running your VBA script in then you will get that error. You could do something like:

Sub ReNaming()

    Dim ffile As String
    Dim pathname As String

    pathname = "h:\folder1\"
    ffile = Dir(pathname & "happy*")
    ffile = pathname & ffile
    NewName = "yellow.xlsx"
    Name ffile As pathname & "NewName" 'or just "NewName" if you *want* to change the folder
End Sub