VBA code to copy, move, delete and manage files

Automating processes with VBA can involve copying, moving, deleting and managing a lot of files.  Thankfully, VBA has a number of built-in functions to undertake these tasks.  These files do not necessarily need to be Excel workbooks, they can be any file type.

Check if a file exists

If any actions are performed on a file which does not exist, the VBA code will error.  As a result, checking for file existence is often the most common action.

The code below will print True or False to the immediate window to indicate if the file exists.

'Check if a file exists
Debug.Print Dir("C:\Users\marks\Documents\Folder\Text.xlsx") <> ""

The code below uses an If statement to carry out different actions depending on if the file does or does not exist.

'Check if a file exists
Dim filePath As String
filePath = "C:\Users\marks\Documents\Folder\FileName.xlsx"

If Dir(filePath) <> "" Then

    'Insert action for if the file exists
    'This example prints to the immediate window
    Debug.Print filePath & " exists."

Else

    'Insert actions if the file does not exist
    'This example prints to the immediate window
    Debug.Print filePath & " does not exist."

End If

When regularly checking for the existence of files, it can be easier to have a reusable function within a Module to be called upon when required.

'Reusable function to check if a file exists
Function doesFileExist(filePath) As Boolean

doesFileExist = Dir(filePath) <> ""

End Function

The code below shows how to call the reusable function as part of an If statement

'Call the reusable function to check for file existence
If doesFileExist("C:\Users\marks\Documents\Folder\FileName.xlsx") = True Then

    'Insert action for if the file exists
    'This example prints to the immediate window 
    Debug.Print "The file exists."

End If

Rename a file

'Rename a file
Name "C:\Users\marks\Documents\Folder\CurrentFileName.xlsx" As "C:\Users\marks\Documents\Folder\NewFileName.xlsx"

Moving a file

The code to move a file is the same syntax as the code to rename a file.

'Move a file
Name "C:\Users\marks\Documents\FileName.xlsx" As "C:\Users\marks\Documents\New Folder\FileName.xlsx"

Copying a file

'Copy a file
FileCopy "C:\Users\marks\Documents\Folder\Original File.xlsx", "C:\Users\marks\Documents\New Folder\Copied File.xlsx"

Delete files

'Delete a specific file
Kill "C:\Users\marks\Documents\Folder\DeleteMe.xlsx"

'Delete all .xlsx files using the * wildcard character
 Kill "C:\Users\marks\Documents\Folder\*.xlsx"

'Delete all files from a folder using two * wildcard characters
 Kill "C:\Users\marks\Documents\Folder\*.*"

Common wildcard characters are:

Character	Description
* (asterisk)	Any number of characters
? (question mark)	Any individual characters

The example below applies the * (asterisk) and ? (question mark) wildcard characters

'Delete all .xlsx or .xlsm files from a folder, but not .xls files
'as the ? wildcard must be atleast on character in length
 Kill "C:\Users\marks\Documents\Folder\*.xls?"

Get file attributes

'If the file is Read-Only, display message box
If (GetAttr("C:\Users\marks\Documents\Folder\ReadOnlyFile.xlsx") And vbReadOnly) <> 0 Then
    MsgBox "The file is Read only"
End If

It is possible to check for other file attributes, by replacing vbReadOnly in the code above:

VBA Name of attribute	Enumerator	Description
vbNormal	0	Files with no attributes (default setting)
vbReadOnly	1	Read-only files
vbHidden	2	Hidden files
vbSystem	4	System files
vbVolume	8	Volume label
vbDirectory	16	Directories

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts: