Excel VBA – Copy or Move files from one folder to another

The Excel FSO also known as the File System Object has methods to move, create, alter and delete files and folders in a machine. Using couple of its features in this article, we will see how to copy or move files from one folder to another folder.

copy or move files from one folder to another

Now, let us assume we have an Excel file with a list of books that we need to deliver to one of our clients. The list is in tabular format (see the above image, 2nd column). We need to search these books in a folder, which already has a collection of many books in it, and check if it is available or not.

Related: Create Folder and Sub Folders in another Folder and Copy Source Files to the Sub Folders

It is a very practical scenario and I have come across many such requirements in the past. If the books are in our inventory (in the folder) then the File Status column will show the status “On Hand” and the files will be copied or moved from the source folder to the destination folder.

Coping or moving files across different folders or directories, play a slightly different role. With many other methods, the File System Object has provided us with 2 distinct methods such as MoveFile and CopyFile.

Method MoveFile

Syntax: object.MoveFilesource, destination

Set objFSO = CreateObject("scripting.filesystemobject")
objFSO.MoveFile

The objFSO.MoveFile will move the files permanently from the source folder to the destination folder. Its function is similar to cutting and pasting an object.

Related: Find and Highlight Duplicate Values in Excel using VBA (macro)

Method CopyFile

Syntax: object.CopyFilesource, destination, overwrite

Set objFSO = CreateObject("scripting.filesystemobject")
objFSO.CopyFile

The CopyFile method will copy the files from the source folder to the destination folder. The Original copy of the file remains in the source folder. Other than source and destination, the 3rd parameter is a Boolean value overwrite. If set as True the files in the destination folder will be overwritten and if set false, the file in the destination folder will remain unchanged.

You can copy or move any type of file in a folder. In the demo example here, I’ll search for .jpg files only, ignoring other types of files.

Also Read: Get Data from a Closed Excel File or Workbook without Opening it.

The VBA Code

Open an excel file and create a table and add few rows in it. Once the table is created, press Alt+F11 which will open a module in the visual basic IDE. Copy and paste the code below into the module and save the file as macro enabled file. Run the macro by pressing F5 and see the result both in the excel file itself and destination folder.

Option Explicit

Sub CopyFiles()
    Dim iRow As Integer         ' ROW COUNTER.
    Dim sSourcePath As String
    Dim sDestinationPath As String
    Dim sFileType As String
    
    Dim bContinue As Boolean
    
    bContinue = True
    iRow = 2
    
    ' THE SOURCE AND DESTINATION FOLDER WITH PATH.
    sSourcePath = "C:\books\"
    sDestinationPath = "C:\booksforclient\"
    
    sFileType = ".jpg"      ' TRY WITH OTHER FILE TYPES LIKE ".pdf".
   
    ' LOOP THROUGH COLUMN "B" TO PICK THE FILES.
    While bContinue
    
        If Len(Range("B" & CStr(iRow)).Value) = 0 Then    ' DO NOTHING IF THE COLUMN IS BLANK.
            MsgBox "Process executed" ' DONE.
            bContinue = False
        Else
            ' CHECK IF FILES EXISTS.
            
            If Len(Dir(sSourcePath & Range("B" & CStr(iRow)).Value & sFileType)) = 0 Then
                Range("C" & CStr(iRow)).Value = "Does Not Exists"
                Range("C" & CStr(iRow)).Font.Bold = True
            Else
                Range("C" & CStr(iRow)).Value = "On Hand"
                Range("C" & CStr(iRow)).Font.Bold = False
            
                If Trim(sDestinationPath) <> "" Then
                    Dim objFSO
                    Set objFSO = CreateObject("scripting.filesystemobject")
                    
                    ' CHECK IF DESTINATION FOLDER EXISTS.
                    If objFSO.FolderExists(sDestinationPath) = False Then
                        MsgBox sDestinationPath & " Does Not Exists"
                        Exit Sub
                    End If
                    
                    '*****
                    ' HERE I HAVE INCLUDED TWO DIFFERENT METHODS.
                    ' I HAVE COMMENTED THE SECOND METHOD. TO THE SEE THE RESULT OF THE 
                    ' SECOND METHOD, UNCOMMENT IT AND COMMENT THE FIRST METHOD.
                    
                    ' METHOD 1) - USING "CopyFile" METHOD TO COPY THE FILES.
                    objFSO.CopyFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _
                        sFileType, Destination:=sDestinationPath
                    
                    ' METHOD 2) - USING "MoveFile" METHOD TO PERMANENTLY MOVE THE FILES.
                    'objFSO.MoveFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _
                        sFileType, Destination:=sDestinationPath
                    '*****
                End If
            End If
        End If
       
       iRow = iRow + 1      ' INCREMENT ROW COUNTER.
    Wend
End Sub


Also Read: Extract or Get FileName from FilePath in Excel using VBA Macro

Conclusion

These are useful methods and the File System Object comes with a package of many useful methods. However, before using these functions, you must make sure that the folders have necessary permissions. Since many of these methods will have no result if the folder attributes are set as read-only. You might also need permissions from the server on which you are performing these functions.

← PreviousNext →



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner

Related Posts: