S
simon
Just thought i'd give a couple of things back to this group as it has
helped me so much.
When I was setting up my website i had to rename thousands of images
with their product codes and was a nightmare. I had the old names and
new names in a spread sheet and so wrote this macro to rename the
files.
Sub FileRenamer()
'
' FileRenamer Macro
'
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
If Dir(cell.Value) <> "" And cell.Offset(0, 1).Value <> ""
Then
Name cell.Value As cell.Offset(0, 1).Value
End If
Next cell
'
End Sub
on sheet one of my spread sheet in column A i had the old file name
including the whole file path.
C:\Documents and Settings\SimonBucknall\Desktop\Images\002 9801.jpg
and in column B i had the new name including file path
C:\Documents and Settings\SimonBucknall\Desktop\images webcoded\2.jpg
the macro then reads column A and if it finds the file replaces it's
name/path with the coresponding value in column B.
Make sure both the folders exist, I copied the file path name from one
of the origonal image property boxes and pasted it in to another
spread sheet, in the next column pasted the file name and in the third
the file suffix.
Column A
C:\Documents and Settings\SimonBucknall\Desktop\Images\
Column B
002 9801
Column C
..jpg
saved the file as a csv file, opened it in word and using the replace
function removed the ",". then saved it again and opened the file in
excel. this was the only way i could merge the cells. I suspect there
is a much easier way to do this but i couldn't find it - if there is
please let me know. I then pasted this new column in to my replace
spreadsheet. Repeat the process for the destination file name, run the
macro and you should see all your files renamed in the second folder
(or same if you used the same file path). Those not renamed will
remain unchanged.
I hope this is of use to someone, it has been invalueable to me. Sorry
if the code is not very "correct" or neat but I had to teach myself.
If you can improve on it the pleas let me know.
Simon B
www.ultimatebikes.com
running on office 2007
helped me so much.
When I was setting up my website i had to rename thousands of images
with their product codes and was a nightmare. I had the old names and
new names in a spread sheet and so wrote this macro to rename the
files.
Sub FileRenamer()
'
' FileRenamer Macro
'
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
If Dir(cell.Value) <> "" And cell.Offset(0, 1).Value <> ""
Then
Name cell.Value As cell.Offset(0, 1).Value
End If
Next cell
'
End Sub
on sheet one of my spread sheet in column A i had the old file name
including the whole file path.
C:\Documents and Settings\SimonBucknall\Desktop\Images\002 9801.jpg
and in column B i had the new name including file path
C:\Documents and Settings\SimonBucknall\Desktop\images webcoded\2.jpg
the macro then reads column A and if it finds the file replaces it's
name/path with the coresponding value in column B.
Make sure both the folders exist, I copied the file path name from one
of the origonal image property boxes and pasted it in to another
spread sheet, in the next column pasted the file name and in the third
the file suffix.
Column A
C:\Documents and Settings\SimonBucknall\Desktop\Images\
Column B
002 9801
Column C
..jpg
saved the file as a csv file, opened it in word and using the replace
function removed the ",". then saved it again and opened the file in
excel. this was the only way i could merge the cells. I suspect there
is a much easier way to do this but i couldn't find it - if there is
please let me know. I then pasted this new column in to my replace
spreadsheet. Repeat the process for the destination file name, run the
macro and you should see all your files renamed in the second folder
(or same if you used the same file path). Those not renamed will
remain unchanged.
I hope this is of use to someone, it has been invalueable to me. Sorry
if the code is not very "correct" or neat but I had to teach myself.
If you can improve on it the pleas let me know.
Simon B
www.ultimatebikes.com
running on office 2007