Rename files with VBA




I want to rename some files in a folder using vba.
Preferable what I would like to do is
a)find file x in folder a
b)rename file x to xy

Please can you help,



Rick Rothstein

You can use the Name...As statement to do this...

Name "c:\Dir1\Dir2\OldFileName.ext" As "c:\Dir1\Dir2\NewFileName.ext"

Just change the example path, filename and extension to those for your
actual situation.

Note that you must provide the full path for both the old file name and the
new filename for this statement to work.

Mike H


Change the filenames and path to suit and try this

Sub DoSomething()
MyPath = "C:\"
MyFile = "Book2.xls"
NewName = "Othername.xls"
If Dir(MyPath & MyFile) <> "" Then
Name MyPath & MyFile As MyPath & NewName
MsgBox "File not found"
End If
End Sub


Mike Spencer

Hi Michelle;
I went looking for this not to long ago to rename a couple thousand files at a time to re-organize archive files.

I found parts of code all over the place, beginning with the following.

- Find the Files in a directory and list them in the workbook -

Sub ListAllFiles()

Dim fs As FileSearch, ws As Worksheet, i As Long
Set fs = Application.FileSearch
With fs
.SearchSubFolders = ActiveSheet.Cells(1, 11).Value ' set to true if you want sub-folders included
.FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
.LookIn = ActiveSheet.Cells(1, 10).Value 'modify this to where you want to serach
If .Execute > 0 Then
Set ws = ActiveSheet
For i = 1 To .FoundFiles.Count
ws.Cells(i, 1) = .FoundFiles(i)
MsgBox "No files found"
End If
End With
End Sub

- Rename Files -

Sub RenameFiles()
For R = 1 To Range("A1").End(xlDown).Row
OldFileName = Cells(R, 1).Value
NewFileName = Cells(R, 2).Value
On Error Resume Next
If Not Dir(OldFileName) = "" Then Name OldFileName As NewFileName
On Error GoTo 0
End Sub

Ok, so the first procedure needs to know what directory to search, {read the comments for each line once you copy this back into excel} and it will list all the files in that folder and print them in Column A.

The RenameFiles sub goes through the entire range, taking the old filename value from Column A, and the new filename value from Column B and renames the file(s)

Best of Luck


michelle439731 wrote:

Rename files with VBA


I want to rename some files in a folder using vba
Preferable what I would like to do i
a)find file x in folder
b)rename file x to x

Please can you help



Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice
Professional Active Server Pages 3.0 (Wrox)


Mike Spencer,

Thanks for the code. I'm getting this error:
"object does not support this action"
Set fs = Application.FileSearch

The FileSearch is hidden in 2007. Is there a work around?

Rick Rothstein

You have now discovered why you should *always* tell us what version of
Excel you are using when you ask a question on these newsgroups...
FileSearch was removed from Excel 2007. Here is a forum link that has some
alternatives shown as links within the discussions... take your pick for a


Thanks for the reply. I was not the OP, but was an interested reader in the
code. Could you provide the links to the newsproups about alternatives to
FileSearch when using 2007.


Rick Rothstein

Sorry about not getting back to you sooner. I can't find the link I had
located when I first answered you, but if you do a Google search using

excel 2007 filesearch

or this...

excel 2007 filesearch replacement

you will find, one, that the FileSearch object has been removed from the
Office 2007 product line and, two, several links containing replacement
options for it. This is how Microsoft handles it...

look at the WORKAROUND section for the link to their coded solution.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question