|
|
|
|
|
|
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"
newName = "D:\Test"
FileType = "xls"
On Error Resume Next
MkDir newName
myFile = Dir(oldName & "\*." & FileType)
Do Until myFile = ""
Name oldName & "\" & myFile As newName & "\" & myFile
myFile = Dir
Loop
End Sub
|
How to use:
|
- Press Alt+F11 from Excel to open the VB Editor.
- Click on the Project(Workbook) name in the left pane.
- Click on ?Insert? on the Menu Bar
- Select ?Module? from the list. The new Module will open.
- Paste the code into the right pane of the Module.
- Press Alt+F11 to return to Excel
|
Test the code:
|
- Create a folder called 'Test' on one of your hard drives or separate partitions with different drive letters.
- Copy a mixture of files into the folder.
- Decide the required path and a name for the new folder - input the details beside the variable 'newName' - remember the quotation marks.
- Run the code
- 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.
|
|