Formatting Dates & regional setting Excel VBA

This is super frustrating and it doesn't make sense to me.

This is in Excel 2010.

I've recorded a macro to format some data, containing date (DD/MM/YYYY). I import it, Excel sees it as text. So I use the "text to date" macro I have recorded, store into a VBA sub.

Here's the recorded macro:

Sub DateFormatting()
    Sheets("Donnees").Select
    Columns("H:H").Select
    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :="/", FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
End Sub

When I import my data, at first I have this. You can see it's seen as TEXT by Excel because it is left-aligned (Column H and I confirm it actually is):

Then I run the macro, and get this (column H):

You can see Excel sees it as Date, as it is right-aligned. If I convert it to "number" I see the underlying serial, as expected. So one would think it's fine. But it's really not:

If I run the macro AGAIN (and I would, because more data will be added to it later, so I need to make sure the newly imported data at the bottom of these will be correctly formatted as well), I get this:

So basically it changed the format from DD/MM/YYYY (which it is SUPPOSED to be) to MM/DD/YYYY (which is wrong). If I run that macro again on that data set, it switches back to DD/MM/YYYY.

But the WORST thing is that if I MANUALLY do the exact same thing (e.g. instead of running the macro, I manually go to "Data", "Text to columns" and select the EXACT same options), then it doesn't change. If the date is formatted as DD/MM/YYYY, it stays this way, and if it is formatted as MM/DD/YYYY (because of this stupid quirk), then it stays that way as well. I repeat it enough (and even re-recorded the macro a couple times) to be SURE I do the exact same things.

I know this is because of regional settings, but the file won't always be use on my computer, and I have no way of ensuring that the end user will have any specific regional settings. I need this file to be region-settings independant, basically.

MY QUESTION IS: How can I make sure that those dates are:

• Formatted & recognized as date by Excel
• Independant of the local regional settings of the users?

I know I could either to an intermediary import step (and format the data there, instead of in the main file), or then tweak the code so that only newly imported data have the macro applied.... But then I feel it's not reliable because how do I know Excel won't mess up the format?

Oh, because the Macro is a bit cryptic looking at the VBA:

I go to Data, Text to Column, choose "Delimited" (doesn't matter because I don't actually split it into columns), then "Delimiters" as default (doesn't matter again I'm not actually splitting the text into columns), then "Date" and in the dropdown "DYM" for DD/MM/YYYY

EDIT: Please see Ron Rosenfield's answer for full details. For completedness, here's the code I'll be running to import the data & format it at import, as opposed to importing it and then formatting it:

Sub importData()
Dim myFile As String
myFile = Sheet5.Cells(2, 5).Value 'My metadata sheet, containing name of file to import

Sheet1.Select  'Setting target sheet as active worksheet
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\ChadTest\" & myFile, _
    Destination:=Sheet1.Cells(Sheet5.Cells(2, 1).Value, 1))
        .Name = "ExternalData_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
If Sheet1.Cells(1, 1).Value = "" Then
  .TextFileStartRow = 1
Else
  .TextFileStartRow = 2
End If
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 4, 1, 1, 1, 4, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheet1.Select
    Application.WindowState = xlMinimized
    Application.WindowState = xlNormal
End Sub

From what you write, it seems you are getting your data from a TEXT or CSV file which you OPEN in Excel, and then try to process the date. A common problem.

Instead, what you can do is IMPORT the file. If you do this, the Text Import Wizard (same as the text-to-columns wizard), will open BEFORE the data gets written to the worksheet, and give you the opportunity to specify the format of the dates being imported.

That option should be on the Get External Data tab of the Data Ribbon:

I'm a bit hazy on the exact process you are using to get data from the source to the Excel worksheet, but the whole process could surely be automated with VBA. And there should be no need to run the macro on the already imported data.