CODEWITHSUNDEEP
excelvba
Jhigs
Jhigs

Reputation: 67

If File = "False" Application.GetOpenFileName Error 13 Type Mismatch

I'm trying to ensure Application.GetOpenFileName doesn't break if someone hits cancel when selecting a file.

I have an if statement that states if file = "false" then show a msgbox and exit sub.
This works when no file is selected, however when I run the macro with the files selected I get a Type Mismatch error.

Dim nom As String
Dim wb1, wb2, wb3, wb4, wb5 As Excel.Workbook
Dim i, j, k, file As Variant

nom = ActiveWorkbook.Name
If CurDir() <> CurDir("J:") Then
    ChDrive "J:"
    ChDir "J:FEA Material Data"
End If
For i = 1 To 5
    Application.ScreenUpdating = False
    MsgBox ("Select Compound" & vbNewLine & vbNewLine & "If Data From Criterion, Select Loading Only" & vbNewLine & vbNewLine & "If Data From Alliance, Select All")
    file = Application.GetOpenFilename( _
      FileFilter:="Text Files (*.csv), *.csv", _
      MultiSelect:=True)
    If file = "False" Then
        MsgBox "No File Selected"
        Exit Sub
    Else
        counter = 1
        While counter <= UBound(file)
            Workbooks.Open file(counter)
            counter = counter + 1
        Wend
    End If 
    'more code

Upvotes: 3

Views: 5037

Answers (1)

D_Bester
D_Bester

Reputation: 5911

When it has files it returns a variant with an array. Debug.Print VarType(file) returns 8204. So you need to check the array for file names. If the user selects Cancel then the Variant will be a boolean. 

If VarType(file) = 11 Then
    MsgBox "No File Selected"
    Exit Sub

Or more readable (thanks to Dirk Reichel):

If Not IsArray(file) Then
    MsgBox "No File Selected"
    Exit Sub

Determining the Type of a Variant

Upvotes: 3

Related Questions