Mass File Rename

[jesusoneez]

I'm not sure this is the right place nor am I sure Access is the right application but here goes.

I have around 1000 photos of students, and they are named (for example);

05162xxxxx.jpg

Where "xxxxx" is a random number. The filenames are in an Excel file, along with the student's name, student code and various other information...this can easily be imported into Access.

I need to rename each file as follows;

00xxxx.jpg

Where "xxxx" is the student code.

This is the only way our school management software will link to a students photo when viewing their records.

So, basically, I need Access to look at each filename in turn, compare it to the imported data, find the associated student code, and rename the file to that student code.

Is this possible?

 

[boblarson]

yes, it's possible. Here's a quote from lagbolt on another post here in the forum:

In a VBA code window goto Menu->Tools->References and set a reference to either "Microsoft Scripting Runtime" or "Windows Script Host Object Model". Check out the variety of methods available if you create a FileSystemObject. Use the GetFolder method to return a folder object, and enumerate its Files collection.

 

[battenberg]

would love a copy of that database or a sample of that code if you get it written

 

[KeithG]

Look up the File System Object and I am sure you will find some examples. Also if you take BobLarson advice with setting reffrence to the .Dll file then you will be able to use VBA's intellesense feature.

 

[boblarson]

Yeah, I hate trying to remember all of the properties and methods, so I always set the reference first so I can use the Intellisense. That's one of the reasons I have loved "Visual" basic.

 

[jesusoneez]

Thanks, I'll have a look at that.

 

[jesusoneez]

Right, I've been staring at this problem and am still not sure what to do. I've set up my references as required, but there's no real inbuilt help for Access for this (that I can see)...I've also searched the forum for FileSystemObject, but I can't find anything relevant.

I know (sort of) what my pseudo code is, I just can't translate to VBA. I believe I need to do an If Exists possibly. IF EXISTS "filename" Then Rename "filename" To [Photo_ID] & ".jpg".

I can't figure out what the VBA for this is, and I can't figure out how to loop through each file in the photo's directory, comparing it to each record in the database.

Any more help much appreciated, noting that I'm no programmer!

 

[gemma-the-husky]

if you are still lokking at this, perhaps this will help
i use this code, where ther following vars are external to this sub.

i was using this to rename a load of jpegs, hence the jpeg default

external dimmed variables or consts
newprefix is a textstring for the new filename
lastdir is the folder you are using

Private Sub btnProcess_Click()
Dim files As String
Dim nextfile As String
Dim fcount As Integer
Dim extension As String
Dim pos As Integer
Dim first As String

    If NewPrefix = "" Then
        MsgBox ("Please Enter A New Prefix")
        Exit Sub
    End If
    
    files = ""
    fcount = 0
    
    nextfile = Dir(lastdir, vbNormal)
    If nextfile <> vbNullString Then
        pos = InStr(1, ".", nextfile)
        If pos > 0 Then
            extension = Mid(nextfile, pos)
        Else
            extension = ".jpg"
        End If
        files = nextfile
        fcount = 1

        Name nextfile As NewPrefix & Format(fcount, "000" & extension)
        first = NewPrefix & Format(fcount, "000" & extension)
    Else
        MsgBox ("No Files In Folder")
        Exit Sub
    End If
    
    Do
        nextfile = Dir
        If nextfile <> vbNullString Then
            If nextfile = first Then Exit Do
            fcount = fcount + 1
            pos = InStr(1, ".", nextfile)
            If pos > 0 Then
                extension = Mid(nextfile, pos)
            Else
                extension = ".jpg"
            End If
            
            Name nextfile As NewPrefix & Format(fcount, "000" & extension)
        End If
    Loop Until nextfile = vbNullString
    
    MsgBox ("Completed: Files Renamed " & fcount)

end sub

 

[jesusoneez]

Thanks, I'll have a crack with that.

 

[OllyK]

If the data is in Excel, why not use VBA in excel to do the task rather than messing about putting it in Access first?

As mentioned, the FileSystem Object will provide all the functionality you need to rename files as well as providing other file handling features.

 

[kevinshmein]

I would like to suggest a program "KrojamSoft BatchRenameFiles Tool".
it's very effective for file rename BatchRenameFiles.org