Opening semicolon delimited CSV file

Question

How does one open a semicolon delimited CSV file with VBA in Excel 2000?

Sample data

An ID;TEST20090222
A Name;Firstname Surname
A Date;11.05.2000

Country:;SomeCountryName
Currency;EUR
CostA;
CostB;
CostC;
Part1;10;20;30
Part2;;;;
Part3;34;56;87

Code

In Excel 2003 11.8231.8221 SP3 with VBA 6.5.1025, I can open a semicolon delimited file with the following VBA code:

Workbooks.OpenText filename:=myFilename, _
    DataType:=xlDelimited, Semicolon:=True, Local:=True

However, when the same code is run in Excel 2000 9.0.8961 SP1 with VBA 6.5.1025, I get the following error:

Compile error: Named argument not found

That is --I think-- because Excel 2000 doesn't know the named argument "Local".

Therefore, I deleted the "Local:=True" part. But the problem then is that an entire line from the CSV file is written into one cell instead of being split up into the separate semicolon delimited parts.

I have searched the Internet for a solution, but did not find anything useful and concise.

Any ideas?

[Update 17.02.2009]

I tried the suggestion from user lc with the macro recorder. However, the results were confusing.

When I open the CSV file with menu File->Open... and then select the CSV file, the semicolon separated data is correctly parsed. And the recorded code is as simple as:

Workbooks.Open filename:= _
               "D:\testdata\Example 01 CSV\input.csv"

But when I use that VBA code in my macro, each line ends up in one cell again.

According to the suggestion from user barrowc, I also changed the The Windows "Regional and Language Options" settings from "German (Switzerland)" to "English (United States)". Even after restarting Excel, nothing changed, same problem.

I wonder why it is working on user Remou's system. What regional and language settings do you have?

Answer 1

[Update 22.02.2009]

In the meantime, I solved the problem by writing an import function myself instead of using Workbooks.OpenText.

I just open the CSV file as a text file, read line by line, split each line into the semicolon separated elements and write each element into a cell.

Sub ImportCSVFile(filepath As String)
    Dim line As String
    Dim arrayOfElements
    Dim linenumber As Integer
    Dim elementnumber As Integer
    Dim element As Variant

    linenumber = 0
    elementnumber = 0

    Open filepath For Input As #1 ' Open file for input
        Do While Not EOF(1) ' Loop until end of file
            linenumber = linenumber + 1
            Line Input #1, line
            arrayOfElements = Split(line, ";")

            elementnumber = 0
            For Each element In arrayOfElements
                elementnumber = elementnumber + 1
                Cells(linenumber, elementnumber).Value = element
            Next
        Loop
    Close #1 ' Close file.
End Sub

Got the inspiration from Shasur: http://vbadud.blogspot.com/2007/06/vba-read-text-files-with-leading.html

I still do not know why Workbooks.OpenText does not work on my system even though it seems to work on user Remou's system. I guess it might have something to do with the operating system language (English) and the regional and language settings (German, Switzerland), but I am not sure.

Anyway, the workaround works for me. Thank you all for you suggestions and help!