Import CSV file into Excel understanding the date importing issue

Question

I have received help on this before, but I clearly don't understand the date import. The file I want to import has data in Columns A to D. I Only want to import A to C. This part I get right but the issues are the dates. Currently in the CSV file it is in the format of YYYYMMDD I need it to convert it to dd/mm/yyyy. My regional settings are in that format.

When I run the code the dates are totally botched instead for example 04 December 2023 which needs to be 04/12/2023 it changes the date to 12/04/2023 it reads as 12 April 2023.

Here is my attempt:

Private Sub ImprtBtnABSA_Click()
    Dim vFile, arIn, arOut()
    Dim wbCSV As Workbook
    Dim i As Long, lastRow As Long, s As String
    Dim t0 As Single

    'Select a text file through the file dialog.
    'Get the path and file name of the selected file to the variable.
    vFile = Application.GetOpenFilename("ExcelFile *.txt,*.txt;*.csv", _
        Title:="Select CSV file", MultiSelect:=False)

    'If you don't select a file, exit sub.
    If TypeName(vFile) = "Boolean" Then
        Application.ScreenUpdating = True
        Exit Sub
    End If
    t0 = Timer

    'The selected text file is imported into an Excel file. format:2 is csv, format:1 is tab
    Set wbCSV = Workbooks.Open(Filename:=vFile, Format:=2, ReadOnly:=True)

    'Bring all the contents of the sheet into an array
    'and close the text file
    With wbCSV.Sheets(1)
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lastRow < 2 Then Exit Sub ' If last row is less than 2, exit sub
        ' Exclude the last row by adjusting the range
        arIn = .Range("A2:C" & lastRow).Value
        
        wbCSV.Close
    End With
    
    'built output array from input array
    ReDim Preserve arOut(1 To UBound(arIn), 1 To 3)
    For i = 1 To UBound(arIn, 1)
        ' Adjust column numbers as needed
        s = Trim(arIn(i, 2))
        ' Remove text within parentheses including brackets
        s = RemoveTextInParentheses(s)
        ' Assuming the date is in the format YYYYMMDD
        arOut(i, 1) = Format(DateSerial(Left(arIn(i, 1), 4), Mid(arIn(i, 1), 5, 2), Right(arIn(i, 1), 2)), "dd/mm/yyyy")
        arOut(i, 2) = s ' Trimmed and without text within parentheses
        arOut(i, 3) = arIn(i, 3) ' Column C
    Next

    'write output array to sheet2
    With ThisWorkbook.Sheets(2)
        .UsedRange.Clear
        .Range("A1:C1") = Array("Date", "Description", "Amount")
        .Range("A:A").NumberFormat = "dd/mm/yyyy"
        .Range("B:B").NumberFormat = "@"
        .Range("C:C").NumberFormat = "General"
        .Range("A2").Resize(UBound(arOut), 3).Value = arOut
        .Columns("A:C").AutoFit
    End With
    MsgBox "Done", vbInformation, Format(Timer - t0, "0.0 secs")
End Sub

Can someone help me understand what I am doing wrong and how to xif it please?

Answer 1

I suggest using the TextToColumns to handle the dates.

Start with removing the line:

    ' Assuming the date is in the format YYYYMMDD
    arOut(i, 1) = Format(DateSerial(Left(arIn(i, 1), 4), Mid(arIn(i, 1), 5, 2), Right(arIn(i, 1), 2)), "dd/mm/yyyy")

to keep the format as is.

Then you need to output your data on the sheet, do the text to columns and then change the date format:

'write output array to sheet2
With ThisWorkbook.Sheets(2)
    .UsedRange.Clear
    .Range("A1:C1") = Array("Date", "Description", "Amount")
    .Range("B:B").NumberFormat = "@"
    .Range("C:C").NumberFormat = "General"
    .Range("A2").Resize(UBound(arOut), 3).Value = arOut
    .Columns("A:C").AutoFit
End With 

ThisWorkbook.Sheets(2).Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 5), TrailingMinusNumbers:=True
ThisWorkbook.Sheets(2).Range("A:A").NumberFormat = "dd/mm/yyyy"

Now you should be able to set the format of the cells as you like