Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename files with VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename files with VBA
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. -- Rick (MVP - Excel) "michelle439731" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename files with VBA
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 "michelle439731" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Files using VBA through Excel
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 is a)find file x in folder a b)rename file x to xy 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/tutorials...ive-serve.aspx |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Files using VBA through Excel
Thanks guys that's really usefull. I've got my code working now.
:) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Files using VBA through Excel
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? -- Data Hog "Mike Spencer" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Files using VBA through Excel
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. -- Rick (MVP - Excel) "J_Knowles" wrote in message ... 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? -- Data Hog "Mike Spencer" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Files using VBA through Excel
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, -- Data Hog "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Files using VBA through Excel
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. -- Rick (MVP - Excel) "J_Knowles" wrote in message ... 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, -- Data Hog "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Files using VBA through Excel
Thank you all of you
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I rename files that are related. | Excel Discussion (Misc queries) | |||
Help to rename files | Excel Worksheet Functions | |||
Rename Files | Excel Programming | |||
Import and Rename Files | Excel Programming | |||
Rename Files from Explorer | Excel Programming |