Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

adjusting code rename files of different extensions

0

hello

I  have  this  code  it  renames  the  files  of  type .JPG   in  specific  folder but  I  have  many  differnt  files  extensions .PNG  , .PDF, .AVI and  so  on   so  I  search  for  way  to  make  it  more dynamically   to  include  all  of  diferent  files extensions   when  I  rename  the  files  based on   new names  in  COL B  after  match  old  names  in COL A 

Sub Rename_Files_from_List()
 Dim oFiles As Range, fPath As String
 Set oFiles = Range("A2", Range("A65536").End(xlUp))
 fPath = Range("D2").Value

 For Each cell In oFiles
 If Len(Dir(fPath & cell & ".JPG")) > 0 Then
 Name fPath & cell & ".JPG" As fPath & cell.Offset(0, 1) & ".JPG"
 Else
 MsgBox fPath & cell & ".JPG" & " doesn't exist"
 End If
 Next
End Sub

so  I  truly  appreciate   to  guide  me  how   I  do  that

Answer
Discuss

Answers

0

Hello Hasson,

There is something fundamentally wrong with your idea: it doesn't save time. By the time you set up the worksheet to run the code - once you have the code - the job can already be completed without code. The more files you have the more time you save by not having to prepare the workbook.

In Windows Explorer you can select multiple files in two ways.

  1. Click on a file you want to transfer. Then hold down the SHIFT key and select another file. All the files between the first click and the second will become selected.
  2. Click on a file you want to transfer. Then hold down the CONTROL key and select another file. Both files will be selected. Continue to holding down the CONTROL key and select as many files as you want.
    Clock on an already selected file again to deselect it.

You can use the second method after the first to select a big block of file names and then add more files from other parts of the list. Or youi can select a big block and then deselect some of them.

Now you can press COPY, go to another directory folder and press PASTE to make copies of the selected files there. Or you can use CUT and PASTE to move the files.

The use of a worksheet and VBA would make sense if you have a list of files from one directory and you want to move the files selectively to number of other directories, like all PDF files to directory A, all JPG files to Directory B, or by their file names or whatever. To set up this kind of list you would first have to have a list of destination folders, for example, different folder names in column D. But for 3 files no VBA is needed and for 100 files writing 100 destination paths is too much work. Therefore you need a list of folder paths and refer to them by only "A", "B" and "C" or "1", "2", 3".

Secondly, you would need rules by which files and folders are matched to each other. Your question includes no such rules. Therefore no intelligent code can be written.

If you want to persist in using VBA for this task the central code you will need is this:-

Name [old Name] As [new Name]

Both old name and new name must be complete, full file names consisting of Drive:Path\FileName.Extension. It seems that you only want to change the path. The function below will exchange an existing path for a new one. You can run the code from the Immediate pane with MsgBox NewFileName.

Function NewFileName() As String

    Dim FileName    As String
    Dim NewPath     As String
    Dim Sp()        As String

    NewPath = "C:\Users\PC WORLD\Destop\SSS\"       ' incl final backslash
    FileName = ThisWorkbook.FullName

    Sp = Split(FileName, "\")
    FileName = Sp(UBound(Sp))                       ' discard the old path
    Sp = Split(NewPath, "\")                        ' the final blackslash creates a blank element
    Sp(UBound(Sp)) = FileName
    NewFileName = Join(Sp, "\")
End Function

You can easily expand the utility of this function to let it retrieve names and paths from your worksheet or feed both or one of these strings as arguments in the function call.

Discuss

Discussion

thanks  
I  no  know  why  you  concentrate  moving   files  from directory  to another      my  main  Q  it's  just  about  rename  the  files ,   I  don't  ask move  files  from directory  to another    when  you  have  differnt  files  of  extension    to  rename and  use   the  manual  way  takes  more  time    by  the  way  this    not  real  data ,  just  I want to understand  my  idea
Hasson (rep: 30) Jun 12, '21 at 9:06 am
Hi Hasson,
If you wanted me to understand your idea your sample data didn't make a good job of that. A file name has 3 components: Path, name and extension. If you change the path you are moving the file. If you change the extension you won't be able to open it. And your code or data aren't about changing the name.
So, how do you propose to change the path without moving the file?
Variatus (rep: 4889) Jun 12, '21 at 10:05 am
 I don't   change  directory  at  all it 's  always in  D2     the  directory   contains  files  types  from pdf,docx, mp3,xls     ,the code     matches  files names  in  col A     with   the  files names   in  directory  based on D2    and  rename   the  all  files  from  type  of  JPG  in directory  based on COL B
Hasson (rep: 30) Jun 12, '21 at 3:41 pm
Please provide an example: Old name > New name, both to be with directory and extension.
Variatus (rep: 4889) Jun 12, '21 at 7:50 pm
I updated  my  file 
Hasson (rep: 30) Jun 13, '21 at 5:57 am
My answer remains unchanged. You should use
Name [Old name] As [New name]
to change the name and use the function I posted to modify the name. The task becomes neither easier nor harder if the path remains unchanged.
Variatus (rep: 4889) Jun 13, '21 at 8:40 pm
actually ,  I  face  a big  difficultly   to  deal with  this  code  especially   it's  function ,    I  put   the  function  in modula  but  I  no  know  how  works   , may  you  guide  me  how  can  I  run  this  code?   sorry  I don't  used to  with  functions
Hasson (rep: 30) Jun 14, '21 at 4:49 am
Such guidance is exactly what this forum is trying to provide. The method we employ is for eople to find the questions and others provide the answer. In the present thread you got an answer and a whole lot of guidance. Now we are at the end of the thread and you ask for more. That isn't the way to success. If the answer was too difficult for you then the fault must be with the question. Design a question the answer of which will be useful to you and ask it in another thread.
Variatus (rep: 4889) Jun 14, '21 at 5:53 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login