How to Rename All Images Names in a Folder According to a List of Cells in Excel?


Have you ever had to take on the challenging duty of renaming a sizable number of image files in accordance with particular data kept in an Excel spreadsheet? Each file must be manually renamed, which can be laborious and error-prone. Luckily, using the power of programming and automation, there is a more effective approach to complete this chore. This tutorial will show you how to rename every photograph in a folder based on a list of cells in an Excel file by taking you step-by-step through the procedure.

Rename All Images Names in a Folder According to a List of Cells

Here we will first get the original names of the images on the sheet, then rename them. So let us see a simple process to know how you can rename all the images in a folder according to a list of cells in Excel.

Step 1

Consider any Excel sheet.

First, right-click on the sheet name and select View code to open the VBA application.

Right-click > View Code.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Code

Sub PictureNametoExcel()
   Dim I As Long
   Dim xRg As Range
   Dim xAddress As String
   Dim xFileName As String
   Dim xFileDlg As FileDialog
   Dim xFileDlgItem As Variant
   On Error Resume Next
   xAddress = ActiveWindow.RangeSelection.Address
   Set xRg = Application.InputBox("Select a cell to place name list:", "Rename All Images", xAddress, , , , , 8)
   If xRg Is Nothing Then Exit Sub
   Application.ScreenUpdating = False
   Set xRg = xRg(1)
   xRg.Value = "Picture Name"
   With xRg.Font
   .Name = "Arial"
   .FontStyle = "Bold"
   .Size = 10
   End With
   xRg.EntireColumn.AutoFit
   Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
   I = 1
   If xFileDlg.Show = -1 Then
      xFileDlgItem = xFileDlg.SelectedItems.Item(1)
      xFileName = Dir(xFileDlgItem & "")
      Do While xFileName <> ""
         If InStr(1, xFileName, ".jpg") + InStr(1, xFileName, ".png") + InStr(1, xFileName, ".img") + InStr(1, xFileName, ".gif") + InStr(1, xFileName, ".ioc") + InStr(1, xFileName, ".bmp") > 0 Then
            xRg.Offset(I).Value = xFileDlgItem & "" & xFileName
            I = I + 1
         End If
         xFileName = Dir
      Loop
   End If
   Application.ScreenUpdating = True
End Sub

Step 3

Then click F5 to run the module. Then select a single cell to place the image names and click OK.

F5 > Select Cell > Ok.

Step 4

Then select the folder containing the images and click OK.

Select Folder > OK.

Step 5

Then you will see that the original names will be placed on the sheet. Now again in VBA, click on Insert, select Module, and copy the below code into the text box.

Insert > Module > Copy.

Code

Sub RenameFile()
   Dim I As Long
   Dim xLastRow As Long
   Dim xAddress As String
   Dim xRgS, xRgD As Range
   Dim xNumLeft, xNumRight As Long
   Dim xOldName, xNewName As String
   On Error Resume Next
   xAddress = ActiveWindow.RangeSelection.Address
   Set xRgS = Application.InputBox("Select Original Names(Single Column):", "Rename All Images", xAddress, , , , , 8)
   If xRgS Is Nothing Then Exit Sub
   Set xRgD = Application.InputBox("Select New Names(Single Column):", "Rename All Images", , , , , , 8)
   If xRgD Is Nothing Then Exit Sub
   Application.ScreenUpdating = False
   xLastRow = xRgS.Rows.Count
   Set xRgS = xRgS(1)
   Set xRgD = xRgD(1)
   For I = 1 To xLastRow
      xOldName = xRgS.Offset(I - 1).Value
      xNumLeft = InStrRev(xOldName, "")
      xNumRight = InStrRev(xOldName, ".")
      xNewName = xRgD.Offset(I - 1).Value
      If xNewName <> "" Then
         xNewName = Left(xOldName, xNumLeft) & xNewName & Mid(xOldName, xNumRight)
         Name xOldName As xNewName
      End If
   Next
   MsgBox "Congratulations! You have successfully renamed all the files", vbInformation, "Rename All Images"
   Application.ScreenUpdating = True
End Sub

Step 6

Then click F5 to run the module. Then select the range of cells containing the original names and click OK.

Select Cells > Ok.

Step 7

Then select the new names and click OK to complete the task.

Select Cells > Ok.

This is how you can rename all the images in a folder in Excel.

Conclusion

In this tutorial, we have used a simple process to show how you can rename all images in a folder according to a list of cells in Excel to highlight a particular set of data.

Updated on: 13-Sep-2023

288 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements