Move Files Using VBA

You can use the ‘FileSystemObject.MoveFile’ method in VBA to move single or multiple files from one folder to another.

In this article, I will show you how to use the FileSystemObject.MoveFile method in VBA, and how to use it to move files between folders.

‘FileSystemObject.MoveFile’ Method

Below is the syntax of the ‘FileSystemObject.MoveFile’ method:

object.MoveFile source, destination 

The ‘object’ element is required and is always the ‘FileSystemObject’ which grants access to the computer’s file system.

Before using the ‘FileSystemObject,’ you must enable the ‘Microsoft Scripting Runtime’ reference as explained in the section ‘Enable Microsoft Scripting Runtime in VB Editor’ below.

The ‘source’ argument is required and is the path to the file or files to be moved. You can use wildcard characters in the last segment of the ‘source’ argument.

The ‘destination’ argument is required and is the path where the file or files are to be moved.

Enabling Microsoft Scripting Runtime in VB Editor

Before you can use the ‘FileSystemObject.MoveFile’ method in VBA to move files, you must enable ‘Microsoft Scripting Runtime’ in VB Editor using the steps below:

1. From the active workbook, press ALT + F11 to open the VB Editor.
2. On the menu bar of the VB Editor, click ‘Tools’ and select ‘References.’

3. On the ‘References – VBAProject’ feature that appears, scroll down the ‘Available References’ list box, check ‘Microsoft Scripting Runtime,’ and click OK.

Microsoft Scripting Runtime’ is a library in VBA that provides access to the Windows ‘FileSystemObject.’ ‘The FileSystemObject’ enables VBA code to read, modify, create, and delete folders and files, and perform other file system operations like renaming, moving, and copying files and folders.

In this tutorial, I will show you seven examples of how to move files using the ‘FileSystemObject.MoveFile’ method in VBA.

Example #1: Move a Single File to an Existing Folder

Suppose you have the ‘Annual Sales.xlsx’ file in the ‘Reports’ folder on drive C and want to move it to an existing ‘Summaries’ folder on drive D.

You can use the code below to move the Excel file:

Sub MoveMyFileExistingFolder()
Dim FSO As Object
Dim sourceFilePath As String
Dim destFolderPath As String
Set FSO = CreateObject("Scripting.FileSystemObject")
sourceFilePath = "C:\Reports\Annual Sales.xlsx"
destFolderPath = "D:\Summaries\"
FSO.MoveFile Source:=sourceFilePath, Destination:=destFolderPath
MsgBox "File moved successfully!", vbInformation
End Sub

The above code moves the source file to the existing destination folder and displays an informational message box indicating the file was moved successfully.

Example #2: Move a Single File to a Folder (Create Folder if Doesn’t Exist)

Sometimes you may want to move a file to a folder but are not sure if the folder exists.

In this case, you need to use code that checks if the folder exists and creates it if it doesn’t.

Suppose you have the ‘Employees.xlsx’ file in the ‘Reports’ folder on drive C and want to move it to the folder ‘Archived Reports’  on the D drive and create it in VBA code if it does not exist.

You can use the below code to do the job:

Sub MoveFileWithFolderCheck()
Dim sourceFilePath As String
Dim destFolderPath As String
Dim destFilePath As String
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
sourceFilePath = "C:\Reports\Employees.xlsx"
destFolderPath = "D:\Archived Reports\"
If Not FSO.FolderExists(destFolderPath) Then
FSO.CreateFolder (destFolderPath)
End If
FSO.MoveFile Source:=sourceFilePath, Destination:=destFolderPath
MsgBox "File moved successfully!", vbInformation
End Sub

The above VBA subroutine moves the source file from the source file path to the destination folder.

Before moving the source file, it checks whether the destination folder exists.

If the folder does not exist, it creates it first, then proceeds to move the file. Once the code moves the file successfully, it displays a message box informing the user.

Also read: VBA to Create Multiple Files in a Folder

Example #3: Move Specific Files to an Existing Folder

