Why can Excel open up properly a badly formatted csv but vba can't?

Greetings,

I am here after an afternoon of fruitless searches.
I am hoping someone can illuminate my now zombie-level brain

Preface: I understand as well as anyone that the csv in question shouldn't have commas as delimiters and should use a different delimiter, be it a double quote, tab, or zombie face emoji.

My question:

I have a 27513 lines CSV file that sometimes, rarely, has a comma where it shouldn't.
Note: I have no control or access to the source for formatting.

If I open the file in Excel by simply double clicking the csv file in windows explorer, Excel somehow manages to format everything nicely.
I can see that indeed, somehow, one cell here and there does have a comma amongst its text, and yet, dear mystery, Excel manages to format it properly.

If I use typical VBA code to import the same somewhat shoddy csv file,

Option Explicit

Private uri As String
Private ws As Worksheet

Private Sub btnUpload_Click()

    shtRawData.Activate
    Set ws = ActiveWorkbook.Sheets("RawData") 'set to current worksheet name
    
    uri = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
    
    With ws.QueryTables.Add(Connection:="TEXT;" & uri, Destination:=ws.Range("A1"))
         .TextFileParseType = xlDelimited
         .TextFileCommaDelimiter = True
         .Refresh
    End With

End Sub

Then, the code doesn't detect and fix those rare errors.
I understand that the code above is not broken.

There's simply something more that Excel does when you double click on a csv file and Excel opens it up.
And that is precisely what I can't find after an afternoon of Googling.

What is it that Excel does to guess correctly (at least, in this case) the correct format of the file, and how can I use VBA to do the same?

Once again, I have no control over formatting and comments to that effect are a waste of time.
I'm hoping someone can illuminate what is Excel doing differently do fix the file when you open up the csv by double-clicking on it versus importing it in vba?


Cheers from Vancouver,

PS: Whoever figures this one out get a free latte card from Starbucks

What changes could be anything; could need

.TextFileConsecutiveDelimiter = False

Without some csv data for testing it's impossible to do analizing/debugging (just a couple of rows before and after the one that creates the problem).

I've sanitized it and replicated the problem with just a few rows. Hey, thank you I can't upload I'm too new it seems but the t1ny u3l is slash vbacsv

That opens it up correctly in new Excel window. Okay, I'm getting somewhere. Now I need to google how to target the given sheet in my app instead of a separate Excel window.

Winner winner...chicken dinner PM me your email