asked on

Need VBA code (EXCEL or MS ACCESS) to rename files with information found in cells in a spreadsheet

I need to use Excel VBA to rename files in a folder based on two columns in a spreadsheet. This is to add a "BATES" number for a legal proceeding.
If no EXCEL experts have a solution, I would also welcome a solution that works in MS Access.

So the original file name is in COLUMN B, the information I want to add to each filename is in COLUMN A

FYI....
I brought the list in COLUMN B by doing a "dir *.tif /on /b >tiflist.txt" from a command line, then copy and pasted the contents of the text file into excel.

Ideally it would copy the file to a new location, renaming it along the way.... but if it renames it "in place", thats ok too.

Thanks in advance for any help.

Bill Prew

What should the new file name be, for example on row 1?

0000000001_100274_57213.tif
100274_57213_0000000001.tif
other?

»bp

TechGuise

ASKER

0000000001_100274_57213.tif

Bill Prew

If it were me, I would just add this formula to column C,

=CONCATENATE("copy ""c:\dir1\",B1,""" ""c:\dir2\", A1, B1, """")

copy down as needed, then copy column C into Notepad or a text editor and save as a simple .BAT file. At a command prompt run that .BAT file.

Right now it will build lines that look like:

copy "c:\dir1\100274_57213.tif" "c:\dir2\0000000001_100274_57213.tif"

Adjust the paths in the CONCATENATE() to reference your source and destination folder (make sure dest folder exists). It will copy the files to the new folder and give them the new name in the process.

»bp

TechGuise

ASKER

Interesting method.
I can definitely see how that will work.

But I'd rather make something more easily repeatable and something I can eventually add some checks and confirmations too. I'm afraid if the batch file fails, it will just fail.... with no real feedback on issue or any kind of placeholder.

John Tsioumpris

Well Excel should be fine but if you want real control then Access is much better
Take a look at my Attachment
Just put some files in the "Source" Folder and they will be Renamed in the "Renamed" Folder
Because i don't have handy something like the Excel data you showed i just added some Random numbers and "ABC"
You can tweak it to your liking
EE.zip

TechGuise

ASKER

Thanks John for the reply.

I'm not seeing how that will add information from adjacent fields. That is the part of the code that I don't know how to do.

If you happen to reply again, would you mind posting the code in your response (downloading and extracting a zip file is a little cumbersome)

Thanks very much for help.

John Tsioumpris

Here is the code

Private Sub cmdFindAndRename_Click()
Dim InputFile As String
Dim OutputFile As String
Dim strFile As String

    strFile = Dir(Application.CurrentProject.Path & "\Source\")
    Do While Len(strFile) > 0
    FileCopy Application.CurrentProject.Path & "\Source\" & strFile, Application.CurrentProject.Path & "\Renamed\" & strFile
    Name Application.CurrentProject.Path & "\Renamed\" & strFile As Application.CurrentProject.Path & "\Renamed\" & Rnd(100) & "ABC" & strFile
        strFile = Dir

    Loop
End Sub

Open in new window

Probably you need to pay attention to the following lines

strFile = Dir(Application.CurrentProject.Path & "\Source\") --> Here you pickup the 1st file in Folder...you can do a check to see if it matches your file (like Excel)

FileCopy Application.CurrentProject.Path & "\Source\" & strFile, Application.CurrentProject.Path & "\Renamed\" & strFile -- > I do a copy from Source to Renamed

Name Application.CurrentProject.Path & "\Renamed\" & strFile As Application.CurrentProject.Path & "\Renamed\" & Rnd(100) & "ABC" & strFile --> The Actual Renaming...From the Original Named File to the Renamed one ...you can do whatever match you want.

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TechGuise

ASKER

Thanks guys!
Bill - Exactly what I was looking for. The outcome of each file was needed more than I knew (colors where a real nice touch)

John - I'm sure you solution would have worked, but I couldn't get my brain past the Rnd() and figure out the code to grab info from adjacent column. Simple once I saw it done. doh!
The COPY method was actually pretty ingenious.... bet I use that in the future for quick fixes.

Thanks again gentlemen

Bill Prew

Welcome, glad that was helpful, and thanks for the feedback.

»bp