help with dates format and vba code

Hello everyone. I need some help!
An online platform I use exports .csv files where a column is a date. For some random reason, but I guess because I am located in London and the files are generated in US (so with mm-dd format) some of the dates are recognized as such, whilst others are considered as text. You can see what I mean at row 242 of the file I am attaching.


As I need all dates to be in the number format (44227 for example), I created the following code which splits the column A into day month and year, recombine and get the number in column G.

Sub Macro1()


 Dim OOF As Long
    OOF = Range("A1").End(xlDown).Row

    Columns("B:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.NumberFormat = "General"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""DD/MM/yyyy"")"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=NUMBERVALUE(LEFT(RC[-1],2))"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=NUMBERVALUE(MID(RC[-2],4,2))"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=NUMBERVALUE(RIGHT(RC[-3],2))"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-2],""/"",RC[-3],""/"",""20"",RC[-1])"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+0"


Range("B2:g2").Select
    Selection.AutoFill Destination:=Range("b2:g" & OOF), Type:=xlFillDefault

End Sub

It works just fine.

The problem I have is that it only works if I add the module directly in the csv file and run the macro there, whilst what I want is to start the macro from another file, pick the csv file and let it run the macro. If I do so, the format of the dates remain incorrect, ending up with #value error in the column G.

This is the code I created:

Sub example()
Dim myFile As String
myFile = Application.GetOpenFilename
If myFile = "False" Then Exit Sub

Application.ScreenUpdating = False
Application.DisplayAlerts = False


Workbooks.Open filename:=myFile

Dim OOF As Long
    OOF = Range("A1").End(xlDown).Row

    Columns("B:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.NumberFormat = "General"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""DD/MM/yyyy"")"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=NUMBERVALUE(LEFT(RC[-1],2))"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=NUMBERVALUE(MID(RC[-2],4,2))"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=NUMBERVALUE(RIGHT(RC[-3],2))"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-2],""/"",RC[-3],""/"",""20"",RC[-1])"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+0"


Range("B2:g2").Select
    Selection.AutoFill Destination:=Range("b2:g" & OOF), Type:=xlFillDefault


Application.ScreenUpdating = False
Application.DisplayAlerts = False



End Sub

I don't know if there is something I can do in the vba itself or it's a setting in the file with the macro perhaps?

Can anyone help me?

Thank you!

Originally Posted by thoandros

... something I can do in the vba itself ...

You took on this matter from the most tedious and time-consuming side - use Nori's advice.
In Excel, you have ready-made text import mechanisms, which can additionally be turned into a macro, e.g.:

Option Explicit

Sub strange_kind_of_date() ' :)
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Clear: .Filters.Add "CSV Files", "*.csv"
        .InitialFileName = ThisWorkbook.Path & "\"
        If .Show <> -1 Then Exit Sub
        Dim strbufpth: strbufpth = .SelectedItems(1)
    End With
    
    Dim nwbk As Object
    Set nwbk = Workbooks.Add
    
    With nwbk.ActiveSheet
        With .QueryTables.Add("TEXT;" & strbufpth, .Range("A1"))
            .FieldNames = True
            .AdjustColumnWidth = True
            .TextFilePlatform = 65001
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileColumnDataTypes = Array(3)
            .Refresh BackgroundQuery:=False
            .Delete
        End With
        With .Range("A1")
            .EntireColumn.ColumnWidth = .EntireColumn.ColumnWidth + 5
        End With
    End With
    
    Set nwbk = Nothing
End Sub