Use Excel 2007 to Rename Files in a Folder

Christopher_Green

New Member
Joined
Apr 28, 2013
Messages
12
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Apr 28, 2013
Messages
12
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:
Row/ColumnColumn AColumn BColumn C
1Current File Name:New File Name:File Location:
2Scan_0001Image_001My Documents
3Scan_0002Image_002My Documents

<TBODY>
</TBODY>

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again Christopher,

This macro code should do what you describe:

Code:
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

New Member
Joined
Apr 28, 2013
Messages
12
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
Code:
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

New Member
Joined
Oct 29, 2015
Messages
12
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???
 

Forum statistics

Threads
1,081,862
Messages
5,361,740
Members
400,653
Latest member
ProParadox

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top