Rename files with VBA

[michelle439731]

Afternoon,

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,

Cheers,

Michelle

 

[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]

Hi,

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
Else
MsgBox "File not found"
End If
End Sub

Mike

 

[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)
Next
Else
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
Next
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
Regards;

Mike



michelle439731 wrote:

Rename files with VBA
11-Dec-09

Afternoon

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

Cheers

Michelle

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Professional Active Server Pages 3.0 (Wrox)
http://www.eggheadcafe.com/tutorial...4-b4ea9b62129e/professional-active-serve.aspx

 

[michelle439731]

Thanks guys that's really usefull. I've got my code working now.

 

[J_Knowles]

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
replacement.

 

[J_Knowles]

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.

Thanks,

 

[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
this..

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...

http://support.microsoft.com/kb/920229

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