Rename and move files to a new location

chrysanthi

New Member
Joined
Dec 6, 2019
Messages
12
Office Version
  1. 365
  2. 2010
Platform
  1. 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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
The Name statement can be used instead of FileCopy / Kill. Name can move a file across drives.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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

  • Untitled.jpg
    Untitled.jpg
    37.5 KB · Views: 9
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top