Pasting CSV data in workbook with date formating

[Crooza]

Hi guys,

I've run into an issue I can't resolve.

I'm copying data from a csv file and pasting in to a workbook. The data is in dd/mm/yyyy format and when I copy the data I'm setting local:=true

I can physically paste the data into a workbook at this stage manually and the date data is formatted correctly ie dd/mm/yyyy, however if I record that same manual process as a macro and rerun the macro it pastes the date data in mm/dd/yyyy format.

I've tried formatting the column of data before copying, after copying but it's not a formatting issue (I think) as the data is in text form when I copy it (or is it).

This is the line I'm using to paste the data

Please Login or Register  to view this content.

Do I need to add something to that ?

Thanks for any help in advance

[David A Coop]

Hi Crooza,

When we live outside the USA, dates cause lots of problems which the MS programmers seen reluctant to address.

I believe that when you OPEN the file in your macro, if you code something like:

Please Login or Register  to view this content.

you will get what you want. I know I've dealt with it several years ago, but can't remember what I did, so I used Google to remind me.

I discovered that by setting Local = true uses the local machines date format rather than assuming mdy so in countries such as Australia (dmy) it'll change the behaviour of Open()

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Crooza]

David

Thanks. Yes I've set Local:=true and that gets the data in the correct format. As I stated if I copy I can paste manually and it pastes in the correct format but if I use VBA to paste it reverts to the old format!!

It's driving me crazy.

[David A Coop]

I think I remembered what I did!

I wonder if you could work on the CSV data with your macro.

When you open the CSV file, use VBA to change the format of the date column(s) to General - then do your copy and paste steps, and in your new sheet, format the dates back to the format you want.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Crooza]

Mate

Thanks. That was all it needed. I hate working with date fields. You've saved me some headaches.

Well done

[David A Coop]

Thanks for the Rep, Crooza. I share your opinion of date fields! Happy I was able to help. Simple solution, but it works!

David