Importing CSV file with Dates using Macros

[Daz]

Hi List
I am using macros to import CSV files which have a column of dates that is
formatted consistantly down the column. They data to be imported is in format
,"dd/mm/yyyy", . If I open the file manually the data is imported
consistantly and the entire column is converted to dates.
When I use a macro to open the same file some cells are treated as text and
some as number. I cannot detect a patten to which is imported as text and
which is number.

Macro
WorkBooks.OpenText Filename:="C:\test.csv"

Sample data
"AckDate","Date Avail","Diff"
0,"1/11/2004",0
0,"21/11/2004",0
0,"5/10/2004",0
0,"1/11/2004",0

Using sample data above row 1,3,4 open as dates rows 2 opens as text
i am running Excel 2002 with SP-2
Can anyone suggest what I am doing incorrectly.

 

[Dave Peterson]

Rename your .csv file to .txt and your macro will run ok.

 

[Daz]

If I just rename the csv file to have a txt ending I get a runtime error File
not found.
If I modify the point called in the excel macro to
WorkBooks.OpenText Filename:="C:\test.txt" and leave the file as c:\test.csv
Same result file not found.
If I do both rename file and change macro the file loads as text strings ie
each row from the text file is loaded as a single string into one cell.

Any other suggestions.
Can I force the loading as dates using Fieldinfo attribute to the OpenText
command. Would this be
WorkBooks.OpenText Filename:="C:\test.csv", _
dataType:=xlDelimited, comma:=True, Fieldinfo:=Array(2,4)

 

[Dave Peterson]

Rename the file to .txt

Record a macro when you open it. You can specify each field as you want.

Use that macro and toss the older version.