How to copy/move folders and files in VBA

This is another VBA tips I learned with my Excel VBA project. Just wanted to share here for the future reference. Hope you can find it useful as well. The original post was found on tek-tips.com.

The following procedures demonstrate creating, copying, moving and deleting of files and folders using VBA:

Check if a file exists

Sub FileExists()
Dim fso
Dim file As String
file = “C:\Test.xls” ‘ change to match the file w/Path
Set fso = CreateObject(“Scripting.FileSystemObject”)
If Not fso.FileExists(file) Then
MsgBox file & ” was not located.”, vbInformation, “File Not Found”
Else
MsgBox file & ” has been located.”, vbInformation, “File Found”
End If
End Sub

Copy a file if it exists

Sub CopyFile()
Dim fso
Dim file As String, sfol As String, dfol As String
file = “test.xls” ‘ change to match the file name
sfol = “C:\” ‘ change to match the source folder path
dfol = “E:\” ‘ change to match the destination folder path
Set fso = CreateObject(“Scripting.FileSystemObject”)
If Not fso.FileExists(sfol & file) Then
MsgBox sfol & file & ” does not exist!”, vbExclamation, “Source File Missing”
ElseIf Not fso.FileExists(dfol & file) Then
fso.CopyFile (sfol & file), dfol, True
Else
MsgBox dfol & file & ” already exists!”, vbExclamation, “Destination File Exists”
End If
End Sub

Move a file if it exists

Sub MoveFile()
Dim fso
Dim file As String, sfol As String, dfol As String
file = “test.xls” ‘ change to match the file name
sfol = “C:\” ‘ change to match the source folder path
dfol = “E:\” ‘ change to match the destination folder path
Set fso = CreateObject(“Scripting.FileSystemObject”)
If Not fso.FileExists(sfol & file) Then
MsgBox sfol & file & ” does not exist!”, vbExclamation, “Source File Missing”
ElseIf Not fso.FileExists(dfol & file) Then
fso.MoveFile (sfol & file), dfol
Else
MsgBox dfol & file & ” already exists!”, vbExclamation, “Destination File Exists”
End If
End Sub

Delete a file if it exists

Sub DeleteFile()
Dim fso
Dim file As String
file = “C:\test.xls” ‘ change to match the file w/Path
Set fso = CreateObject(“Scripting.FileSystemObject”)
If fso.FileExists(file) Then
fso.DeleteFile file, True
Else
MsgBox file & ” does not exist or has already been deleted!” _
, vbExclamation, “File not Found”
End If
End Sub

Here are the Folder Procedures

Check if a folder exists

Sub FolderExists()
Dim fso
Dim folder As String
folder = “C:\My Documents” ‘ change to match the folder path
Set fso = CreateObject(“Scripting.FileSystemObject”)
If fso.FolderExists(folder) Then
MsgBox folder & ” is a valid folder/path.”, vbInformation, “Path Exists”
Else
MsgBox folder & ” is not a valid folder/path.”, vbInformation, “Invalid Path”
End If
End Sub

Create a folder if it doesn’t exist

Sub CreateFolder()
Dim fso
Dim fol As String
fol = “c:\MyFolder” ‘ change to match the folder path
Set fso = CreateObject(“Scripting.FileSystemObject”)
If Not fso.FolderExists(fol) Then
fso.CreateFolder (fol)
Else
MsgBox fol & ” already exists!”, vbExclamation, “Folder Exists”
End If
End Sub

Copy a folder if it exists

Sub CopyFolder()
Dim fso
Dim sfol As String, dfol As String
sfol = “c:\MyFolder” ‘ change to match the source folder path
dfol = “e:\MyFolder” ‘ change to match the destination folder path
Set fso = CreateObject(“Scripting.FileSystemObject”)
If Not fso.FolderExists(dfol) Then
fso.CopyFolder sfol, dfol
Else
MsgBox dfol & ” already exists!”, vbExclamation, “Folder Exists”
End If
End Sub

Move a folder if it exists

Sub MoveFolder()
‘ ***********************************************************
‘ *** This will only work if your operating system ***
‘ *** allows it otherwise an error occurs ***
‘ ***********************************************************
Dim fso
Dim fol As String, dest As String
sfol = “c:\MyFolder” ‘ change to match the source folder path
dfol = “e:\MyFolder” ‘ change to match the destination folder path
Set fso = CreateObject(“Scripting.FileSystemObject”)
If Not fso.FolderExists(dfol) Then
fso.MoveFolder sfol, dfol
Else
MsgBox dfol & ” already exists!”, vbExclamation, “Folder Exists”
End If
End Sub

