How to move files from one folder to another in VBA Excel ?

VBA move file from folder

Objective

To move files from one folder to another using VBA Excel.

Approach

In this VBA Excel automation we are moving the files from source to target folder. This can be done in two ways-

  1. Move “command” in command prompt
  2. Move “Method” from FileSystemObject
  1. Using Move Command

 In this approach we invoke the command prompt shell using VBA.

Steps

  1. Check if source and target folders exist
  2. Invoke the command prompt by VBA code , here we are passing multiple commands in single line
  3. Here we are using “cd” command to change current directory as source folder
  4. Use “Move” command to move specifically .pptx type of files to target folder
  5. Exit command is used to exit the command prompt
  6. Exit the subroutine if source or destination folder does not exist

 

Code 1

Sub MoveAllFiles()
Dim SourceFolder As String
Dim TargetFolder As String

   'Path of the folder where files are locted
    SourceFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Source_Folder\"
    TargetFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Target_Folder\"
     
'Check if the folder already exists
If Dir(SourceFolder, vbDirectory) <> "" And Dir(TargetFolder, vbDirectory) <> "" Then
'We can use *.* or any specific file extension as well to move any type of file
Call Shell("cmd.exe /S /C" & "cd " & SourceFolder & " && " & "move *.pptx " & TargetFolder & "&&" & " Exit")
MsgBox "All files moved to target folder"
Else
 MsgBox "Either source or target folder does not exist"
End If
End Sub

 

We can move any specific (*.pptx or *.xlsx) or all (*.*) type of file using this approach.

 

Using Move “Method”

In this approach we declare the file system object, and then use the Move “Method” (In above code  we had used Move “command” from command prompt) to move the files.Here we are using “Move Method” , not the “Move Command” from command prompt.

Steps

  1. Check if source and target folders exist
  2. Loop through each file in source folder
  3. Check if file is of .pptx type
  4. Use “Move” method to move .pptx type of files only from source folder  to target folder
  5. Exit the subroutine if source or destination folder does not exist

Code 2

Sub MoveAllMyFiles()

    Dim objFileSystem As Object
    Dim SourceFolder As String
    Dim TargetFolder As String
    Dim MyFile As Object

    'Path of the folder where files are locted
    SourceFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Source_Folder\"
    TargetFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Target_Folder\"
          
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    
    'Check if source and target folder exists
    If objFileSystem.FolderExists(SourceFolder) = True And objFileSystem.FolderExists(TargetFolder) = True Then
    'Looping through each .pptx file in the source folder
       For Each MyFile In objFileSystem.getfolder(SourceFolder).Files
        If InStr(MyFile, ".pptx") Then
        MyFile.Move TargetFolder
        End If
       Next MyFile
    Else
        MsgBox "Either source or target folder does not exist"
    End If

     
End Sub

 

Before executing the VBA code, we had 2 .pptx files in souce folder, we can see that in the image below.

 

VBA move file from folder

 

Once the VBA code is executed, both the .pptx files have been moved from source to target folder.Now source folder is blank.

 

VBA move file from folder

 

Note

We can use this VBA move method to create similar Microsoft excel automation tools where we can move any other type (e.g. txt) of file or all the files. 

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/move-method-filesystemobject-object
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object

Post you may like

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