I am writing a macro and the macro works fine, but I am trying to add some error handling to it so others are using it and an error occurs they are able to figure out what happened. The last problem I am having is I am using the Application.GetOpenFilename to open multiple files with multiselect = True. I am using a regex to match the file name and if the wrong file name is chosen then it displays an error message. If multiselect = False then I get no errors, but when it is equal to True I get a Type Mismatch error. I can only assume this is because when mutliselect = True the file is an array which the regex cannot handle. Is there a solution to this or can anyone point me to a better solution to handle the error. I have attached the VBA script as well.
Sub DataImport_Loop() Dim nom As String Dim wb As Excel.Workbook Dim i, j, k, m, n, file As Variant Dim strPattern As String: strPattern = "Strain End Point [0-9] - FEA Loop - Loading - (Timed)" 'File Pattern Dim regex As Object Set regex = CreateObject("VBScript.RegExp") 'Turns Screen Updating and Alert Displays off Application.ScreenUpdating = False Application.DisplayAlerts = False nom = ActiveWorkbook.Name 'takes user straight into necessary folder If CurDir() <> CurDir("J:") Then ChDrive "J:" ChDir "J:FEA Material Data" End If 'Number of specimens tested For i = 1 To 5 'Allows user to select multiple files to open file = Application.GetOpenFilename( _ FileFilter:="Text Files (*.csv), *.csv", _ MultiSelect:=True) 'If no file selected, stop data import and display error message If Not IsArray(file) Then MsgBox ("You only imported " & (i - 1) & " Specimens.") Exit Sub 'Sets patteren to check if correct file With regex .Pattern = strPattern End With 'Checks set pattern, displays error message if not correct file If regex.Test(file) = False Then MsgBox ("Select Loading Only") Exit Sub End If Else Counter = 1 While Counter <= UBound(file) j = (2 * i) - 1 Workbooks.Open file(Counter) Set wb = Workbooks("Strain End Point " & Counter & " - FEA Loop - Loading - (Timed).csv") 'End of column, needs + 3 to account for first 3 unused cells k = Range("F4", Range("F4").End(xlDown)).Count + 3 'Loops through data, deletes negative values For m = 4 To k If Range("F" & m).value < 0 Or Range("F" & m).Offset(0, 1) < 0 Then Range("F" & m).Delete Range("F" & m).Offset(0, 1).Delete 'If cell is deleted, rechecks new value m = m - 1 End If Next m Range("F4:G" & k).Copy Workbooks(nom).Sheets(Counter + 1).Cells(4, j).PasteSpecial wb.Close 'Opens next file Counter = Counter + 1 Wend End If Next i 'Turns Screen Updating and Alert Displays back on Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub 
Likeoperator for something like this. SinceFilewill always be an array (whenMultiSelectisTrue, even with just a single file selected, you will always need to iterate through the elements.filearray. Also, it looks to me like your code will not achieve what you intend. If the user hits the cancel button, the code afterIf Not IsArray(file) Thenwill execute --- your MsgBox is displayed then youExit Sub. The regex code NEVER executes. It seems like the regex block of code should follow theElsestatement. Also, I think it would be better code style to use aForloop instead of theWhile Counterloop.