VBA – Validate Filenames

The following code will validate whether a filename is valid.  This is useful if your code requires a user inputted filename. It will verify that the filename is valid before proceeding and possibly generating an error.

Validate File Name

This function tests that the variable ‘FileName’ is a valid filename:

Function ValidateFileName(ByVal FileName As String) As Boolean
Application.ScreenUpdating = False
    Dim wb As Workbook

    ' Check for nothing in filename.
    If FileName = "" Then
        ValidateFileName = False
        GoTo ExitProc
    End If

    'Create Temp File
    On Error GoTo InvalidName:
    Set wb = Workbooks.Add
    wb.SaveAs Environ("temp") & "\" & FileName & ".xlsx", 51
    On Error Resume Next
    'Close Temp Excel File
    wb.Close False
    'Delete Temp Excel File
    Kill Environ("temp") & "\" & FileName & ".xlsx"
    'File Name Validated - Exit Function
    ValidateFileName = True
    GoTo ExitProc

'If File Can not be Created
    On Error Resume Next

    'Close Temp Excel File
    wb.Close False
    'File Name Not Validated - Exit Function
    ValidateFileName = False

Application.ScreenUpdating = False
End Function

You can call the function like this:

Sub test_ValidateFileName()

Debug.Print ValidateFileName("fda?/")

End Sub

This test procedure will output TRUE or FALSE to the Immediate Window.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text


Learn More!

<<Return to VBA Examples

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)