Move Excel Workbooks from one Folder to another Using VBA

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

Note:
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:


Further reading:
Accessing Files with FileSystemObject

One thought on “Move Excel Workbooks from one Folder to another Using VBA

  1. Pingback: Best Training on Advanced Excel and VBA in Bangalore | jaseemblog

Leave a Reply

Your email address will not be published. Required fields are marked *