Excel

Moving files between hard drive folders

Ease of Use

Easy

Version tested with

97, 2002 

Submitted by:

Glaswegian

Description:

You can move selected files between folders on the same drive or between folders on different drives using VBA rather than Windows Explorer. 

Discussion:

You may need to move files, perhaps in a long code routine, between folders or drives. Or perhaps you just want to avoid using Explorer! This simple code allows you to do that, using Name Statement. There is no need to create a copy of a file then delete the original. Note that the Name Statement cannot create a new directory or folder, therefore you use the MkDir Satement to create the new folder. Note that if a file of the same name already exists then then the original file will not be moved. 

Code:

instructions for use

			

Option Explicit Option Compare Text Sub MoveFiles() Dim myFile As String Dim oldName As String Dim newName As String Dim FileType As String oldName = "C:\Test" 'This is the original folder newName = "D:\Test" 'This is the new folder - will be created with the MkDir Statement FileType = "xls" 'Enter File Type to be moved 'e.g. xls, xl*, doc, do*, or * for all files On Error Resume Next MkDir newName 'create a new folder based on the path for the variable 'newName' myFile = Dir(oldName & "\*." & FileType) 'not restricted to xls files, could also be Word documents etc Do Until myFile = "" 'until there are no files left Name oldName & "\" & myFile As newName & "\" & myFile myFile = Dir 'calls Dir Function again without changing the Path Loop End Sub

How to use:

  1. Press Alt+F11 from Excel to open the VB Editor.
  2. Click on the Project(Workbook) name in the left pane.
  3. Click on ?Insert? on the Menu Bar
  4. Select ?Module? from the list. The new Module will open.
  5. Paste the code into the right pane of the Module.
  6. Press Alt+F11 to return to Excel
 

Test the code:

  1. Create a folder called 'Test' on one of your hard drives or separate partitions with different drive letters.
  2. Copy a mixture of files into the folder.
  3. Decide the required path and a name for the new folder - input the details beside the variable 'newName' - remember the quotation marks.
  4. Run the code
  5. Excel files in the original folder will be moved to the newly created folder.
 

Sample File:

No Attachment 

Approved by mdmackillop


This entry has been viewed 216 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express