web 2.0


Copying and Moving files using VBA

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

Tags: , , , , , ,

Microsoft Office | Microsoft Office - VBA

Comments are closed