This tip shows how to use VBA to copy of move files in Windows directories. You can apply this tip to any of the office suite (Word, Excel, Outlook, Access, etc).
In order for this tip to work using the code below, you need to ensure that the references to Windows Script Host Object Model are installed.
COPY FILE
Sub CopyFileToAnotherLocation()
Dim FSObj As FileSystemObject
Dim answer As Integer
Set FSObj = New FileSystemObject
If FSObj.FileExists("C:\New Location\Filename.txt") Then
answer = MsgBox("File already exists in this location. " _
& "Are you sure you want to continue? If you continue " _
& "the file at destination will be deletedo!", _
vbInformation + vbYesNo)
If Not answer = vbYes Then Exit Sub
End If
FSObj.CopyFile "C:\Old Location\Filename.txt", _
"C:\New Location\Filename.txt", True
End Sub
MOVE FILE
Sub MoveFileToAnotherLocation()
Dim FSObj As FileSystemObject
Dim answer As Integer
Set FSObj = New FileSystemObject
If FSObj.FileExists("C:\New Location\Filename.txt") Then
answer = MsgBox("File already exists in this location. " _
& "Are you sure you want to continue? If you continue " _
& "the file at destination will be deletedo!", _
vbInformation + vbYesNo)
If Not answer = vbYes Then Exit Sub
End If
On Error Resume Next
Kill "C:\New Location\Filename.txt"
FSObj.MoveFile "C:\Old Location\Filename.txt", _
"C:\New Location\Filename.txt"
End Sub