PDA

View Full Version : Excel and renaming files



Mike
20-11-2002, 07:07 PM
I have a spreadsheet at work with a column of file names (around 400 of them) and a column next to that column with what the file names should be changed to. Is there any way to automate the process so that I don't have to rename each file manually one-by-one? :D I'm lazy.

Mike.

Mike
20-11-2002, 07:09 PM
I suppose I should give more info :)

Excel 2002
Win 2000 pro (I think)
on a network (I doubt this will have any impact on anything)

The files are currently named something like "SA 23874" and they will be changed to something like "102145". File extension is to remain the same (.tif).

Any ideas?

mike.

Heather P
20-11-2002, 08:30 PM
Someone may think of an automatic way but in case they don't...

In Windows Explorer use the F2 key to rename rather than mouse clicks.

You could have the Excel and Explorer windows open together side by side and copy, F2, paste.

Mike
20-11-2002, 08:51 PM
> Someone may think of an automatic way but in case
> they don't...
>
> In Windows Explorer use the F2 key to rename rather
> than mouse clicks.
>
> You could have the Excel and Explorer windows open
> together side by side and copy, F2, paste.

Thanks Heather - that's what I've been doing... but when there's columns of similar looking numbers, its a very difficult method to follow :) I'm hoping for the automatic way :D

Mike.

Elephant
20-11-2002, 09:16 PM
If you have a certain rule you can apply, like replace "A" with "1" etc then this freeware tool can do it for you.

http://www.1-4a.com/rename/download.htm

Freeware too. :-)

robo
20-11-2002, 09:31 PM
Old name column A, new name column B, formula column C reads:

="REN "&A1&" "&B1

Note spaces after REN and in " ".

Copy down, then copy results and paste as values in a sheet, save as text file called newames.bat.

At the Dos command prompt get into the directory and type newnames.bat and press <enter>

Assuming everything is in one directory.

Or am I missing something and being an utter twat?
robo.

Heather P
20-11-2002, 09:54 PM
>>Or am I missing something and being an utter twat?

Mmmmm.... before doing as Robo suggests - enter Windows Explorer, find the directory with all your files in it and make a backup copy of the directory. THEN play with batch files.

-=JM=-
20-11-2002, 10:21 PM
That should do it I think robo, just make sure you do a test first.

robo
20-11-2002, 10:55 PM
I would hope that mike would take backups first, he's an IT professional after all.
robo.

Heather P
20-11-2002, 11:02 PM
Of course we trust Mike. But there may well be some less talented person searching this forum with a similar problem at a future date. A reminder about backups could prevent future howls of pain, rage and frustration.

mikebartnz
20-11-2002, 11:53 PM
I suspect it could be done using VBA.

Mike
21-11-2002, 07:58 AM
> Old name column A, new name column B, formula column
> C reads:
>
> ="REN "&A1&" "&B1
>
> Note spaces after REN and in " ".
>
> Copy down, then copy results and paste as values in a
> sheet, save as text file called newames.bat.
>
> At the Dos command prompt get into the directory and
> type newnames.bat and press <enter>
>
> Assuming everything is in one directory.

Brilliant, Robo, just brilliant :D - pity I had some dupilcates in my spreadsheet :) I didn't fill my spreadsheet properly to start with (silly me cutting corners again)

> Or am I missing something and being an utter twat?
> robo.

You're never an utter twat Robo (sucks up for some odd reason)

Ah well, I guess I should get back to work... see you all tonight :p

Mike.

Mike
21-11-2002, 08:26 AM
> pity I had some dupilcates in my spreadsheet I didn't fill my
> spreadsheet properly to start with (silly me cutting corners again)

AHA! I figured it out, and now it's completed. Thanks everybody :D Hmmm... Now I've got nothing to do for the next 4 hours (I guess that's how long it would have taken me to rename them all manually :))

What a team :D

Mike.

robo
21-11-2002, 08:44 AM
All part of the service, Ma'am.

<doffs cap, and looks for the next story out there in PC land>

Mike
21-11-2002, 09:28 AM
>>> <doffs cap, and looks for the next story out there in PC land>

How about something on all the different types of work us PressF1ers are doing in the IT industry :p

Mike.

Clueless
21-11-2002, 09:34 AM
>How about something on all the different types of work us PressF1ers are doing in the IT industry

>Mike.

Mike wanna start a new post then???????????

.Clueless