Opening CSV as worksheet with VBA - incorrect parsing

Question

I have some file.csv. If I open it by double-click, it opens in Excel with the proper setup (there are no semicolons and each "line" of data is correctly showing up in the expected columns).

Example result:

However, in VBA:

'Workbooks.OpenText Filename:=f, StartRow:=2, DataType:=xlDelimited, Semicolon:=True, ConsecutiveDelimiter:=True, TextQualifier:=xlTextQualifierNone
'Workbooks.OpenText Filename:=f, DataType:=xlDelimited, Semicolon:=True
'Workbooks.OpenText Filename:=f, DataType:=xlDelimited
'Workbooks.Open Filename:=f, Format:=xlDelimited, Delimiter:=Chr(34)
'Workbooks.Open Filename:=f, Format:=xlDelimited, Delimiter:=";"
Workbooks.Open Filename:=f, Format:=xlDelimited
Dim s As String
s = """" & ";" & """"
Workbooks.Open Filename:=f, Format:=xlDelimited, Delimiter:=s
'Workbooks.OpenText Filename:=f, DataType:=xlDelimited, OtherChar:=s
'Workbooks.OpenText Filename:=f, DataType:=xlDelimited, OtherChar:=";"

I've tried the lines above (and a number of others before I started keeping all my attempts) as well as without any arguments at all, but no matter what I do, the file will open without the text being split as it does when I double-click the file.

Example result:

So in summary; double-clicking the file parses the file correctly, Open and OpenText does not. Surprisingly, the worksheet looks identical regardless of using Open or OpenText.

A similar problem is described in this question, though it remains without a proper answer. The difference between our cases is that Excel won't parse anything for me, where it seems like it does parse large parts of the file for OP.

I just want to open the workbook and iterate over one of the columns, then close it. I have a couple of workarounds in mind, so I will be able to solve it one unnecessarily roundabout way or another.

So far I have found these ideas:

1. Renaming .csv to .txt and using OpenText
2. Using QueryTable import
3. Iterating over the borked file as it appears using OpenText and using LEFT/MID/RIGHT to get at the desired column(s)
4. Binary Open and Split each line into an array

Desired answers:

1. How to achieve this solution using Workbooks.OpenText or similar native function.
2. Explanations (or maybe even ideas) as to why Workbooks.OpenText and/or the most appropriate native function similar to it cannot achieve the desired result when Excel itself does seem to be capable of it.
3. Other workarounds.

Answer 1

If you are going to use VBA to import the file, just use VBA to parse the records:

Sub ImportFile()
   Dim j As Long, k As Long
    Close #1
    Open "C:\TestFolder\whatever.csv" For Input As #1
    j = 1
     Do While Not EOF(1)
            Line Input #1, TextLine
            ary = Split(TextLine, ";")
            k = 1
            For Each a In ary
               Cells(k, j) = a
               k = k + 1
            Next a
            j = j + 1
    Loop
    Close #1
End Sub

Doing it this way stops VBA from assigning special meaning to the .csv extension.

Once the core code is working to your satisfaction, you can decorate it with other features like double-quote encapsulation, etc.

Answer 2

Thanks to @GarysStudent for inadvertently tipping me off as to the one attribute I had not been smart enough to try. They said "assigning special meaning to the .csv extension".

Turns out that localization impacts this "special meaning" in the .csv context. I am using a localized version of Excel and setting Local:=True enables both Open and OpenText to parse the file correctly, even without specifying ; as a delimiter.