Renaming Pictures files by comparing a list

[brjames32]

Hi people

I have around 1300 jpeg's that need to be renamed based on the value in an excel spreadsheet.

What I need to be able to do is parse each jpeg in a directory, compare the filename to a list of names in a column in the spreadsheet and if it exists, rename it with the corresponding value from the next column and then move to the next file and so on and so on.

I have the following code for opening the spreadsheet and reading the files but am not sure how to compare the spreadsheet values to the filenames and then rename them:

Imports System
Imports System.IO
Imports System.IO.File

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Open Excel file and read info
        Dim cnStudents As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Logon\WORK\ben\Desktop\Names.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")
        Dim daStudents As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnStudents)

        Dim dsStudents As New DataSet
        daStudents.Fill(dsStudents)

        'Read picture files from folder

        Dim s As String
        For Each s In System.IO.Directory.GetFiles("\\logon\shared\Penalty Points\Backup\")

        ' do comparing and renaming here ??        

        Next s




    End Sub
End Class

Can anyone help me out??

Thanks in advance

[brjames32]

Anyone??

[dglienna]

If you type S. you will get values that you can use. You'd want to split the file name, into name, disk folder, and filetype. You might want to create a List(of T).

Search thru the 101 Samples found here.

[brjames32]

Hi dglienna

thanks for the reply. I think a bit overkill for what I need. All I need to be able to do is compare the filename with values in Column A in the spreadsheet. If it exists in that column then rename the File with the contents of the Value in column B.

I have managed to write this in VBA in the spreadsheet:

Option Explicit

Sub RenameFiles()

Dim lngRow As Long, lngRowCount As Long, lngStartRow As Long
Dim vPath, vFile As String
   On Error Resume Next
   
   lngRowCount = Cells(1325, "A").End(xlUp).Row
   lngStartRow = 1
   Application.ScreenUpdating = False

vPath = "C:\Photos\" '' new
vFile = Dir(vPath & "*.jpg") '' new

Do While vFile <> "" '' new

For lngRow = lngStartRow To lngRowCount
      'If Len(Cells(lngRow, "E").Value) > 0 And Len(Cells(lngRow, "B").Value) > 0 Then
        If vFile = Cells(lngRow, "E").Value & ".jpg" Then
            Name vPath & vFile As vPath & Cells(lngRow, "B").Value & ".jpg" '' new
            vFile = Dir '' new
                'Name strPath & Cells(lngRow, "E").Value As strPath & Cells(lngRow, "B").Value
                Cells(lngRow, "F").Value = "Renamed"
         Else
         Cells(lngRow, "F").Value = "Not Renamed"
      End If
      'End If
   Next lngRow
Loop  '' new

Application.ScreenUpdating = True
End Sub

It renames the files, but they aren't in the correct order. The files are pics of students in the school I work in and the filenames are their student id. A new attendance system has created new Student IDs for them - hence the need to rename them to the new IDs.

Any ideas?

[DataMiser]

Hmm.. I'm thinking that there is no need to look up the files at all.

If you create a loop that loops through all the records in the spread sheet and then within that loop use a try catch and within the Try add a line to rename a file by the name of column1 to the name in column 2 it should get them all correctly assuming of course that none of your existing filenames are the same as any of the new ones as this would cause a problem.


ETA: not sure what you mean about them not being in the correct order. Explorer sorts files based on your settings but does not really show you how the files are actually arranged.

[brjames32]

Hey DataMiser

Thanks for the reply. Will try that out and let you know how I get on

[brjames32]

All sorted! In the end I created a macro within the apreadsheet with the following code:

Sub RenameFiles()
Dim SourceName As String
Dim DestName As String
Dim row As Long

For row = 1 To Sheets("Sheet1").Range("A1").End(xlUp)

On Error Resume Next
    SourceName = Sheets("sheet1").Cells(row, 1).Value
    DestName = Sheets("sheet1").Cells(row, 2).Value

    
    Name "\\logon\work\ben\desktop\photos\" & SourceName & ".jpg" As "\\logon\work\ben\desktop\photos\renamed\" & DestName & ".jpg"
    
    
Next

End Sub