Worksheet.Opentext failure?

I am struggling with the Worksheet.Opentext code required to open a text file in excel - specifically with the date format.
For example, I have a text file containing Feb08.
When opened manually using space as deliminator it correctly returns 01/02/2008.
However, when I record the same opening action as a macro and then run same macro it returns 08/02/2009, which is incorrect. Try it and see for yourself!
Any ideas how to correct this apparent anomally so that the macro code returns the correct date?
Many thanks
Astrikor

Try What??
You probably need a year in the date

It would help to see your code.

Seems like the FieldInfo information is not applied when playing the macro back.

You may need to use a QueryTable approach to load your file into an existing workbook.

If you need more help then code and example file would help. also details of xl version.

Originally Posted by davesexcel

Try What??
You probably need a year in the date

Thanks for your replies.
Not being a formally trained excel user, I rely heavily on the macro recording function for creating code. The Workbook.Opentext code appears to be fairly complex, so I am hoping someone will point me to a solution for this issue. The easiest way is for me to explain how I got the results I have described so you can do the same.

Let me be specific:
Open Notebook and type feb08. Save the txt file.
Open excel (I am assuming excel 2007) and start the record macro facility.
Now (within excel) open the saved txt file.
When prompted, select 'Delimited' > next, 'Tab' >next, 'General', 'DMY' > Finish
You should have Feb-08 displayed in the cell and 01/02/2008 in the editing box. THis would be correct.
Stop the macro recording.
Close the saved file.
(The macro code you have recorded should resemble as below)

Now run the macro you have recorded.

You will have 08-Feb displayed in the cell and 08/02/2009 in the editing box. This is wrong.

Now you will understand the problem.

Unfortunately I have no control over the text files I am opening, so I can't change the text data format.

If you can try the procedure I have described, assuming your results are the same as mine, presumably there is some code missing from the macro.

Example code as follows:

Please Login or Register  to view this content.

This produces 08/02/2009 whereas it should be 01/02/2008.
What is wrong?

Suggestions welcome!

Many thanks
Astrikor.

Did you try my suggestion of using the Querytable code instead of OpenText?

Turn of the macro recorder and use Data > Import External data.

As I said it looks like this part of the code is not applied with replaying the macro,

Please Login or Register  to view this content.