Excel VBA Tutorial: Copying and Moving Files Between Folders

Last updated: 9th Feb, 2023

Here in this article, 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 the form of a table. I'll have to search the books in a folder 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 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.

Among its many methods, the File System Object offers two distinct ones: MoveFile and CopyFile that I am using in my example here. Let us understand the methods first.

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 = "D:\booksforclient\"
    
    sFileType = ".jpg"      ' TRY WITH OTHER FILE TYPES LIKE ".pdf".

    Dim objFSO
    Set objFSO = CreateObject("scripting.filesystemobject")
                    
    ' First, check if the "Destination" folder exists.
    If objFSO.FolderExists(sDestinationPath) = False Then
        MsgBox sDestinationPath & " Does Not Exists"
        Exit Sub    ' Folder does not exist. So bail out.
    End If
   
    ' 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
                    '*****
            ' I have two different methods. Both methods produce different result.
            ' The 1st method simply copies the files to the desination folder.
            ' The 2nd method moves the files permanently from source to destination.
                    
                    ' 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 methods are highly useful, and the File System Object offers a variety of built-in functions. However, before using these functions, ensure that the folders have the necessary permissions. Many methods will not work if the folder attributes are set to 'read-only.' Additionally, you might need permissions from the server where you are performing these functions.

← PreviousNext →