renaming Multiple files in a directory Using Excel VBA

[Mannthegame]

Hi Ninjas,

What should be the code to execute the file renaming where given data is 2 columns Input and Output and input say starts from B4 till B500 and output as C4 till C500. given there are many blank spaces in between. (I would always have both the columns filled, fetching them from other tabs and I have been able to keep the file extensions to the names in both columns)

it can ask for a source folder or source folder can be mentioned in a particular cell.

Example:

Input Output
dc1.Jpg abc.Jpg
dc5.Jpg bcd.Jpg

dc23.Jpg xyz.Jpg



Would be really helpful if you can suggest something on it.

Eagerly waiting for a reply...

Regards
Mann

 

[Luke M]

How's this for a start?

Sub RenameThings()
Dim recCount As Long
Dim fPath As String
Dim newName As String
Dim oldName As String

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Where are files stored?"
    .AllowMultiSelect = False
    .Show
    fPath = .SelectedItems(1)
End With

'Check if user cancelled
If fPath = "" Then Exit Sub

'Error check
If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
End If

'Which cells to look at?
For recCount = 2 To 500
    'Provide feedback to user
    Application.StatusBar = "Reading line: " & recCount
    oldName = Cells(recCount, "B")
    newName = Cells(recCount, "C")
   
    If oldName <> "" Then
        Name fPath & oldName As fPath & newName
    End If
Next recCount
Application.StatusBar = False

End Sub

 

[Mannthegame]

Hi Luke,

First of all thank you for showing up and suggesting.

I am getting an error

I am also uploading my excel here, in the code I just changed the column names to actual. Rest was same.



I did come across a same excel file which has similier utility, but I need to make one for my work. Also attaching that, this might get you some more focus on it.

Looking forward to your support on it.

Rgds
Mann

 

[Marc L]

Hi,

use Dir VBA function to check either source file and destination file exist
(must see in VBA help, beginner level) …
​

 

[Mannthegame]

Hi Marc,

Thanks for showing up. I checked the DIR function if a directory exists or not.

What I want to achieve is finish a utility where my colleges can use it with a couple of clicks.

Any suggestion how should I achieve that...?

I have submitted the original excel sheet above too.

Would appreciate if get a way soon to do it while I google things up to close it.

Rgds
Mann

 

[Marc L]

In your case, that's files to check, not directory ‼
Dir function returns a empty string when a file does not exist …

My point of view is that kind of utility is a waste of time :
faster to directly rename files using Windows Explorer for example
than to enter and check each filename in worksheet columns !
​

 

[Mannthegame]

Hi Mark,

In my case it would be handy to formulate such tool where images are in huge numbers (100-1000).

I get pictures of many items (3-6) pics for each. I always get the image names in front of the item in an excel.

As the mentioned excel which is made by me, I have made it to formulate new names as per the item names which is my need.

I understood that we need 2 columns to rename bulk files 1st is input names and 2nd is output name. I made it, (Provided some blank spaces as example shared) Also tried .bat file batch convert but didn't work on all files. Need something more stable.

I have also shared an excel above which I found googling, it does the same thing but just can't use it for my work as yet.

I am sure will find out a way to do it, asking for some help since I am not a VB guy.

Rgds
Mann

 

[Marc L]

The error message is crystal clear :
you want to renane a file which not exists !

So just by correcting your worksheet …

Works also by batch command with a well thinking design filename !
For example same prefix filename to replace by a new one …
​

 

[Mannthegame]

Well, I appreciate your point but file name is correct.

Below is the input and output I want. (Taken from original file)

Input Output
DSC_0388.JPG red kurti with white flowers.JPG
DSC_0391.JPG orange and purple kurti.JPG


Would you be able to try this code in the uploaded excel?

Would be great if the utility works..

 

[Marc L]

Progress in code via step by step mode hitting F8 Key to find out
where error occurs … But this error is obviously clear : source file not found !

​

 

[Luke M]

check for extra spaces or other little characters. Just having one thing wrong will cause an issue.

 

[Mannthegame]

Hi Ninjas,

I understand the error which leads to file not found.

Checked the file names, which are same as original. Now is it possible due to long blank rows between some data?

Example:

Raw: Input: Output
1 DC2.JPG Image.JPG
.
.
.
36 DC37.JPG Image_1.JPG



is it the reason?

As an extended support, I would request any of you to try and use the excel - My Image Renaming Utility and check if things are okay in there.

Would it be possible?

Regards
Mann

 

[Marc L]

It depends on which codeline the error occurs …
​

 

[Mannthegame]

Hi All,

Just got it resolved.

Thanx for your time.

Rgds
Mann