PDA

View Full Version : Solved: Help needed with Delimiting



Poundland
10-28-2008, 02:26 AM
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

Bob Phillips
10-28-2008, 02:37 AM
Your code is opening an Excel file, is this correct?

How about posting an example csv file.

Poundland
10-28-2008, 04:15 AM
Your code is opening an Excel file, is this correct?

How about posting an example csv file.

Attached is one of the CSV files.

Bob Phillips
10-28-2008, 05:02 AM
Which row throws a problem?

Poundland
10-28-2008, 05:30 AM
Which row throws a problem?

It is all of the dates, any date that cab make sense both ways, ie 01/12/2008, 12/01/2008.

GTO
10-28-2008, 10:04 PM
Greetings Poundland,

While I don't often import delimited data, due to both seeing these type questions several times recently and contemplating some of this type work, I tried to emulate your efforts.

Firstly, it appears that you are recording a macro (nothing wrong with that), but even just going through the steps w/the recorder on produced slightly different results for me, in that opening the file was recorded as 'Workbooks.OpenText ...' rather than 'Workbooks.Open ...'. Thus - it appeared to me that you MAY have recorded two macros and combined them? Or, maybe you're in 2003 and this is just a difference in version as 2000 doesn't list .csv in file types but rather prompts advising that the xls file is delimited...

This part is mostly just curiousity though, as regardless of whether you extrapolate the data while opening, or use TextToColumns afterwards, the problem(s) appear to me to be:

The argument 'FieldInfo:='

In yours, you tell Excel that the first column is a date, (which ain't so...) then don't set the columns that actually are dates as such. Thus - you're planting the data into the columns as text. Once that's done, it seems harder/less efficient to get Excel to coerce the data back to a date.


'...statements...
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

'...statements...

Looking at just the TextToColumns, and presuming the files you'll convert will be consistent in columns, maybe something similar to...

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 2), Array(5, 1), _
Array(6, 2), Array(7, 2), Array(8, 4), Array(9, 4), Array(10, 4), _
Array(11, 4), Array(12, 4), Array(13, 2), Array(14, 4), Array(15, 2), _
Array(16, 1), Array(17, 1))

...would work.

Now if that works, maybe something similar to:

Sub ConvDelWDates()
Dim wb_Child As Workbook
Dim strFullName As String

'// Change to suit needs//
strFullName = ThisWorkbook.Path & "\Copy of On the Water report 2.xls"

Workbooks.OpenText Filename:=strFullName, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 2), _
Array(5, 1), Array(6, 2), _
Array(7, 2), Array(8, 4), _
Array(9, 4), Array(10, 4), _
Array(11, 4), Array(12, 4), _
Array(13, 2), Array(14, 4), _
Array(15, 2), Array(16, 1), _
Array(17, 1))

Set wb_Child = ActiveWorkbook

With wb_Child
With .Sheets(1)
.Columns("A:Q").EntireColumn.AutoFit
.Range("H:H,I:I,J:J,K:K,L:L,N:N").NumberFormat = "dd-mm-yy"
End With
.SaveAs Filename:=strFullName, FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False, AddToMru:=False
End With

Set wb_Child = Nothing

End Sub

You can see that in the different columns, I converted them to date or text.

Date format (displayed)

Now at least on my laptop, I also ran into the problem you mentioned, as to the date being formatted to whatever the default format is, suh as mm/dd/yy, where you wanted dd/mm/yy. Since we now have imported or converted the data as a date, I just converted the display with .NumberFormat

Hope this helps and please let me know, as this may help me in the near future.

Thanks,

Mark

Poundland
10-29-2008, 07:35 AM
GTO,

Thanks for your help what you suggested in your last part has worked.

GTO
10-29-2008, 09:34 PM
Glad we were able to help :beerchug:

Mark