Yes, you can move a file to another folder in Excel VBA using the FileSystemObject.MoveFile method or the simpler Name ... As ... statement. The quickest one-line approach is Name "C:\OldFolder\file.xlsx" As "C:\NewFolder\file.xlsx", but for more control (like handling errors or moving multiple files), the FileSystemObject is recommended. Below is a full guide with code examples, common pitfalls, and answers to frequent questions.
Background: Why Move Files with VBA?
Excel VBA is often used to automate repetitive file management tasks. Instead of manually dragging files or using Windows Explorer, a macro can move files based on conditions, such as after exporting a report, archiving old data, or organizing downloads. The two main methods are:
- Name statement – a built-in VBA command that works like renaming, but can also move a file to a different folder.
- FileSystemObject (FSO) – a more flexible object from the Microsoft Scripting Runtime library, which offers methods like
MoveFileandCopyFile.
Both methods are reliable, but FSO gives you better error handling and the ability to check if a file exists before moving.
How to Move a File Using the Name Statement
The Name statement is the simplest way to move a file. The syntax is:
Name <oldPath> As <newPath>
For example, to move C:\Data\Report.xlsx to D:\Archive\Report.xlsx:
Sub MoveWithName()
Name "C:\Data\Report.xlsx" As "D:\Archive\Report.xlsx"
End Sub
Important: The destination folder must already exist. If it doesn’t, you’ll get an error. Also, the Name statement cannot move a file that is currently open in Excel or another program.
How to Move a File Using FileSystemObject
To use FSO, you need to add a reference to “Microsoft Scripting Runtime” (Tools > References in the VBA editor) or use late binding with CreateObject. Late binding avoids the reference setup and is more portable. Here’s a complete example:
Sub MoveWithFSO()
Dim fso As Object
Dim sourceFile As String
Dim destFolder As String
' Set paths
sourceFile = "C:\Data\Report.xlsx"
destFolder = "D:\Archive\"
' Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
' Check if source exists
If fso.FileExists(sourceFile) Then
' Check if destination folder exists, if not create it
If Not fso.FolderExists(destFolder) Then
fso.CreateFolder destFolder
End If
' Move the file
fso.MoveFile sourceFile, destFolder & "Report.xlsx"
MsgBox "File moved successfully!"
Else
MsgBox "Source file not found."
End If
Set fso = Nothing
End Sub
This code checks for the source file, creates the destination folder if needed, and then moves the file. It’s more robust than the Name statement because it handles missing folders.
How to Choose Between Name and FileSystemObject
Both methods work, but your choice depends on the scenario:
- Use
Nameif you have a simple, one-time move and the destination folder always exists. It’s shorter and doesn’t require any references. - Use FSO if you need to check for file existence, create folders on the fly, move multiple files with patterns (e.g., *.xlsx), or handle errors gracefully. FSO also lets you move files across drives without issues.
For most automation tasks, FSO is the safer bet because it gives you more control.
Common Problems and Solutions
Here are typical issues you might encounter when moving files with VBA:
- File in use – If the file is open in Excel or another program, the move fails. Use
On Error Resume Nextor check withfso.FileExistsand try to close the file first. - Destination folder doesn’t exist – The
Namestatement will error. Use FSO and create the folder withCreateFolder. - Path with spaces – Always enclose paths in double quotes. In VBA strings, use double quotes as shown.
- Permission denied – Ensure you have write access to both source and destination folders. Running Excel as administrator can sometimes help, but it’s better to check folder permissions.
- Moving multiple files – Use a loop with
Diror FSO’sGetFolderto iterate through files. Example:For Each file In fso.GetFolder("C:\Data").Files.
Moving Files with a File Dialog (User Selects Folder)
Often you want the user to pick the destination folder. Use Application.FileDialog to let them choose:
Sub MoveWithDialog()
Dim fso As Object
Dim fd As FileDialog
Dim sourceFile As String
Dim destFolder As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
sourceFile = "C:\Data\Report.xlsx"
If fd.Show = -1 Then
destFolder = fd.SelectedItems(1) & "\"
If fso.FileExists(sourceFile) Then
fso.MoveFile sourceFile, destFolder & "Report.xlsx"
MsgBox "Moved to " & destFolder
Else
MsgBox "Source file not found."
End If
End If
Set fso = Nothing
End Sub
This makes the macro interactive and user-friendly.
Alternatives to Moving: Copy, Rename, or Delete
Sometimes you don’t want to move a file, but copy it instead. Use fso.CopyFile or the FileCopy statement. To rename a file without moving it, use Name oldName As newName (same folder). To delete, use Kill or fso.DeleteFile. Here’s a quick comparison:
- Move – removes from source, places in destination.
- Copy – leaves source intact, creates a duplicate.
- Rename – changes name in same folder.
- Delete – permanently removes (use with caution).
For copying, the FSO method is: fso.CopyFile source, dest. For deleting: fso.DeleteFile source.
Safety and Best Practices
Moving files is irreversible if you don’t have a backup. Always consider these precautions:
- Save a copy of your workbook before running any macro that moves files, because macros cannot be undone with Ctrl+Z.
- Test with a dummy file first to ensure paths and permissions are correct.
- Add error handling using
On Error GoToto catch unexpected issues and display a message. - Use absolute paths or build them from
ThisWorkbook.Pathto avoid hardcoding. - Consider using a confirmation dialog before moving, especially if the macro deletes or overwrites files.
Example of error handling:
Sub SafeMove()
On Error GoTo ErrorHandler
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile "C:\Data\file.txt", "D:\Archive\file.txt"
MsgBox "Done"
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description
End Sub
Frequently Asked Questions
Can I move a file to a folder on a different drive?
Yes, both Name and FSO can move files across drives. For example, from C: to D:. Just ensure the destination folder exists or create it with FSO.
How do I move multiple files at once?
Use a loop with Dir to find files matching a pattern, or use FSO to iterate through a folder. For example, move all .xlsx files from one folder to another:
Dim fso As Object, folder As Object, file As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("C:\Data")
For Each file In folder.Files
If LCase(fso.GetExtensionName(file.Name)) = "xlsx" Then
fso.MoveFile file.Path, "D:\Archive\" & file.Name
End If
Next
What if the destination file already exists?
By default, both methods will overwrite the existing file without warning. To avoid overwriting, check with fso.FileExists and decide to rename or skip.
Why do I get a “Permission Denied” error?
This usually means the file is open in another program or you lack write permissions to the destination folder. Close any programs using the file and check folder security settings.
Does the Name statement work on network paths?
Yes, but you may need to map the network drive or use the UNC path (e.g., \\server\share\folder\file.xlsx). Ensure you have appropriate network permissions.
Related
- Employee Schedule Spreadsheet: How to Build One
- Excel Loan Amortization Template: How to Build One
- Excel Spreadsheet for Debt Payoff? Yes, Here’s How
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Result: Return Values and Button ChoicesIn VBA, MsgBox returns a VbMsgBoxResult value that identifies which response button the user selected. Store that return…
- Excel VBA String Comparison: Rules, Methods, and ExamplesIn Excel VBA, compare strings with StrComp when you want to choose case sensitivity explicitly: use StrComp(firstText, secondText,…
- Excel VBA Filter Function: Syntax, Examples, and LimitsIn Excel VBA, the Filter function searches a one-dimensional array of strings and returns the elements that contain…
- VBA Sort Order in Excel: Rules and ExamplesIn Excel VBA, sort order is set with xlAscending or xlDescending; the sort key determines which column or…
- Excel Not Responding After a Macro? What to DoIf Excel says “Not Responding” after you run a macro, first give it a moment and check whether…
- Excel VBA Data Validation Drop-Down Lists: How to Build ThemTo create an Excel data-validation drop-down list with VBA, apply the xlValidateList validation type to the target cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.