Rename and move files to a new location

chrysanthi

New Member
Joined
Dec 6, 2019
Messages
12
Office Version
365, 2010
Platform
Windows
Dear all,
I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following: Column E & ( & column F & ) & Rev & Column C.

Then each one of them must be send to a new folder location which is C:\Users\chma\Desktop\F2 - Copy & Column G (but the word PROJECT_483 must be repplaced by the C:\Users\chma\Desktop\F2 - Copy)

ABCDEFG
FILEDOCUMENT IDENTIFICATIONISSUEDATEKKSTITLEPATH
483-21-DT-M-00300-Ed001.pdf483-21-DT-M-00300
1​
05-12-19​
111915-21-YDS-MDD-EA-00300P&ID SYMBOLOGY AND GENERAL NOTESPROJECT_483\200 Mechanical Area\221 Systems\221.01 P&IDs
483-21-FV-D-00001-Ed001.pdf483-21-FV-D-00001
1​
05-12-19​
Monthly Progress Report (NOVEMBER 2019)PROJECT_483\000 Project Management\001 General\001.03 Progress Reports
483-21-S-M-20250-Ed002.pdf483-21-S-M-20250
2​
04-12-19​
111915-21-PA_-MHP-EA-20250COOLING TOWERS (INDUCED DRAFT) DATA SHEETPROJECT_483\200 Mechanical Area\221 Systems\221.05 Data Sheets


Thank you
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,791
Office Version
2007
Platform
Windows
Hi @chrysanthi, welcome to the board!

For example:
This file

483-21-DT-M-00300-Ed001.pdf

It will look like this:
C:\Users\chma\Desktop\F2 - Copy\200 Mechanical Area\221 Systems\221.01 P&IDs\
111915-21-YDS-MDD-EA-00300 (P&ID SYMBOLOGY AND GENERAL NOTES) Rev1 .pdf

It was missing that you mention the source folder where the current files are.

--------------------------

Try this.
Change "C:\folder\origin\" for your origin folder
Files are copied for your review.
If all good, you can enable this line to be deleted from the source folder.
Kill Path1 & wOld


VBA Code:
Sub Rename_and_move_files()
  Dim Path1 As String, Path2 As String, Path3 As String
  Dim wNew As String, wOld As String, i As Long
 
  Path1 = "C:\folder\origin\"                 'origin
  Path2 = "C:\Users\chma\Desktop\F2 - Copy\"  'destination
 
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Path3 = Replace(Range("G" & i), "PROJECT_483", Path2)
    If Right(Path3, 1) <> "\" Then Path3 = Path3 & "\"
    wOld = Range("A" & i).Value
    wNew = Range("E" & i).Value & " (" & Range("F" & i).Value & ") " & _
           "Rev" & Range("C" & i).Value & ".pdf"
    If Dir(Path1 & wOld) <> "" Then
      If Dir(Path3, vbDirectory) <> "" Then
        FileCopy Path1 & wOld, Path3 & wNew
        'Kill Path1 & wOld
      End If
    End If
  Next
End Sub
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
The Name statement can be used instead of FileCopy / Kill. Name can move a file across drives.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,791
Office Version
2007
Platform
Windows
The Name statement can be used instead of FileCopy / Kill. Name can move a file across drives.
Thanks for the comment.
But as a security measure, I just wanted the OP to review its files before renaming everything.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
Always a good idea.
 

chrysanthi

New Member
Joined
Dec 6, 2019
Messages
12
Office Version
365, 2010
Platform
Windows
Thank you both for the help and your response!! I will test it and let you know! One thing i forgot to ask... In case one of the cells is empty, a space or a "-" should be placed in the file name
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,791
Office Version
2007
Platform
Windows
Thank you both for the help and your response!! I will test it and let you know! One thing i forgot to ask... In case one of the cells is empty, a space or a "-" should be placed in the file name
Hi@chrysanthi, I put the macro updated with the "-" and with Tetra's suggestion:

VBA Code:
Sub Rename_and_move_files()
  Dim Path1 As String, Path2 As String, Path3 As String
  Dim wNew As String, wOld As String, i As Long
  Dim c1 As String, c2 As String, c3 As String
 
  Path1 = "C:\folder\origin\"                 'origin
  Path2 = "C:\Users\chma\Desktop\F2 - Copy\"  'destination
 
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Path3 = Replace(Range("G" & i), "PROJECT_483", Path2)
    If Right(Path3, 1) <> "\" Then Path3 = Path3 & "\"
    wOld = Range("A" & i).Value
    c1 = IIf(Range("E" & i).Value <> "", Range("E" & i).Value, " - ")
    c2 = IIf(Range("F" & i).Value <> "", Range("F" & i).Value, " - ")
    c3 = IIf(Range("C" & i).Value <> "", Range("C" & i).Value, " - ")
    wNew = c1 & " (" & c2 & ") " & "Rev" & c3 & ".pdf"
    If Dir(Path1 & wOld) <> "" Then
      If Dir(Path3, vbDirectory) <> "" Then
        Name Path1 & wOld As Path3 & wNew
      End If
    End If
  Next
