Excel Automation - Problems with strings containing dates

[jump-jet]

64-bit Windows7, Excel 2007

I'm driving Excel from within a .NET program using Excel's automation interface. I'm running on a PC in England, where the date format is "dd/mm/yyyy".

When I insert a string containing "10/05/2011" (10th May 2011) into a cell and then view the cell in Excel, Excel shows "05/10/2011" in the cell, and the date has become 5th October 2011 (in other words if I use the "Format Cells" operation on the cell, the Category shown is Date, the Sample value represented is the fifth of October and the Locale is shown as English (UK)).

Correct me if I'm wrong, but it looks to me as if the Excel automation interface has inspected the string and recognised it as a date, but has not using the expected locale when it then parsed the date. How can I control the locale used by the automation interface?

Dave

 

[Sektor]

Change thread's Culture to UK.

Imports System.Threading
Imports System.Globalization

Thread.CurrentThread.CurrentCulture = New CultureInfo("en-GB")

 

[jump-jet]

Thanks for the quick reply.
The culture on the executing thread is already "en-GB".

 

[Sektor]

So, which notation you need?

 

[jump-jet]

So, which notation you need?

I don't understand your question.
I need to be able to take a date string that was formatted using the PC's current locale (e.g. for 10th May 2011 on an English PC "10/05/2011", or "05/10/2011" on a US PC) and import it into Excel using automation so that the date in Excel is correctly set to 10th May 2011.