Excel VBA - Error Parsing Dates, Handling Strings

Something like this should work for you:

Sub tgr()

    Dim wb As Workbook
    Dim wsDest As Worksheet
    Dim sFilePath As String
    Dim aData As Variant

    sFilePath = Application.GetOpenFilename("CSV Files, *.csv", MultiSelect:=False)
    If sFilePath = "False" Then Exit Sub    'Pressed cancel

    Set wb = ActiveWorkbook
    Set wsDest = wb.Sheets("Sheet2")

    Application.ScreenUpdating = False
    With Workbooks.Open(sFilePath)
        aData = .Sheets(1).Range("A1", .Sheets(1).Cells(.Sheets(1).Rows.Count, "E").End(xlUp)).Value
        .Close False
    End With
    Application.ScreenUpdating = True

    With wsDest.Range("B11").Resize(UBound(aData, 1), UBound(aData, 2))
        .Value = aData
        .Resize(, 1).NumberFormat = "mm/dd/yyyy"    'Can set date format here, change to dd/mm/yyyy if needed
    End With

End Sub

Most likely, the issue is a mismatch between the date format of your data and your Windows Regional Settings. Several ways to handle this

• Change your Windows Regional Settings so they match
• Change the file type to a *.txt file. Then use the Workbooks.OpenText method which allows you to specify the date column data type.
• Create a Data Connection which will also allow you to do the same. Just be sure that you don't keep creating QueryTables. It the table is already there, either delete and recreate, or refresh.

Here is some code demonstrating the QueryTable method. In the Excel GUI this would be the Data ► Get External Data ► From text option

Option Explicit

Sub OpenCSV()
Dim filePath As String
Dim intChoice As Integer
Dim WS As Worksheet

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogOpen).Show

If intChoice <> 0 Then

    filePath = Application.FileDialog( _
      msoFileDialogOpen).SelectedItems(1)

    Set WS = Worksheets("sheet2")
    With WS.QueryTables

    'If it exists, either delete and re-import or refresh
    If .Count > 0 Then
        Range(.Item(1).Destination.Address).CurrentRegion.Delete
        .Item(1).Delete
    End If
    End With
'
    With WS.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=WS.Range("$B$11"))
        .Name = "New Text Document"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False

        'make sure format argument matches format in the csv file
        .TextFileColumnDataTypes = Array(xlDMYFormat)

        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End If
End Sub