Use Excel 2007 to Rename Files in a Folder

[Christopher_Green]

Hi there,</SPAN>

When I scan documents onto our system using our scanner the scanner automatically creates the name of the file, I would like a Macro that will rename the files in a folder to the name I specify in a cell for example:</SPAN>

File Saved in: C:\My Documents</SPAN>
File Name: Scan01</SPAN>

Macro runs and asks me where the files are saved:</SPAN>

New File Name is picked up from column A and the macro renames the file.</SPAN>

Any advice or support on this would be great.</SPAN>

Thanks</SPAN>

 

[Damon Ostrander]

Hi Christopher,

You appear to be asking for the macro to name all the files with the same name, which can't be done because file names in a folder must be unique. Can you clarify how you envision providing unique names?

Damon

 

[Christopher_Green]

Hi Damon,

Thanks for your Response. Sorry i was not very clear in my first Post i hope this clear's things up.

I would like a Macro that would rename a file from its orginal name to one i specify in a cell, for example:
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Row/Column[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Current File Name:[/TD]
[TD]New File Name:[/TD]
[TD]File Location:[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Scan_0001[/TD]
[TD]Image_001[/TD]
[TD]My Documents[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Scan_0002[/TD]
[TD]Image_002[/TD]
[TD]My Documents[/TD]
[/TR]
</TBODY>[/TABLE]

I think this should be a straight foward macro to create but my skills are no where near good enough to do this.

Best Wishes

Christopher Green

 

[Damon Ostrander]

Hi again Christopher,

This macro code should do what you describe:

Sub RenameFiles()

   Dim iRow       As Long
   Dim OldName    As String
   Dim OldNameWxt As String   'old name with extension
   Dim NewName    As String
   Dim NewNameWxt As String   'new name with extension
   Dim Ext        As String   'file extension string (including ".")
   'start with row 2 (assume row 1 is header row)
   
   iRow = 2
   Do Until IsEmpty(Cells(iRow, "A"))
      'set working folder from string in column C
      ChDir Cells(iRow, "C")
      OldName = Cells(iRow, "A")
      NewName = Cells(iRow, "B")
      'get old name including file extension
      OldNameWxt = Dir(OldName & ".*")
      Ext = Right(OldNameWxt, Len(OldNameWxt) - Len(OldName))
      NewNameWxt = NewName & Ext
      'rename file
      Name OldNameWxt As NewNameWxt
      
      iRow = iRow + 1
   Loop
      
End Sub

For this code to work properly you should use a complete path (e.g., C:\My Documents) in column C.

Note it will apply the same file extension (e.g., ".jpg") to the renamed file.

 

[Christopher_Green]

Hi Damon

This macro works great.

Thanks for your help

 

[Christopher_Green]

Hi Damon,

I have been using the macro and sometimes it works but most times it doesn't.

The Run-Time Error i get is 5 "Invalid procedure or cell agrument" when i debug the macro it highlights this line

Ext = Right(OldNameWxt, Len(OldNameWxt) - Len(OldName))

I don't know if this has anything to do with it but i am renaming files that exist on network drives but i do use the file file path.

any ideas

Thanks again

Chris

 

[DWightman]

This Macro is exactly what I was looking for. I knew something like this had to be possible and I couldn't find anything in my Mr. Excel book that I thought fit. But this sounds perfect. Problem is, I have no idea how to use it!! Where do I paste this? Key this? Put this to make it work???