Text To Columns VBA Date problem

[lucasmk]

Hello,
I'm importing some data from a worksheet using Text to Columns command, when I do it manually it works ok but when I run it on VBA excel converts the data in text format. I've already tried putting xlDMYformat in the array but it still doesn't work.

The code is:

   Workbooks.OpenText Filename:="C:\Temp\ConsumiveisPOA.XLS", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, ), Array(9, 1), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)), _
        TrailingMinusNumbers:=True

The datas are in the column number 8.
Anyone can help me?

 

[Norie]

Why not try importing without changing it?

 

[lucasmk]

Still can't make it work...
The problem is that in the other worksheet that uses this data I have a formula that Concatenate an ID with the month and the year of those dates, on the dates that are stored as a text(2-FEB-12) the formula returns #VALUE!
The formula I use to concatenate is this one: =A2&MONTH(H2)&YEAR(H2)
Returning the error when I consolidate the data I have entire months with no value.

 

[Joe4]

If you look at the Text File in a Text Editor (not Excel), is this date field format pretty consistent all the way down (any odd entries, blanks, etc)?

You might want to post the rest of the code. Maybe there is something else going on that is interfering with this process.

Running with Norie's thoughts, another option is to bring ALL the dates in as text, as use STRING function (LEFT, MID, RIGHT) to pull off the month and day.

 

[lucasmk]

The strange thing is that when I open the txt file manually the dates come all correct, only when I open via VBA the dates come in text format... I'll try to bring all the dates as text to see if works, what number I should put on the array to bring them as text?

 

[Norie]

You could try not using the FieldInfo argument.

 

[lucasmk]

If you look at the Text File in a Text Editor (not Excel), is this date field format pretty consistent all the way down (any odd entries, blanks, etc)?

You might want to post the rest of the code. Maybe there is something else going on that is interfering with this process.

Running with Norie's thoughts, another option is to bring ALL the dates in as text, as use STRING function (LEFT, MID, RIGHT) to pull off the month and day.

I've brought all the dates in as text and it worked, thanks for the help guys! Problem solved!

 

[kulmalik]

Tow option are there one is normal

Sub abc()


Range("A:A").NumberFormat = "DD-MMM-YY"


End Sub

Second is Insert One column and use text formula.

Sub abc()
Dim rng As String


rng = ActiveSheet.UsedRange.Rows.Count


Range("B2:B" & rng).Value = "=TEXT(A2,""dd-mmm-yy"")"


End Sub