Move Excel Workbooks from one Folder to another Using VBA

Home More Excel (VBA) Training Videos

Last time we learnt how to copy the data from different suppliers workbooks to a master workbook. Now since the work of transferring data is completed we don't want the suppliers files to be in the suppliers master folder. How can we move these Excel files or workbooks to another folder, for example, into a folder called suppliers-done? We use Excel VBA. The code below shows how to move the files from one folder into another folder quickly and easily. We also learn about a new Excel object called FSO or FileSystemObject:

Sub Move_Files_From_One_Folder_To_Another_Folder()
' We define our variables
    Dim FSO As Object
    Dim FromDir As String
    Dim ToDir As String
    Dim FExtension  As String
    Dim FNames As String
' We initialize our source and destination directories
    FromDir = "C:\suppliers-master\supplier-a.xlsx"
    ToDir = "C:\suppliers-done\supplier-a.xlsx"
'We define here the types of files we wish to move. If you had .txt or .docx or .doc files you can use *.* as the FExtension
    FExtension = "*.xlsx"  

'Now assign each file name with extension
    FNames = Dir(FromDir & FExtension)
'Check whether there are any files in the folder so that you can exit if there are no files
    If Len(FNames) = 0 Then
        MsgBox "No files in " & FromDir
        Exit Sub

    End If
'Most methods return values but FSO returns an object. You cannot simply assign an object to a variable using an equals sign. In Excel VBA you need to use the Set statement
    Set FSO = CreateObject("Scripting.FileSystemObject")
'Now we move the file from the source directory to the destination directory

    FSO.MoveFile Source:=FromDir & FExtension, Destination:=ToDir

End Sub

To programme in the FSO model you need to
  • Dim a variabe as a FileSystemObject
  • Create a refernce to the Scripting type library located in the file Scrrun.dll
  • You can create a FilesystemObject using the following code - DimFSO As New FileSystemObject 
  • Or, FSO = CreateObject("Scripting.FileSystemObject") - here Scripting is the reference to the type library and FileSystemObject is the name of the object - we wish to create an instance of this object
  • Now you can use methods like FSO.MoveFile or FSO.CopyFile
To move a single file from one folder to another folder you can use the following code:

Sub Move_Single_File()

    Name "C:\suppliers-master\supplier-a.xlsx" As "C:\suppliers-done\supplier-a.xlsx" ' Here you can also assign a different name to the file while moving

End Sub

To copy a single file from one folder to another folder you can use the following code:

Sub Copy_Single_File()
    FileCopy "C:\suppliers-master\supplier-a.xlsx", "C:\suppliers-done\supplier-a.xlsx"
End Sub
Watch the Excel training video to see how the complete process of moving multiple files from one folder to another is implemented:

Watch this video on YouTube
Home More Excel (VBA) Training Videos