Opentext method

[imported_unknown]

Hello.
I'm trying to use opentext method in order to open a csv file containing a date column.

I started recording a macro to do this then opened the file and specified DMY datatype for the date column. the file is opened and everything is ok. i stop the recording.

I go into the macro and see the opentext methond with all it's arguments. i run the macro, the file is opened but this time without the correct datatype in the date column. despite the fact that the correct opentext arguments are correct.
as a result day and month in some of the date values are interchanged.
for example if i have a date 1/10/02 in the csv file, when using opentext 10/01/02 is displayed instead.

i'd appriciate any help in this

 

[Andrew Poulsom]

If I record a macro to open a CSV file Excel uses the Open method not the OpenText method. In other words it bypasses the Text Import Wizard.

What do your dates look like in the CSV file? Post a few complete lines from the file.

 

[imported_unknown]

Hello andrew.
Thank you for your quick reply.

Here is some lines from the file when opened without a macro:

Priority Service Affecting Type Status Event Time+
Critical Yes BTS Open 30/09/2002 16:13
Critical Yes BTS Open 30/09/2002 17:02
Critical No BTS Open 30/09/2002 22:02
Minor No BTS Open 01/10/2002 9:04
Critical Yes BTS Open 01/10/2002 10:23
Critical Yes BTS Open 01/10/2002 11:23
Critical No Maintenance Open 01/10/2002 10:43


And here is the same file when opened using the macro:

Priority Service Affecting Type Status Event Time+
Critical Yes BTS Open 30/09/2002 16:13
Critical Yes BTS Open 30/09/2002 17:02
Critical No BTS Open 30/09/2002 22:02
Minor No BTS Open 10/01/2002 9:04
Critical Yes BTS Open 10/01/2002 10:23
Critical Yes BTS Open 10/01/2002 11:23
Critical No Maintenance Open 10/01/2002 10:43

Please note that the four lines in the second file month and day changed places whereas the three first line remained unchanged.

Hope you can help me with this.

Rafael

 

[Andrew Poulsom]

If I save your data as a CSV file (from Excel) it looks like this in Notepad:

Priority,Service,Affecting Type,Status,Event,Time+
Critical,Yes,BTS,Open,30/09/2002,16:13
Critical,Yes,BTS,Open,30/09/2002,17:02
Critical,No,BTS,Open,30/09/2002,22:02
Minor,No,BTS,Open,01/10/2002,9:04
Critical,Yes,BTS,Open,01/10/2002,10:23
Critical,Yes,BTS,Open,01/10/2002,11:23
Critical,No,Maintenance,Open,01/10/2002,10:43

If I open it in Excel the Text Import Wizard does not appear and everything is fine.

What does your CSV file look like in Notepad?

 

[imported_unknown]

Hello andrew

The file looks like in your last post but
still that doesn't solve the problem of opening my file using a macro in excel the month and day still interchange.

Rafael

 

[Andrew Poulsom]

What do you get if you run this code:

Sub Test()
    MsgBox Application.International(xlMDY)
End Sub

True or False?

 

[imported_unknown]

False

 

[Andrew Poulsom]

Strange, so do I.

I am wondering why Excel records the OpenText method when you open the file. What happens to your data and what does Excel record if you hold down Shift when opening the file? This will bypass the Text Import wizard.

 

[nehpets12]

At last someone with the same problem as me

I think this error is due to service release 1 on excel 2000

I also can open up manually ok but when you record the macro and run it it transposes the date format

 

[imported_unknown]

I'm afraid that recording a macro and opening the file with shift pressed changes the method recorded from opentext to open but still day/month are transposed