Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 18
Default Renaming Files using VBA through Excel

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

:)


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 25
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 25
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default Renaming Files using VBA through Excel

Thank you all of you
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I rename files that are related. FinnSchH2 Excel Discussion (Misc queries) 2 August 31st 07 02:12 PM
Help to rename files Hilton Excel Worksheet Functions 7 July 7th 07 03:51 AM
Rename Files Dominique Feteau Excel Programming 1 July 13th 04 11:19 PM
Import and Rename Files rickey24[_7_] Excel Programming 1 July 7th 04 02:43 AM
Rename Files from Explorer BENNY Excel Programming 2 June 19th 04 07:22 PM


All times are GMT +1. The time now is 11:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"