Table of Contents
Files can be moved using VBA (Visual Basic for Applications) by utilizing the “FilesystemObject” library. This library allows for the creation, manipulation, and management of files and folders. To move a file using VBA, the “Move” method can be used, which takes in the source file path and the destination file path as parameters. An example of moving a file using VBA would be as follows:
Sub MoveFile()
Dim fso As Object
Set fso = CreateObject(“Scripting.FileSystemObject”)
‘create file paths for source and destination
Dim sourcePath As String
Dim destPath As String
sourcePath = “C:Documentsfile1.xlsx”
destPath = “C:DocumentsNewFolderfile1.xlsx”
‘move file from source to destination
fso.MoveFile sourcePath, destPath
End Sub
This code snippet would move the file “file1.xlsx” from the “Documents” folder to the “NewFolder” folder within the “Documents” folder.
Move Files Using VBA (With Example)
You can use the MoveFile method in VBA to move a file from one folder to another.
Here is one common way to use this method in practice:
Sub MoveMyFile()
Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")'specify source file and destination file
SourceFile = "C:UsersbobDesktopSome_Data_1soccer_data.txt"
DestFile = "C:UsersbobDesktopSome_Data_2soccer_data.txt"
'move file
FSO.MoveFile Source:=SourceFile, Destination:=DestFile
End Sub
This particular macro moves the file called soccer_data.txt from a folder called Some_Data_1 to a folder called Some_Data_2.
The following example shows how to use this syntax in practice.
Example: How to Move Files Using VBA
Suppose we have a text file called soccer_data.txt located in a folder called Some_Data_1 on our Desktop:
Now suppose we would like to use VBA to move this text file to another folder called Some_Data_2 on our Desktop, which currently contains two text files:
Before using VBA to move this file, we need to first enable Microsoft Scripting Runtime within the VB Editor.
To do so, open the VB Editor, then click Tools, then click References:
In the new window that appears, scroll down until you see Microsoft Scripting Runtime and check the box next to it. Then click OK.
Next, we can create the following macro to move the file:
Sub MoveMyFile()
Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
'specify source file and destination fileSourceFile = "C:UsersbobDesktopSome_Data_1soccer_data.txt"
DestFile = "C:UsersbobDesktopSome_Data_2soccer_data.txt"
'move file
FSO.MoveFile Source:=SourceFile, Destination:=DestFile
End Sub
Note that if you’d like to move all files from one folder to another, you can use the following syntax:
Sub MoveMyFile()
Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
'specify source and destination foldersSourceFile = "C:UsersbobDesktopSome_Data_1*"
DestFile = "C:UsersbobDesktopSome_Data_2"'move all files from source folder to destination folder
FSO.MoveFile Source:=SourceFile, Destination:=DestFile
End Sub
This particular macro will move all files from the Some_Data_1 folder to the Some_Data_2 folder.
Note: You can find the complete documentation for the MoveFile method .
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: