Hi,

What about pasting the string: "MyName" & vbTab & cdbl(cdate("01/12/2004"))
cdbl(cdate(string)) converts the string into a date then into the number
representing the date. (just make sure cdate returns the correct value in the
VB app)
Finally, once you have pasted, if excel displays the number, just apply the
date format on the whole column (or row) of dates, eg:
- assuming you have pasted strings in A1:Z100 and column B contains the dates
- WorkSheet.Range("B1:B100").numberformat = "dd/mm/yyyy"

I hope this will work for you,
Regards,
Sebastien

"Rua" wrote:

Hi all

Just wondering if anyone had encountered this problem before, and if so, had
a better work around than me.

The situation:
I create an Excel 8 application in VB and add a worksheet. All this is fine.
I then build a string in VB which contains numerous datatypes including
Text, numbers, and unfortunately, dates!

A simplified version of this string is "MyName" & vbTab & "01/12/2004" &
vbTab & "0001"
The date here is in the format "dd/mm/yyyy" so reads 1st Dec 2004

In VB I then do the following...

Clipboard.Clear
Clipboard.SetText (string)
WorkSheet.Paste

The problem is, after the paste, the date has been entered in the cell as
"12/01/2004" (American Date Format). To check I'm not insane, I then do a
CTRL-V (Paste), so the same string is on the clipboard, but the date is now
pasted in as "01/12/2004". What's going on behind the scenes???

I've tried formatting the target column with .NumberFormat = "dd/mm/yyyy",
and it doesn't work. My regional settings are also set to "dd/mm/yyyy"

My workaround is to set the target column .NumberFormat to Text (@), but
this means my dates aren't really usable in the Excel sheet

Anyone have any advice - or at least empathy!