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
InvalidName:
    On Error Resume Next

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

ExitProc:
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.

<<Return to VBA Examples