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!

Thanks - that works great. Was thinking about doing something like that but
couldn't find a convert to Serial Date vb function. Would also like to know
what exectly is causing it to behave like that.

Anyway, Thanks! Problem solved!

"sebastienm" wrote:

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!

Sorry, I have no idea what is really going on. The code would behave the same
way as CTRL+V, i would understand, but here, it seems like the Paste in code
doesn't use the local settings as does the CTRL+V. No idea why/how.

Regards,
Seb

"Rua" wrote:

Thanks - that works great. Was thinking about doing something like that but
couldn't find a convert to Serial Date vb function. Would also like to know
what exectly is causing it to behave like that.

Anyway, Thanks! Problem solved!

"sebastienm" wrote:

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!