Suppose you have four Excel files (Quarter 1.xlsx, Quarter 2.xlsx, Quarter 3.xlsx, and Quarter 4.xlsx) in the ‘Quarterly’ folder on drive C as shown below.

You want to move only ‘Quarter 1.xlsx’ and ‘Quarter 3.xlsx’ to the folder ‘Sample’ on drive D.

You can achieve this by using an array of the files you want to move or a list of source file paths and destination folder paths in a worksheet.

Move Files Using an Array of Files

You can move the target Excel files using the VBA code below with an array of the files to move:

Sub MoveFilesToNewFolder()
Dim FSO As Object
Dim sourceFolderPath As String
Dim destFolderPath As String
Dim i As Long
Dim fileName As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
sourceFolderPath = "C:\Quarterly Sales\"
destFolderPath = "D:\Sample\"
fileName = Array("Quarter 1.xlsx", "Quarter 3.xlsx")
For i = LBound(fileName) To UBound(fileName)
FSO.MoveFile Source:=sourceFolderPath & fileName(i), _
Destination:=destFolderPath
Next i
MsgBox "Files moved successfully!", vbInformation
End Sub

The above subroutine loops through the list of files in the ‘fileName’ array created and populated by the ‘Array’ function, moving each one individually.

After successfully moving all the specified files, it displays a message box to inform the user that the operation was successful.

Move Files Using a List in a Worksheet

You can move the target Excel files using a list in a worksheet using the steps below:

1. Prepare a list of source file paths and destination folder paths in your Excel sheet. In this case, I have listed the source file paths in column A and the destination folder paths in column B, starting from row 2 on Sheet 1 of the active workbook.

2. Copy and paste the below code in a standard VBA module.

Sub MoveFilesBasedOnCellValues()
Dim ws As Worksheet
Dim FSO As Object
Dim sourceFilePath As String
Dim destFolderPath As String
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
Set FSO = CreateObject("Scripting.FileSystemObject")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
sourceFilePath = ws.Cells(i, 1).Value
destFolderPath = ws.Cells(i, 2).Value
If FSO.FileExists(sourceFilePath) Then
If Not FSO.FolderExists(destFolderPath) Then
FSO.CreateFolder destFolderPath
End If
FSO.MoveFile Source:=sourceFilePath, Destination:=destFolderPath
ws.Cells(i, 3).Value = "File moved successfully"
Else
ws.Cells(i, 3).Value = "Source file not found"
End If
Next i
MsgBox "File moving process completed!", vbInformation
End Sub

3. Run the code.

The above subroutine does the following:

• Loops through each row, starting from row 2 to the last row with data in column A.
• For each row, it reads the source file path from column A and the destination folder path from column B.
• It checks if the source file exists. If it does, the subroutine checks if the destination folder exists. If not, it creates the folder.
• It moves the source file to the destination folder.
• It writes a status message in column C, indicating whether the file was moved successfully or if the source file was not found. After processing all the rows, it displays a message box informing the user that the file-moving process is complete, as shown below:

Example #4: Move Files Last Modified Before, After, or In a Particular Year

Suppose you have a folder ‘Reports’ on drive C with files modified on different dates, as shown below:

You want to move all the files modified before 2024 to the ‘Archived Reports’ folder on drive C.

You can use the code below to do it:

Sub MoveFilesModifiedYear()
Dim FSO As Object
Dim sourceFolderPath As String
Dim destFolderPath As String
Dim file As Object
Dim folder As Object
Dim yearThreshold As Integer
Dim lastModified As Date
yearThreshold = 2024
sourceFolderPath = "C:\Reports\"
destFolderPath = "C:\Archived Reports\"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(sourceFolderPath)
For Each file In folder.Files
lastModified = file.DateLastModified
If Year(lastModified) < yearThreshold Then
If Not FSO.FolderExists(destFolderPath) Then
FSO.CreateFolder destFolderPath
End If
FSO.MoveFile Source:=file.Path, Destination:=destFolderPath
End If
Next file
MsgBox "Files moved successfully!", vbInformation
End Sub

The above code checks all files in the “C:\Reports\” directory. If a file was last modified before 2024, it is moved to the “C:\Archived Reports\” folder as shown below:

