How to move one folder to another location in VBA Excel ?

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-

1. Move “command” in command prompt
2. 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.

1. Check if source and target folders exist
2. Invoke the command prompt, here we are passing multiple commands in single line
3. Use “Move” command to move source folder to target folder
4. Exit “command” is used to exit the command prompt
5. 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

 

2. Using MoveFolder “Method“

In this approach we declare the file system object, and then use the MoveFolder “method”

1. Check if source and target folders exist
2. Use “MoveFolder” method to move source folder to target folder
3. 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

1. We can select the source and target folders using the dialog box as well, which can make this automation more dynamic.
2. 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