How to rename a batch of files using spreadsheet` 1

[Jimbo2112]

Hi All,

I have the need to take a list of files named in an Excel spreadsheet and rename them.

Column A has the file names as they stand. All files are in a single directory.

Column B has the corresponding names that I want to convert the files to. I.E. A1=oldname.jpg A2=newname.png.

What would be the code required to automatically rename the files from column A to the filenames in column B?

In brief; I need to locate each named file in column A within a named directory and rename each file found with the name held in the adjacent cell in column B.

Many thanks!

Jimbo

 

[HarleyQuinn]

You can loop through the cells and use FileCopy() to make the new file, you can then Kill() the original.

Hope that helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Jimbo2112]

Harley,

Thanks for the quick response. I think you are on the right track. But my VBA skills are non existent! What code would I want for this? My current ability only extends to editing macros I have created!

Cheers

Jimbo

 

[xlbo]

Harley - you would be better off using the NAME function:

Name Statement

Renames a disk file, directory, or folder.

Syntax

Name oldpathname As newpathname

The Name statement syntax has these parts:

Part Description
oldpathname Required. String expression that specifies the existing file name and location — may include directory or folder, and drive.
newpathname Required. String expression that specifies the new file name and location — may include directory or folder, and drive. The file name specified by newpathname can't already exist.

Jimbo - you would need to loop through your selected records - syntax similar to

dim myRange as range

set myRange = range("A1:A1000")

For each c in myRange
'do stuff here
Next

To get the OldName and NewName that you need for the Name method, you would need to usethe OFFSET method

c in the sample I provided refers to a range i.e. each individual cell that will be looped through - you need to get that value as well as the value that is OFFSET by 0 rows and 1 column

That should be enough to get you going. See what you can pull together and post back if you have any specific issues

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bong]

Start with the directory name. Either it will come from a cell on the spreadsheet (strDname = cells(rownum, colnum).value), or it's defined in the code (strDname = "<path>"), or the user specifies it in an inputbox (strDname = application.inputbox("enter the path")).

Now you need to know the range (of rows) that has the filenames. The easiest way would be if the cells were contiguous starting at the first row. Then you can use the usedrange property:

for each r in sheets(n).usedrange.rows
  strOldname = strDname & sheets(n).cells(r,1)
  strNewname = strDname & sheets(n).cells(r,2)
  FileCopy strOldname, strNewname
  Kill strOldname
next

_________________
Bob Rashkin

 

[HarleyQuinn]

I'm on the train at the minute but I'll knock you something up when I get in if no-one else has.

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Jimbo2112]

Hey Guys,

Great responses! I am off home on the train now myself, but will catch up with the ideas and fashion a solution tomorrow.

Thanks a bunch!

Jimbo

 

[HarleyQuinn]

Good call Geoff, I knew there was a function that did it in one stroke but I couldn't remember the name!

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Jimbo2112]

Hi Chaps,

I have started to write the VBA fix for this. I used the code from Bong (many thanks) and chose the user input method to get the path.

The code is as below:

Sub renamefiles()
With (strDname = Application.InputBox("enter the path"))
For Each r In Sheets(1).UsedRange.Rows
  strOldname = strDname & Sheets(1).Cells(r, 1)
  strNewname = strDname & Sheets(1).Cells(r, 2)
  FileCopy strOldname, strNewname
  Kill strOldname
Next
End With
End Sub

I am getting the error:`Type Mismatch`

Any ideas?

Cheers

Jimbo

 

[xlbo]

Did you read my post - no need to use FileCopy as it then needs to KILL the old file - the NAME function simply renames an existing file...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Jimbo2112]

Hi Geoff,

I have had a pop at writing the code based on your solution, but I did not get far. Maybe I could write out what I need to happen and we could take it from there?

Define the path where the files to be renamed reside using
strDname = Application.InputBox("enter the path")

Define the last row that needs to be worked on using user input again (if this is needed)

define variables

start loop with loop size determined by last row value from user input

rename each file in turn with value of cell in adjacent column using offset command(Syntax for this bit is where I come unstuck)

end loop

Cheers

Jimbo

 

[Gavona]

To debug that error I would start by Stepping through your code using the watch window to see the values of strOldname & strNewname.

You have not posted your attempt at Geoff's solution so there is little to help you with. However, starting with the code you posted: Delete the FileCopy and Kill lines and replace with the Name statement.
In VBA help if you look up "Name Statement" you see not only the stuff Geoff posted but also a link to an example or two.

Gavin

 

[xlbo]

Yup - no need to change the existing code except to replace the FileCopy statement with the NAME statement

I have given you pretty much everything you need already in my 1st post. There is no need to use a WITH for the folder path - that is just a string you use to determine the exact file you want to rename. WITH is used to work with an OBJECT - the folder path is a STRING not an OBJECT so you cannot use it like that

Sub renamefiles()
dim thePath as string, lRow as long

thePath = "Put Your Folder Path in here"
lrow = sheets(1).cells(65536,1).end(xlup).row

For Each c In Sheets(1).range("A2:A" & lrow)
  strOldname = thePath & c.value
  strNewname = thePath & c.offset(0,1).value
  NAME strOldname strNewname
Next
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Jimbo2112]

Hi Chaps,

I have cracked it! Thanks a bundle for your help. I will use this as a platform to learn more me thinks! I slightly tweaked the code:

Sub renamefiles()
strDname = Application.InputBox("enter the path")
Dim thePath As String, lRow As Long

thePath = strDname
lRow = Sheets(1).Cells(65536, 1).End(xlUp).Row

For Each c In Sheets(1).Range("A1:A" & lRow)
  'MsgBox thePath & c.Value
  ActiveCell = c.Value & 1
  strOldname = thePath & c.Value
  strNewname = thePath & c.Offset(0, 1).Value
  Name strOldname As strNewname
Next
End Sub

I will now try and put more user interactivity into the script, such as checking the file exists before attempting to rename.

Cheers!

Jimbo

 

[Jimbo2112]

Hi Chaps,

I have amended the script to make it ignore empty cells now. I think this could be a useful (fairly) generic bit of code. Is there any merit in posting the code as a `Tip`?

Sub renamefiles()
strDname = Application.InputBox("Enter the path for filenames to be changed ")
Dim thePath As String, lRow As Long

thePath = strDname & "\"
lRow = Sheets(1).Cells(65536, 1).End(xlUp).Row

For Each c In Sheets(1).Range("A3:A" & lRow)
  strOldname = thePath & c.Value & ".png"
  strNewname = thePath & c.Offset(0, 2).Value & ".png"
  If (strOldname = thePath & ".png") Then
    GoTo SKIP
    Else
  If (strNewname <> thePath & ".png") Then
    'MsgBox c.Value
    'MsgBox strOldname
    'MsgBox strNewname
    Name strOldname As strNewname
  Else
SKIP:
  End If
  End If
Next
End Sub

Thanks again for your help, I have enjoyed this!

Jimbo

 

[PHV]

A simplified safer way:
For Each c In Sheets(1).Range("A3:A" & lRow)
strOldname = thePath & c.Value & ".png"
strNewname = thePath & c.Offset(0, 2).Value & ".png"
If Dir(strOldname) <> "" And Dir(strNewname) = "" Then
Name strOldname As strNewname
End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Jimbo2112]

Hi,

The `And` conditional extension will be used from now on!

Thanks

Jimbo

 

[Jimbo2112]

PH,

A question; What significance does the Dir() have when checking the file's existence? I am checking for a file and not a directory.

Cheers

Jimbo

 

[PHV]

Put the cursor inside the Dir word in your code and press the F1 key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Jimbo2112]

PH,

Great Tip!

Cheers

Jimbo