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 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:
|* (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)|
Get Excel news, tips & tricks straight to you inbox. Helping you to save time and achieve more with Excel.
You will also receive the Tab Hopper Add-in for FREE.