How to Copy or Move Files from One Folder to Another in Excel using VBA

Last updated: 9th Feb, 2023

Here in this post, I'll show you how to copy or move files from one folder to another using Excel's File System Object methods.

Now, let us assume I have an Excel file with a list of books that I need to deliver to one of my clients. The list is in tabular format (see the below image, 2nd column). I'll have 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.

copy or move files from one folder to another

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

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.

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.

Macro to Copy files from one Folder to another

1) Open an excel file and create a table and add few rows in it.

2) Once the table is created, press Alt+F11 which will open a "module" in the Visual Basic IDE.

3) Copy and paste the below code into the module and save the file. It should be a macro enabled file.

4) 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

Conclusion

These are useful methods and the "File System Object" comes with a package of many useful built-in 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 →