[RESOLVED] Help needed with Delimiting

[Starbucks Junkie]

Dear all,



I have writen some code that takes a CSV file and converts it into an Excel format.



My problem is, when I perform this function manually through Excel the excel sheet formats correctly with all dates in UK format.

But when I perform the exact same function via VBA any dates that can be read in US version format that way.



Example: - Manually ,01/12/2008, becomes 01/12/2008

VBA ,01/12/2008, becomes 12/01/2008



Can you enlighten me as to what is going wrong when performed via VBA?

ChDir "I:\H925 Buying\Scott Atkinson\Projects\Import Tracker\DHL reports"
    Workbooks.Open Filename:= _
        "I:\H925 Buying\Scott Atkinson\Projects\Import Tracker\DHL reports\Booked not Shipped Report.xls"
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWorkbook.SaveAs Filename:= _
        "I:\H925 Buying\Scott Atkinson\Projects\Import Tracker\DHL reports\Booked not Shipped Report.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWindow.Close

[Siddharth Rout]

two things...

1) How is it stored in the CSV? 01/12/2008 or 12/01/2008?

2) In which columns are the date stored in?

[Davadvice]

one way that may work is:

if you have a template file you are adding the data to you formate the cell that will hold the date and this should fix it.

the issue you are having is due to the fact the MS believe that everyone uses the non UK date format when they create their wizard functions.

you may be better reading the file into an array and then on the output to excel format the date using the format function and processing in line by line.

David

[Starbucks Junkie]

two things...

1) How is it stored in the CSV? 01/12/2008 or 12/01/2008?

2) In which columns are the date stored in?

The data is stored as 01/12/2008.

The column is as per the column in the Columns(..:..) command.

[Starbucks Junkie]

one way that may work is:

if you have a template file you are adding the data to you formate the cell that will hold the date and this should fix it.

the issue you are having is due to the fact the MS believe that everyone uses the non UK date format when they create their wizard functions.

you may be better reading the file into an array and then on the output to excel format the date using the format function and processing in line by line.

David

David,

I am already using a template that is formatted correctly, but still does not work.

The last statement interested me, how would I achieve the array function?

[Siddharth Rout]

Okay, Can I see your csv file? let me see if I can whip up a quick code for you

[Starbucks Junkie]

Okay, Can I see your csv file? let me see if I can whip up a quick code for you

Kool,

One of my CSV files.

[Siddharth Rout]

I just tried it and shows the date correctly...

Here is the code

    Workbooks.OpenText Filename:="P:\AdHocReport[1].txt", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 1), Array( _
        3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
        , 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
        Array(17, 1)), TrailingMinusNumbers:=True
    Cells.Select
    Cells.EntireColumn.AutoFit

[Starbucks Junkie]

I just tried it and shows the date correctly...

Here is the code

    Workbooks.OpenText Filename:="P:\AdHocReport[1].txt", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 1), Array( _
        3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
        , 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
        Array(17, 1)), TrailingMinusNumbers:=True
    Cells.Select
    Cells.EntireColumn.AutoFit

Kool,

I have eight reports all CSV files with differing information in.

How does the Array function work in conjunction to the file formats, so that I can modify it for my other files?

For example, I assume that each array statment is referring to each data set divided by the commas.

[Siddharth Rout]

How does the Array function work in conjunction to the file formats, so that I can modify it for my other files?

It works on the headers that the text file has and which you want to have in the xls file for example, this particular text file has 17 headers...

1 Order
2 Item
3 Partner ID
4 Partner Name
5 Shp Qty
6 Container
7 Origin
8 Early Ship Date
9 Late Ship Date
10 Sailing
11 Est Disch
12 ETA Dest
13 Vehicle Name
14 Actual Disch Date
15 Commercial Invoice
16 Invoice Value
17 BOOKING REF

[Starbucks Junkie]

And what does the second number in each array relate to?

[Siddharth Rout]

And what does the second number in each array relate to?

Before I explain this... you need to first understand on how can you format the data in the Text to Columns. There are 3 formats 1 (GeneralFormat), 2(TextFormat) and 4(DMYFormat) for the data in the columns.

So the second number in each array relates to these formats for example let's individually assing formats for the above text file... see the pic attached...

Also we need to remember that when Excel VBA sees a *.csv filename, it ignores the FieldInfo data. Now, the dates in your file are in dd-mm-yyyy format, however Excel is seeing them as mm-dd-yyyy. Consequently, where a date is a valid mm-dd-yyyy date, Excel formats it in mm/dd/yyyy date format, otherwise it formats it as dd-mm-yyyy in general format.

If you rename the .csv to .txt (or almost anything else), then it will
format the dates as expected.