Why can't excel get aussie dates right?

[chrisaroundtown]

Hi,

I have a reporting program that dumps data into a csv file every 30 mins. When I go into my Master excel document I need to run a macro that goes into the csv file, copies a few columns and pastes into the Master document.

When I do it manually it works beautifully, but (like the lazy bastard I am) I don't want to do it manually, I want a macro to do it for me. Whenever the macro runs it tries to convert my text dates to real dates. For example, in the csv file I will have cells with 31/03/2003 and below it 01/04/2003(1st of April in Aussieland) . Whenever the macro pastes in my Master document it converts 01/04/2003 to 04/01/2003(4th January), and because there is no 31st month it leaves 31/03/2003 as is.

The same crap happens when trying to change formats. If I go into the csv file manually and change the dates to text format all are changed to serial numbers correctly. When a macro does the exact same thing I get 37625 for 1st April, it should say 37712 for the 1st April. 31/03/2003 is left as 31/03/2003 again because there is no 31st month.

Why would it work manually and not work with a macro?

Thanks
Chris

 

[onedtent]

this has been covered under
thread68-399663

 

[chrisaroundtown]

Thanks but the thread didn't help.

It works now though, instead of having the data in a csv file I have it in a text file. That removes all formatting, when the macro imports to excel it leaves them as text with the right dates.

Thanks anyway,
Chris.

 

[QldSugardaddy]

I have a regfile that you can run that will fix this problem. It is simple, I use it all the time and it takes about 3 seconds. Anyone who is after it, please email me at toddransome@hotmail.com and I will forward it thru Thanks, Todd
When in doubt, kick 'er in the guts

 

[onedtent]

did u actually do the regedit part in the thread? it should have worked.

 

[chrisaroundtown]

I did use the regedit part, but all my settings were correct.

I'll just keep using the text file, it is easier for me anyway.

Thanks
Chris.