Opentext method

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Jan 13, 2002
Messages
424
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Jan 13, 2002
Messages
424

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What do you get if you run this code:

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

True or False?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Feb 22, 2002
Messages
453
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

Active Member
Joined
Jan 13, 2002
Messages
424
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
 

Forum statistics

Threads
1,144,310
Messages
5,723,640
Members
422,506
Latest member
mdindas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top