If the destination folder doesn’t exist, it is created. After moving the files, a message box informs the user that the process is complete.

Note: To move files last modified after or in the threshold year, change the condition in the statement ‘If Year(lastModified) < yearThreshold Then’ accordingly:

• To move files last modified after the threshold year, the statement should read ‘If Year(lastModified) > yearThreshold Then.’
• To move files last modified in the threshold year, the statement should read ‘If Year(lastModified) = yearThreshold Then.’

Also read: Rename Multiple Files Using VBA

Example #5: Move File And OverWrite If It Exists

You can use the code below to move the file ‘Annual Report.xlsx’ in the ‘Reports’ folder on drive C to the ‘Final Reports’ folder on D drive and overwrite it if it already exists:

Sub MoveOverwriteFile()
Dim FSO As Object
Dim sourceFilePath As String
Dim destFilePath As String
Dim overWrite As VbMsgBoxResult
Set FSO = CreateObject("Scripting.FileSystemObject")
sourceFilePath = "C:\Reports\Annual Budget.xlsx"
destFilePath = "D:\Final Reports\Annual Budget.xlsx"
If FSO.FileExists(destFilePath) Then
overWrite = MsgBox("Do you want to overwrite the existing file?", _
vbYesNo + vbQuestion, "File Exists")
If overWrite = vbYes Then
Kill destFilePath
FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
MsgBox "File moved successfully!", vbInformation
Else
Exit Sub
End If
Else
FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
MsgBox "File moved successfully!", vbInformation
End If
End Sub

The above subroutine moves a file from one folder to another, with an option to overwrite the file if it already exists at the destination. 

If a file with the same name exists at the destination, the user is asked via a message box whether they want to overwrite it.

If the user confirms, the file at the destination is deleted, and the source file is moved. If the user declines, the subroutine exits without doing anything. If the file doesn’t exist at the destination, the file is moved without prompting.

Example #6: Move All or Only Files With a Particular Extension

Let’s say you have a folder ‘User Guides’ on drive C containing files with PDF, XLSX, and TXT extensions, as shown below:

You want to move only files with the TXT extension to the folder ‘Text Files’ on drive D.

You can use the code below to move the text files:

Sub MoveFilesParticularExtensions()
Dim FSO As Object
Dim sourceFilePath As String
Dim destFolderpath As String
Set FSO = CreateObject("Scripting.FileSystemObject")
sourceFilePath = "C:\User Guides\*.txt"
destFolderpath = "D:\Text Files\"
FSO.MoveFile Source:=sourceFilePath, Destination:=destFolderpath
MsgBox "Files moved successfully!", vbInformation
End Sub

The above subroutine uses the asterisk (*) wildcard character to match all the text files in the ‘User Guides’ folder on drive C (“C:\User Guides\*.txt”) and moves them to the ‘Text Files’ folder on drive D without having to specify each one individually, as shown below:

Note: To move all the files, irrespective of their extensions, all you need to do is change the sixth statement of the code to sourceFilePath = “C:\User Guides\*” and all the source files will be moved to the destination folder.

Common Errors When Moving Files using VBA

The table below lists some errors you may encounter when moving files using VBA, their causes, and possible solutions.

Error	Cause	Solution
File Not Found	The source file does not exist at the specified path.	Ensure the source file path is correct and that the file exists.
File Already Exists	In some cases, if you do not handle overwriting explicitly.	Use the ‘Kill’ function to delete the existing file before moving the source file.
Permission Denied	The file is open in another program, or you do not have sufficient permissions to access the source file or folder.	Close any programs that might be using the file, or ensure you have the necessary permissions to access the file and folders.
Path/File Access Error	The destination path is invalid, or you do not have sufficient permissions to access the destination folder.	Verify the destination path is correct and accessible, and check your permissions.
Disk Full	The destination drive is full.	Free up space on the destination drive or choose another location with sufficient space.

In this tutorial, I have shown you several examples of how to move files using VBA. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What is VBA in Excel?
• Open Folder and Select File Using VBA