Delete a folder if it exists

Sub DeleteFolder()
‘ ***********************************************************
‘ *** This will delete a folder even if it contains files ***
‘ *** Use With Caution ***
‘ ***********************************************************
Dim fso
Dim fol As String
fol = “c:\MyFolder” ‘ change to match the folder path
Set fso = CreateObject(“Scripting.FileSystemObject”)
If fso.FolderExists(fol) Then
fso.DeleteFolder fol
Else
MsgBox fol & ” does not exist or has already been deleted!” _
, vbExclamation, “Folder not Found”
End If
End Sub

A couple more procedures that might come in handy!

Move ALL files (or of a specific file type) from one folder into another folder

Sub MoveFilesFolder2Folder()
Dim fso
Dim sfol As String, dfol As String
sfol = “c:\MyFolder” ‘ change to match the source folder path
dfol = “e:\MyFolder” ‘ change to match the destination folder path
Set fso = CreateObject(“Scripting.FileSystemObject”)
On Error Resume Next
If Not fso.FolderExists(sfol) Then
MsgBox sfol & ” is not a valid folder/path.”, vbInformation, “Invalid Source”
ElseIf Not fso.FolderExists(dfol) Then
MsgBox dfol & ” is not a valid folder/path.”, vbInformation, “Invalid Destination”
Else
fso.MoveFile (sfol & “\*.*”), dfol ‘ Change “\*.*” to “\*.xls” to move Excel Files only
End If
If Err.Number = 53 Then MsgBox “File not found”
End Sub

Copy ALL files (or of a specific file type) in one folder into another folder

Sub CopyFilesFolder2Folder()
Dim fso
Dim sfol As String, dfol As String
sfol = “c:\MyFolder” ‘ change to match the source folder path
dfol = “e:\MyFolder” ‘ change to match the destination folder path
Set fso = CreateObject(“Scripting.FileSystemObject”)
On Error Resume Next
If Not fso.FolderExists(sfol) Then
MsgBox sfol & ” is not a valid folder/path.”, vbInformation, “Invalid Source”
ElseIf Not fso.FolderExists(dfol) Then
MsgBox dfol & ” is not a valid folder/path.”, vbInformation, “Invalid Destination”
Else
fso.CopyFile (sfol & “\*.*”), dfol ‘ Change “\*.*” to “\*.xls” to move Excel Files only
End If
If Err.Number = 53 Then MsgBox “File not found”
End Sub

One thought on “How to copy/move folders and files in VBA

  1. devendrad

    Hi Experts,

    I have a code which was running properly previously, but now its giving me error- Runtime Error- 70, Permission Denied.
    So I want to modify the code, but not able to do so, So please assist.
    Condition- Suppose I have 2000 images in a folder & I want to make batches of 200 images, currently am selecting the files & moving them in a new folder. below macro was running previously but now it’s giving runtime error.
    Error in the line – — fso.movefile FolderName & FileNm.Name, NewFolder

    Sub Divide_folder()
    Dim fso, fldr, FileNm, FileColl
    Dim FolderName, NewFolder, FolderIndex As Integer
    Dim fCnt, cnt

    FolderName = ThisWorkbook.Path & “\” ‘(be sure folder name ends in “\”)

    Set fso = CreateObject(“Scripting.FileSystemObject”)
    fCnt = 0
    FolderIndex = 1
    NewFolder = FolderName & “Folder_” & Format(FolderIndex, “000”) & “\”
    If (Not fso.folderexists(NewFolder)) Then
    MkDir NewFolder
    End If
    Set fldr = fso.GetFolder(FolderName)
    ‘ Debug.Assert False
    For Each FileNm In fldr.Files
    Debug.Assert True
    fCnt = fCnt + 1
    If (fCnt > 200) Then
    FolderIndex = FolderIndex + 1
    NewFolder = FolderName & “Folder_” & Format(FolderIndex, “000”) & “\”
    If (Not fso.folderexists(NewFolder)) Then
    MkDir NewFolder
    End If
    fCnt = 1
    End If
    fso.movefile FolderName & FileNm.Name, NewFolder

    Next FileNm
    MsgBox “Finished”
    End Sub

    [Reply]

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Please Answer: * Time limit is exhausted. Please reload the CAPTCHA.