Objective
To move one folder to another folder in VBA Excel.
Approach
In this excel automation ,we are moving a folder to another folder. This can be done in two ways-
- Move “command” in command prompt
- MoveFolder “Method” from FileSystemObject
Using Move “Command”
In this VBA Excel automation, we invoke the command prompt shell using VBA. Here, we have defined the path of source and target folders in two separate variables.
- Check if source and target folders exist
- Invoke the command prompt, here we are passing multiple commands in single line
- Use “Move” command to move source folder to target folder
- Exit “command” is used to exit the command prompt
- Exit the subroutine if source or destination folder does not exist
Code
Sub MoveFolders() Dim SourceFolder As String Dim TargetFolder As String 'Path of the folder where files are locted SourceFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Folder_Y" TargetFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Folder_X" 'Checking if the source and target folder exist If Dir(SourceFolder, vbDirectory) <> "" And Dir(TargetFolder, vbDirectory) <> "" Then 'If folder has files , then files will also move with folder Call Shell("cmd.exe /S /C" & "move " & SourceFolder & " " & TargetFolder & "&&" & " Exit") MsgBox "Source folder has moved to target folder" Else MsgBox "Either source or destination folder does not exist" End If End Sub
- Using MoveFolder “Method“
In this approach we declare the file system object, and then use the MoveFolder “method”
- Check if source and target folders exist
- Use “MoveFolder” method to move source folder to target folder
- Exit the subroutine if source or destination folder does not exist
Code
Sub MoveAllMyFolders() Dim objFileSystem As Object Dim SourceFolder As String Dim TargetFolder As String 'Path of the folder where files are locted SourceFolder = "C:\Users\myusername\Desktop\macro\Final\New\Folder_X" TargetFolder = "C:\Users\myusername\Desktop\macro\Final\New\Folder_Y\" Set objFileSystem = CreateObject("Scripting.FileSystemObject") 'Check if source and target folder exists If objFileSystem.FolderExists(SourceFolder) = True And objFileSystem.FolderExists(TargetFolder) = True Then objFileSystem.MoveFolder Source:=SourceFolder, Destination:=TargetFolder MsgBox "Source folder has moved to target folder" Else MsgBox "Either source or target folder does not exist" End If End Sub
Here we have assigned this VBA code to command button on a xlsm file, as we can see in the image below.
Once we click on command button, a message box will appear.
Before execution of the code, both the folders exist at the same path.
In the below image we can see that “after executing the VBA code” source folder (Folder_X), now has been moved to Folder_y. We can see the path of the folder_X in image below.
Notes
- We can select the source and target folders using the dialog box as well, which can make this automation more dynamic.
- Both the approaches to move the folders have similar functionality.
Reference
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/movefolder-method
Post you may like
Split excel workbook ( file ) in multiple workbooks ( files ) in VBA