Excel VBA Copy and Rename Files

I received a question on post Excel VBA Rename Files and thought a separate post was warranted to cover the answer.  In the following example, I will setup an Excel file to do the following:

  • Allow user to pick a folder and list out the file content (file path and name)
  • Allow user to pick a destination folder for location to move the files
  • Allow user to rename each file prior to moving to destination folder

Setup:  Excel VBA Copy and Rename Files

Example file containing content at the bottom of this post

In a new Excel file, add the following:

  • Cell A3 Text:  Current File Path      Column Width:  45
  • Cell B3 Text:  Current File Name    Column Width:  25
  • Cell C3 Text:  New File Path          Column Width:  45
  • Cell D3 Text:  New File Name       Column Width:  25

Next, add three Command Buttons (Active X controls):

Excel VBA Copy and Rename Files

Position the buttons as follows:

Excel VBA Copy and Rename Files

In the “Properties” box for each button, set the following:

  • (Name):  cmdPickFolder             Caption:  Pick folder to list files you want to copy
  • (Name):  cmdPickDestination     Caption:  Pick folder you want to copy files to
  • (Name):  cmdCopy                     Caption:  Copy Files!

Position the buttons according to picture above.

Next, Insert a new module and add the following code:

Next, add the following code to each button control:

And that should do it!  This workbook allows you to pick a folder and list file content, pick destination folder to move the files, then allows you to rename each file prior to the move.

 

Example File:

Excel VBA Copy and Rename Files

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

Leave a Comment