Rename and move files to a new location

[chrysanthi]

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)

A	B	C	D	E	F	G
FILE	DOCUMENT IDENTIFICATION	ISSUE	DATE	KKS	TITLE	PATH
483-21-DT-M-00300-Ed001.pdf	483-21-DT-M-00300	1​	05-12-19​	111915-21-YDS-MDD-EA-00300	P&ID SYMBOLOGY AND GENERAL NOTES	PROJECT_483\200 Mechanical Area\221 Systems\221.01 P&IDs
483-21-FV-D-00001-Ed001.pdf	483-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.pdf	483-21-S-M-20250	2​	04-12-19​	111915-21-PA_-MHP-EA-20250	COOLING TOWERS (INDUCED DRAFT) DATA SHEET	PROJECT_483\200 Mechanical Area\221 Systems\221.05 Data Sheets

Thank you

 

[DanteAmor]

No... i am sorry i didn't understand the question
The source folder is a temp folder C:\Users\chma\Desktop\EA

The destination is W:\GR-IPP_II\02. Engineering\ + what is shown in the last column without the PROJECT_483

I am trying to understand.

This file
483-21-DT-M-00300-Ed001.pdf
It is in this folder:
C:\Users\chma\Desktop\EA\


Then you want this:
"Cell E" & "space" & "Rev." & "Cell C" & "space" & "(" & "Cell F" & ")"

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

483-21-DT-M-00300

1

05-12-19

111915-21-YDS-MDD-EA-00300

P&ID SYMBOLOGY AND GENERAL NOTES

PROJECT_483\200 Mechanical Area\221 Systems\221.01 P&IDs

Final name:
111915-21-YDS-MDD-EA-00300 Rev.1 (P&ID SYMBOLOGY AND GENERAL NOTES)

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

________________
If not. You could recreate what you need, following my example.

 

[DanteAmor]

If the above is correct, then try the following:

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\EA\"         '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("C" & i).Value <> "", Range("C" & i).Value, " - ")
    c3 = IIf(Range("F" & i).Value <> "", Range("F" & i).Value, " - ")
    wNew = c1 & " " & "Rev" & c2 & " " & "(" & c3 & ")" & ".pdf"
    If Dir(Path1 & wOld) <> "" Then
      If Dir(Path3, vbDirectory) <> "" Then
        Name Path1 & wOld As Path3 & wNew
      Else
        MsgBox "Does not exists folder : " & Path3
      End If
    Else
      MsgBox "Does not existe file : " & Path1 & wOld
    End If
  Next
End Sub

 

[chrysanthi]

It works Perfectly! You are the best!!!Thank you so much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[chrysanthi]

i still havent found how to make this thread- solved

 

[Scott T]

i still havent found how to make this thread- solved

You can not mark it as solved. You can simply post that it is solved.

 

[DanteAmor]

Don't worry, just write that this thread was solved, or simply with the thanks you sent in post #23 is enough!

 

[chrysanthi]

Ok Dante! Thank you once more!

 

[DanteAmor]

Again with pleasure. Have a nice day!