End Sub
 

chrysanthi

New Member
Joined
Dec 6, 2019
Messages
12
Office Version
365, 2010
Platform
Windows
Hi@chrysanthi, I put the macro updated with the "-" and with Tetra's suggestion:

VBA Code:
Sub Rename_and_move_files()
  Dim Path1 As String, Path2 As String, Path3 As String
  Dim wNew As String, wOld As String, i As Long
  Dim c1 As String, c2 As String, c3 As String

  
Path1 = "C:\Users\chma\Desktop\F2 - Copy\"                 'origin
  Path2 = "W:\GR-IPP_II\02. Engineering"  'destination

  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Path3 = Replace(Range("G" & i), "PROJECT_483", Path2)
    If Right(Path3, 1) <> "\" Then Path3 = Path3 & "\"
    wOld = Range("A" & i).Value
    c1 = IIf(Range("E" & i).Value <> "", Range("E" & i).Value, " - ")
    c2 = IIf(Range("F" & i).Value <> "", Range("F" & i).Value, " - ")
    c3 = IIf(Range("C" & i).Value <> "", Range("C" & i).Value, " - ")
    wNew = c1 & " (" & c2 & ") " & "Rev" & c3 & ".pdf"
    If Dir(Path1 & wOld) <> "" Then
      If Dir(Path3, vbDirectory) <> "" Then
        Name Path1 & wOld As Path3 & wNew
      End If
    End If
  Next
End Sub
I tried it but it didn't change the file name, nor moved it to the destination/target network folder (which exists already). What i am doing wrong?
 

Attachments

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,791
Office Version
2007
Platform
Windows
I can not see the image is very small.
You must tell me how your files are in the folder, what the source and destination folders are called, all the data.
 

chrysanthi

New Member
Joined
Dec 6, 2019
Messages
12
Office Version
365, 2010
Platform
Windows
Thank you again for your response
I am sorry i didn't explain it as i should. I receive a csv file, looking like this
FILEDOCUMENT IDENTIFICATIONISSUEDATEKKSTITLEPATH
483-21-DT-M-00300-Ed001.pdf483-21-DT-M-00300
1​
05-12-19​
111915-21-YDS-MDD-EA-00300P&ID SYMBOLOGY AND GENERAL NOTESPROJECT_483\200 Mechanical Area\221 Systems\221.01 P&IDs
483-21-FV-D-00001-Ed001.pdf483-21-FV-D-00001
1​
05-12-19​
-Monthly Progress Report (NOVEMBER 2019)PROJECT_483\000 Project Management\001 General\001.03 Progress Reports
483-21-S-M-20250-Ed002.pdf483-21-S-M-20250
2​
04-12-19​
111915-21-PA_-MHP-EA-20250COOLING TOWERS (INDUCED DRAFT) DATA SHEETPROJECT_483\200 Mechanical Area\221 Systems\221.05 Data Sheets



I have attached a jpeg with how the files look when i receive them. That would be the CELL A information. But i need to have them renamed before moved to the correct location.
(i.e. 483-21-DT-M-00300-Ed001.pdf) but i want to rename them as follows:

"Cell E" & "space" & "Rev." & "Cell C" & "space" & "(" & "Cell F" & ")"
which means that they will look like in the jpeg filenames after
somthing like that 111915-21-YDS-MDD-EA-00300 REV.1 (P&ID SYMBOLOGY AND GENERAL NOTES).pdf

By the way i forgot to add the spaces at my previous post.

Then they need to be place in the correct path each of of them as mensioned before
using cell G PROJECT_483\200 Mechanical Area\221 Systems\221.01 P&IDs for the last part, without the PROJECT_483 and instead W:\GR-IPP_II\02. Engineering

W:\GR-IPP_II\02. Engineering\200 Mechanical Area\221 Systems\221.01 P&IDs


If i manage to do that i will create a hyperlink at the next available column, but i haven't used VBA for ages and i am stuck already

folder structurejpg.jpg

folder structurejpg.jpg
filenamesbefore.jpg
filenames after.jpg
folder structurejpg.jpgfilenamesbefore.jpgfilenames after.jpg
 

Forum statistics

Threads
1,081,855
Messages
5,361,716
Members
400,650
Latest member
tcisrly

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top