[RESOLVED] Help needed with Delimiting
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?
Code:
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
Re: Help needed with Delimiting
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?
Re: Help needed with Delimiting
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
Re: Help needed with Delimiting
Quote:
Originally Posted by koolsid
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.
Re: Help needed with Delimiting
Quote:
Originally Posted by 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
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?
Re: Help needed with Delimiting
Okay, Can I see your csv file? let me see if I can whip up a quick code for you :)
1 Attachment(s)
Re: Help needed with Delimiting
Quote:
Originally Posted by koolsid
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.
Re: Help needed with Delimiting
I just tried it and shows the date correctly... :confused:
Here is the code
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
Re: Help needed with Delimiting
Quote:
Originally Posted by koolsid
I just tried it and shows the date correctly... :confused:
Here is the code
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.
Re: Help needed with Delimiting
Quote:
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
Re: Help needed with Delimiting
And what does the second number in each array relate to?
1 Attachment(s)
Re: Help needed with Delimiting
Quote:
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.
