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?

 

[Joe4]

Welcome to the Board!

Can you post a small sample of how the data in this date columns look in your text file?

Are you sure you are opening a Text file and not an Excel file?
If you are, then why does your Text file have an Excel extensions?

Workbooks.OpenText Filename:="C:\Temp\ConsumiveisPOA.XLS",...

 

[lucasmk]

The data in the date column is like this 01/jan/12 when it's correct and when it's in text format it appears like this 02-FEB-12.

Yes, I'm opening a text file, the correct code is:

    Workbooks.OpenText Filename:="C:\Temp\ConsumiveisPOA.TXT", 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, 1), Array(9, 1), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)), _
        TrailingMinusNumbers:=True

Thanks for the welcome!

 

[Joe4]

Change:
Array(8, 1)
to
Array(8, 4)
(note: you can get this code yourself if you use the Macro Recorder and record yourself opening/importing the file and choosing the Date: DMY option for this field).

This will bring the field in as a date. From there, you can change the date format on the spreadsheet to any date format option you desire.

 

[lucasmk]

I've changed the 1 in the array for 4, but it still doesn't work...

 

[Joe4]

I've changed the 1 in the array for 4, but it still doesn't work...

How does it not work?
What does it do?

 

[lucasmk]

It still brings some dates as text like this: 02-FEB-12
For dates in january it works fine just the other months that dates appears as text.

I thought maybe I could use a formula to make it work but I need to extract this informations every day because they are the database for another worksheet, any idea how could I resolve that problem?

Best Regards

 

[Joe4]

It still brings some dates as text like this: 02-FEB-12

Are you sure that is Text? Because that is also a valid date format.
Like I said, you can format the date any way you like in Excel. Formatting it does not change its underlying value.

Here are a few ways you can check:

1. Highlight the cell in question, and look up in the formula bar. That shows exactly what is stored in Excel. Does that value look like a date.

2. Excel stores dates as a number, specifically the number of days since 1/1/1900. So an easy way to check to see if a value is a date or text is to use the ISNUMBER and ISDATE functions. If it is a date, ISNUMBER will be TRUE. If it is text, ISDATE will return TRUE.

So let's say that the cell in questions is G2. What do these functions return?
=ISNUMBER(G2)
=ISTEXT(G2)

 

[lucasmk]

Yes, I know it's a valid format too but excel doesn't recognize it as a date.
For the example I said before 02-FEB-12 in the formula bar excel shows the same thing 02-FEB-12 but even if I select the whole column and try to change the format to date it doesn't change the dates stored as texts.

For the functions the first one returns FALSE and the second one returns TRUE.
The only way I found is to select the cell with the date in text format press F2 to edit what is in the formula bar and press ENTER, but this is not very functional because I have like twenty thousand lines of data.

 

[Joe4]

It sounds to me like you might be importing this into an exisiting spreadsheet where at least some cells in column G might already be formatted to be Text.

Try putting this line of code before the import code you posted above in your original post, and see if that makes a difference when you run it:

    Columns("G:G").NumberFormat = "m/d/yyyy"

P.S. Be sure to include the change I mentioned in post #4 when doing this!